Collected from the PG bugs email list.
|OS||Gentoo linux (stable)|
|Reported by||Tim Möhlmann|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 15884 Logged by: Tim Möhlmann Email address: (redacted) PostgreSQL version: 11.3 Operating system: Gentoo linux (stable) Description: According to the documentation on aggregate expressions: https://www.postgresql.org/docs/11/sql-expressions.html#SYNTAX-AGGREGATES "Most aggregate functions ignore null inputs, so that rows in which one or more of the expression(s) yield null are discarded. This can be assumed to be true, unless otherwise specified, for all built-in aggregates." On aggregate function documentation no specific mention is made for json_object_agg(). https://www.postgresql.org/docs/11/functions-aggregate.html However, json_object_agg() throws an error in case of null in the argument(s): "error: field name must not be null". And I get it, JSON keys need to be unique strings and null is not that. However, this error is also thrown if both keys and values are an empty CTE result. In the following example there are pages. Each page has sections and each sections has text fields (texts). It is a simplified version of my app's query. When there is a page without sections json_object_agg() gets the empty result for "s.title" and the nested json_build_object() call. create table pages ( page_id serial primary key, domain text unique not null ); create table sections ( section_id serial primary key, title text not null, page_id int references pages ); create table texts ( section_id int references sections, pos int not null, content text not null, primary key (section_id, pos) ); -- spanac.com will have 3 sections with texts and images in each, various amounts insert into pages (domain) values ('spanac.com'); -- foo.com has 1 empty section insert into pages (domain) values ('foo.com'); -- bar.com has no sections insert into pages (domain) values ('bar.com'); -- spanac.com with s as ( insert into sections (page_id, title) select page_id, 'first' from pages where domain = 'spanac.com' returning section_id ), t1 as ( insert into texts (section_id, pos, content) select section_id, 1, 'spanac one.one' from s ) insert into texts (section_id, pos, content) select section_id, 2, 'spanac one.two' from s; with s as ( insert into sections (page_id, title) select page_id, 'second' from pages where domain = 'spanac.com' returning section_id ), t1 as ( insert into texts (section_id, pos, content) select section_id, 1, 'spanac two.one' from s ) insert into texts (section_id, pos, content) select section_id, 2, 'spanac two.two' from s; -- foo.com insert into sections (page_id, title) select page_id, 'empty' from pages where domain = 'foo.com'; And this is the query that triggers the error: with secs as ( select p.page_id, p.domain, s.section_id as sid, s.title as title from pages p left join sections s on p.page_id = s.page_id where p.domain = 'bar.com' -- 'foo.com' and 'spanac.com' work fine ), txt as ( select sid, json_agg( json_build_object( 'Pos', pos, 'Text', content ) order by pos asc ) as txts from texts join secs on sid = section_id group by sid ) select json_build_object( 'ID', s.page_id, 'Domain', domain, 'Sections', json_object_agg ( -- Error occurs here s.title, json_build_object( 'ID', s.sid, 'Texts', t.txts ) order by s.sid asc ) ) from secs s left join txt t on s.sid = t.sid group by s.page_id, domain; The above is also available in a fiddle, although it does not match the PostgreSQL version: https://www.db-fiddle.com/f/nzZz7jjrDSAYKtYK53bz7L/1 I've asked a question regarding this on: https://dba.stackexchange.com/q/241541/150398. The answer I got is that this might be a bug, hence I'm posting here. Two notes: 1. I cannot reproduce this when I simplify the query into not using a CTE and nested json_build_object() 2. I "blame" json_object_agg()", because I used a regular json_agg() without the s.title fields before and it worked fine. As in, the resulting json document just had "Sections": null. Best regards, Tim Mohlmann (muhlemmer)
|2019-07-02 15:04:15+00||PG Bug reporting form||BUG #15884: json_object_agg errors on null in field name|
|2019-08-27 19:57:55+00||Tom Lane||Re: BUG #15884: json_object_agg errors on null in field name|