PostgreSQL 14中如何拼接JSONB数组对象内的字段为字符串

你当前的查询使用jsonb_path_query会将数组中每个匹配的元素拆分为单独行，导致结果重复；同时该函数会自动过滤无匹配结果的行，不符合保留所有ID并返回NULL的需求。

解决方案 #

使用jsonb_path_query_array收集所有匹配的字段值为JSON数组，再通过jsonb_array_text转换为文本数组，最后用array_to_string拼接成逗号分隔的字符串。这种方式会保留所有原始ID，无匹配值时返回NULL。

完整查询代码 #

SELECT 
  t.id,
  array_to_string(jsonb_array_text(jsonb_path_query_array(t.b, '$."obj1"."obj2"."obj3"[*]."obj4"."obj"')), ',') AS obj5
FROM (
  VALUES
    (1, '{"obj1":{"obj2":{"obj3":[{"obj4":{"obj":"A"}},{"obj4":{"obj":"B"}}]}}}'::jsonb),
    (2, '{"obj1":{"obj2":{"obj3":[{"obj4":{"obj":"C"}},{"obj4":{"obj":"D"}}]}}}'::jsonb),
    (3, '{}'::jsonb)
) t(id, b);

执行结果 #

该查询会输出你期望的格式：

id | obj5
----+------
  1 | A,B
  2 | C,D
  3 | NULL

内容的提问来源于stack exchange，提问作者Isabela Oliveira Bardelli