json_object_agg errors on null in field name

Question

PostgreSQL versions: my local install 11.3 and the below fiddle is on 10.0. Both behave the same.

I have a schema of pages, each page has sections and each section can have different kind of contents. When I query for a page, I wish to output all there is about that page in a JSON document.

I'm using CTE's to json_agg() various contents per section. Finally I join the sections into json_object_agg() to map section titles to section contents.

The problem: json_object_agg() throws an error when a page does not have any sections. I've verified guiltiness by using a regular json_agg() without section titles. The exact error:

error: field name must not be null

What I want: In any case not an error. I don't want to do custom error handling on the receiver side. It would be even better if the query can return a JSON Null in place of the json_object_agg() in case of no sections, but that's optional. (Other elegant solutions welcome)

The docs

Probably the documentation is incomplete or I missed something. Just for reference.

On aggregate expressions it says (emphasis mine):

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.

And in aggregate functions, on json_object_agg() no remark about not handling null:

aggregates name/value pairs as a JSON object

Fiddle with faulty domain argument. Changing the domain to the other options makes it work fine. Also using a non-existing domain works fine and returns 0 rows.

Query

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'
),
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
),
img as (
 select
 sid,
 json_agg(
 json_build_object(
 'Pos', pos,
 'Image', image
 )
 order by pos asc
 ) as imgs
 from images
 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,
 'Images', i.imgs
 )
 order by s.sid asc
 )
 )
from secs s
left join txt t on s.sid = t.sid
left join img i on s.sid = i.sid
group by s.page_id, domain;

Schema

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)
);
create table images (
 section_id int references sections,
 pos int not null,
 image 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
),
t2 as (
 insert into texts (section_id, pos, content) select section_id, 2, 'spanac one.two' from s
),
i1 as (
 insert into images (section_id, pos, image) select section_id, 1, 's11.jpg' from s
)
insert into images (section_id, pos, image) select section_id, 2, 's12.jpg' 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
),
t2 as (
 insert into texts (section_id, pos, content) select section_id, 2, 'spanac two.two' from s
),
i1 as (
 insert into images (section_id, pos, image) select section_id, 1, 's21.jpg' from s
)
insert into images (section_id, pos, image) select section_id, 2, 's22.jpg' from s;
with s as (
 insert into sections (page_id, title) select page_id, 'third' from pages where domain = 'spanac.com' returning section_id
),
t1 as (
 insert into texts (section_id, pos, content) select section_id, 1, 'Spanac three.one' from s
)
insert into images (section_id, pos, image) select section_id, 1, 's31.jpg' from s;
-- foo.com
insert into sections (page_id, title) select page_id, 'empty' from pages where domain = 'foo.com';

Answer 1

It sounds like you've found a bug.

You can report it at the bottom of their home page, and monitor the discussion of the bug on the pgsql-bugs mailing list, I don't expect them to change the way postgresql handles this data, they will probably just correct the documentation.

A possible work-around would be to add WHERE s.title IS NOT NULL to the agregating query

select
 json_build_object(
 'ID', s.page_id,
 'Domain', domain,
 'Sections', json_object_agg (
 s.title,
 json_build_object(
 'ID', s.sid,
 'Texts', t.txts,
 'Images', i.imgs
 )
 order by s.sid asc
 )
 )
from secs s
left join txt t on s.sid = t.sid
left join img i on s.sid = i.sid
WHERE s.title IS NOT NULL -- prevent null title from being agregated.
group by s.page_id, domain;

• Tim: The where clause works like charm. I'll post a bug report after the weekend.
• Chris Kobrzak: Please note, the WHERE clause removes the entire row from the results, in other words, you're not going to get that page_id and domain entry if the corresponding title is NULL. This may or may not be what you are expecting.

Answer 2

It would be better to use FILTER (WHERE ...) and COALESCE(..., '{}'::JSON).

select
 json_build_object(
 'ID', s.page_id,
 'Domain', domain,
 'Sections', COALESCE(json_object_agg (
 s.title,
 json_build_object(
 'ID', s.sid,
 'Texts', t.txts,
 'Images', i.imgs
 )
 order by s.sid asc
 ) FILTER (WHERE s.title IS NOT NULL), '{}'::JSON)
 )
from secs s
left join txt t on s.sid = t.sid
left join img i on s.sid = i.sid
group by s.page_id, domain;

See Fiddle.

• Chris Kobrzak: Nit: you don't need to cast the empty object literal to JSON. And if you are happy with the Sections key to equal null, you may even drop the entire second COALESCE argument altogether (so remove , '{}'::JSON).

Answer 3

I had a similar issue involving the title of training events and thankfully found this post. I took a slightly different tact in order to retain the null title records by using coalesce. That way the issue of the null titles is visible and can be addressed in upstream data processes / pipelines.

Code with coalesce for the json_object_agg aggregating field name value:

select
 json_build_object(
 'ID', s.page_id,
 'Domain', domain,
 'Sections', json_object_agg (
 coalesce(s.title, 'Null') as title,
 json_build_object(
 'ID', s.sid,
 'Texts', t.txts,
 'Images', i.imgs
 )
 order by s.sid asc
 )
 )
from secs s
left join txt t on s.sid = t.sid
left join img i on s.sid = i.sid
group by s.page_id, domain;

Answer 4

Your code below does not return the desired results:

select
 json_build_object(
 'ID', s.page_id,
 'Domain', domain,
 'Sections', COALESCE(json_object_agg (
 s.title,
 json_build_object(
 'ID', s.sid,
 'Texts', t.txts,
 'Images', i.imgs
 )
 order by s.sid asc
 ) FILTER (WHERE s.title IS NOT NULL), '{}'::JSON)
 )
from secs s
left join txt t on s.sid = t.sid
left join img i on s.sid = i.sid
group by s.page_id, domain;

If you filter the JSON as indicated in your response with COALESCE wrapped around the json_object_agg call, you will not get an aggregation for records which have a NULL title. You will only get an empty {} aggregation.

We want to get an aggregation for ALL records, including ones with NULL titles. We want their aggregated ID, Texts, and Images to be returned with a title - even if the title is'Null'. This is achieved by my original answer below (I would have simply commented on your post but I was prevented from doing so, so had to re-post this answer)

select
 json_build_object(
 'ID', s.page_id,
 'Domain', domain,
 'Sections', json_object_agg (
 COALESCE(s.title,'Null') as title,
 json_build_object(
 'ID', s.sid,
 'Texts', t.txts,
 'Images', i.imgs
 )
 order by s.sid asc
 ) 
 )
from secs s
left join txt t on s.sid = t.sid
left join img i on s.sid = i.sid
group by s.page_id, domain;