如何将PostgreSQL双列表所有记录查询为单个JSON对象？

在PostgreSQL 15中，有一张存储英文单词的表，部分单词带有解释，部分没有：

create table words (word text not null, explanation text);

insert into words (word, explanation) values
    ( 'AAHED' , null ),
    ( 'AAH' , 'To exclaim in delight' ),
    ( 'AAL' , 'East Indian shrub' ),
    ( 'AARDVARK' , null );

希望将这些记录查询为如下JSON对象（用于导出到Web服务器的JSON文件，供JavaScript应用使用）：

{
  "AAHED" : "",
  "AAH" : "To exclaim in delight",
  "AAL" : "East Indian shrub",
  "AARDVARK" : ""
}

尝试了以下查询，但未得到预期结果：

select json_build_object(word, explanation) from words;

with cte as (select word, explanation from words) select row_to_json(c) from cte c;

请问应使用PostgreSQL的哪个JSON函数？

可以使用json_object_agg函数来聚合所有键值对为单个JSON对象，同时用coalesce将null值转换为空字符串，匹配你的需求：

select json_object_agg(word, coalesce(explanation, '')) as result
from words;

说明 #

• json_object_agg(key, value)：将多行的键值对聚合为一个顶级JSON对象，正好生成你需要的结构。
• coalesce(explanation, '')：把explanation字段的null值替换为空字符串，避免JSON中出现null值，和预期输出完全一致。

内容的提问来源于stack exchange，提问作者Alexander Farber