欄位名稱中的 json_object_agg 錯誤

PostgreSQL 版本：我的本地安裝 11.3，下面的小提琴在 10.0.0 上。兩者的行為相同。

我有一個頁面架構，每個頁面都有部分，每個部分可以有不同類型的內容。當我查詢一個頁面時，我希望在 JSON 文件中輸出關於該頁面的所有資訊。

我將 CTE 用於json_agg()每個部分的各種內容。最後，我將部分加入json_object_agg()以將部分標題映射到部分內容。

問題： json_object_agg()當頁面沒有任何部分時拋出錯誤。我已經通過使用json_agg()沒有章節標題的正常來驗證有罪。確切的錯誤：

error: field name must not be null

**我想要什麼：**無論如何都不是錯誤。我不想在接收方進行自定義錯誤處理。Null如果查詢可以返回 JSON來代替沒有部分的情況會更好json_object_agg()，但這是可選的。（歡迎其他優雅的解決方案）

文件

可能文件不完整或者我遺漏了一些東西。僅供參考。

在聚合表達式上它說（強調我的）：

大多數聚合函式忽略空輸入，因此一個或多個表達式產生空值的行將被丟棄。對於所有內置聚合，除非另有說明，否則可以 假定為 true 。

在聚合函式中，json_object_agg()沒有關於不處理的評論null：

將名稱/值對聚合為 JSON 對象

擺弄錯誤的域參數。將域更改為其他選項使其正常工作。使用不存在的域也可以正常工作並返回 0 行。

詢問

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;

架構

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';

聽起來你發現了一個錯誤。

您可以在他們的首頁底部報告它，並在 pgsql-bugs郵件列表上監視對錯誤的討論，我不希望他們改變 postgresql 處理這些數據的方式，他們可能只會更正文件。

一種可能的解決方法是添加WHERE s.title IS NOT NULL到聚合查詢

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;

引用自：https://dba.stackexchange.com/questions/241541