PostgreSQL低版本无jsonb_path_query时如何合并jsonb数组展开后的分散字段到同一行

你可以通过子查询直接提取对应字段值，避免拆分数组后多行列分散的问题，兼容所有支持jsonb的PostgreSQL版本，无需使用jsonb_path_query函数：

SELECT
  id,
  -- 提取001字段值
  (SELECT f ->> '001' FROM jsonb_array_elements(content -> 'fields') f WHERE f ? '001') AS "001",
  -- 提取符合条件的856下的u字段值
  (SELECT sf ->> 'u' 
   FROM jsonb_array_elements(content -> 'fields') f,
        jsonb_array_elements(f -> '856' -> 'subfields') sf
   WHERE f ? '856' 
     AND sf ? 'u' 
     AND sf ->> 'u' LIKE '%domain.com%'
   LIMIT 1) AS "856"
FROM mytable
-- 过滤符合条件的记录
WHERE EXISTS (
  SELECT 1 
  FROM jsonb_array_elements(content -> 'fields') f,
       jsonb_array_elements(f -> '856' -> 'subfields') sf
  WHERE f ? '856' 
    AND sf ? 'u' 
    AND sf ->> 'u' LIKE '%domain.com%'
);

原理解释 #

• 001字段提取：通过子查询遍历fields数组，找到携带001键的元素直接返回文本值，每条记录仅返回唯一匹配值
• 856下u字段提取：先找到fields数组中携带856键的元素，再拆分subfields数组筛选出u值包含domain.com的项返回
• WHERE条件的EXISTS子句提前过滤掉所有不符合筛选要求的记录，避免返回无效行

如果偏向调整原有拆分数组的写法，也可以通过分组聚合的方式合并行：

SELECT 
  id,
  MAX((field ->> '001')) AS "001",
  MAX((SELECT sf ->> 'u' FROM jsonb_array_elements(field -> '856' -> 'subfields') sf WHERE sf ? 'u')) AS "856"
FROM 
  mytable,
  jsonb_array_elements(content -> 'fields') AS field
WHERE EXISTS (
  SELECT 1 
  FROM jsonb_array_elements(content -> 'fields') f,
       jsonb_array_elements(f -> '856' -> 'subfields') sf
  WHERE f ? '856' AND sf ? 'u' AND sf ->> 'u' LIKE '%domain.com%'
)
GROUP BY id;

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