0

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';
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;
| improve this answer | |
  • The where clause works like charm. I'll post a bug report after the weekend. – Tim Jun 29 '19 at 15:31
0

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.

| improve this answer | |
0

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;
| improve this answer | |
New contributor
chewster is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

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;
| improve this answer | |
New contributor
chewster is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.