PostgreSQL中拼接JSONB数组内选中对象的字段值方法

针对你的需求，这里提供两种可行的SQL解决方案，都能实现从documents表的data列中提取selected为true的书籍，并按number + name + description.note格式拼接后用逗号分隔：

方法一：使用JSON Path查询（简洁高效） #

利用jsonb_path_query筛选符合条件的书籍对象，再拼接字段并聚合：

SELECT
    d.id,
    coalesce(
        string_agg(
            concat(
                (book->>'number')::text,
                ' ',
                book->>'name',
                ' ',
                book->'description'->>'note'
            ),
            ', '
        ),
        ''
    ) AS selected_books
FROM documents d
CROSS JOIN LATERAL jsonb_path_query(d.data, '$.books[*] ? (@.selected == true)') AS book
GROUP BY d.id;

关键说明： #

• jsonb_path_query(d.data, '$.books[*] ? (@.selected == true)')：从data->'books'数组中筛选出selected为true的每一个书籍对象
• concat(...)：将书籍的number（转为文本类型）、name、description.note用空格拼接成单个字符串
• string_agg(..., ', ')：将所有符合条件的书籍字符串用, 连接成一行
• coalesce(...)：处理无选中书籍的情况，返回空字符串而非NULL

方法二：使用JSON转记录集（直观易读） #

通过jsonb_to_recordset将JSON数组转为关系型记录，筛选后再拼接聚合：

SELECT
    d.id,
    coalesce(
        string_agg(
            concat(b.number::text, ' ', b.name, ' ', descr.note),
            ', '
        ),
        ''
    ) AS selected_books
FROM documents d
CROSS JOIN LATERAL jsonb_to_recordset(d.data->'books') AS b(
    selected boolean,
    number int,
    name text,
    description jsonb
)
CROSS JOIN LATERAL jsonb_to_record(b.description) AS descr(note text)
WHERE b.selected = true
GROUP BY d.id;

关键说明： #

• jsonb_to_recordset(d.data->'books')：将books数组转为记录集，定义每个字段的对应类型
• jsonb_to_record(b.description)：进一步将书籍的description对象转为记录，提取note字段
• 用WHERE b.selected = true筛选选中的书籍，后续拼接和聚合逻辑同方法一

示例结果 #

针对你提供的示例数据，两种方法都会返回：

2 Second book Some note for 2nd book, 4 Fourth book Some note for 4th book

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