Postgres 10.3：单SQL查询将sites多行数据写入指定JSONB字段

当然可以做到！针对你的PostgreSQL 10.3环境，这里有个简洁的单条SQL语句就能完成需求，还能完美处理sites表为空的情况：

UPDATE jsonindexdocument
SET index = (
    -- 构造sites数据的JSON数组，空表时自动返回空数组
    SELECT COALESCE(json_agg(json_build_object('id', id, 'name', name)), '[]'::jsonb)
    FROM sites
)
WHERE id = 1;

关键细节拆解： #

• json_build_object('id', id, 'name', name)：把sites表的每一行转成你需要的JSON对象结构，精准只包含id和name两个字段，避免混入表中其他字段（如果有的话）。
• json_agg(...)：将所有生成的JSON对象聚合为一个JSON数组。如果sites表没有数据，这个函数会返回NULL。
• COALESCE(..., '[]'::jsonb)：当json_agg返回NULL时（也就是sites无数据），自动替换为空数组的JSONB类型值，满足你的默认值要求。
• 整个子查询的结果直接赋值给jsonindexdocument表中id=1的index字段。

可选简化写法（如果sites表仅含id和name字段）： #

如果你的sites表本身就只有id和name这两个字段，也可以用更简洁的row_to_json来生成对象：

UPDATE jsonindexdocument
SET index = (
    SELECT COALESCE(json_agg(row_to_json(s)), '[]'::jsonb)
    FROM (SELECT id, name FROM sites) s
)
WHERE id = 1;

这个语句可以重复执行，每次运行都会根据sites表的最新数据更新index字段，非常方便。

内容的提问来源于stack exchange，提问作者Kim Stacks