PostgreSQL中如何从任意位置JSON结构中按name提取choicies数组

解决方案 #

方案1：PostgreSQL 12+ 推荐使用JSONPath递归查询（适配任意嵌套层级） #

直接用JSONPath的递归下降运算符$..遍历全JSON结构匹配目标name，无需关心元素所在路径：

-- 单name查询
SELECT 
  matched_obj -> 'choicies' AS choicies_arr
FROM myTable myt,
  jsonb_path_query(
    myt.json, -- 若你的存储字段是json类型，改为 myt.json::jsonb
    '$ ? (@.name == $target_name)',
    jsonb_build_object('target_name', 'element4') -- 替换为你要查询的元素name值
  ) AS matched_obj
LIMIT 1; -- 因name全局唯一，加limit可提升查询性能

如果需要批量查询多个name，可修改为：

-- 多name批量查询
SELECT 
  matched_obj ->> 'name' AS element_name,
  matched_obj -> 'choicies' AS choicies_arr
FROM myTable myt,
  jsonb_path_query(
    myt.json,
    '$ ? (@.name == any($target_names))',
    jsonb_build_object('target_names', jsonb_build_array('element1', 'element4', 'element8'))
  ) AS matched_obj;

方案2：PostgreSQL 12以下版本 递归CTE遍历 #

如果使用的是12以下不支持JSONPath的版本，可以通过递归CTE遍历所有JSON节点后过滤：

WITH RECURSIVE json_nodes AS (
  -- 初始节点：展开最外层pages数组
  SELECT jsonb_array_elements(myt.json -> 'pages') AS node
  FROM myTable myt
  UNION ALL
  -- 递归展开所有节点下的数组元素
  SELECT jsonb_array_elements(value) AS node
  FROM json_nodes, jsonb_each(node)
  WHERE jsonb_typeof(value) = 'array'
)
SELECT node -> 'choicies' AS choicies_arr
FROM json_nodes
WHERE node ->> 'name' = 'element4' -- 替换为目标name
LIMIT 1;

原写法问题说明 #

你之前的写法仅展开了pages -> elements一级层级，更深的templateElements、直接挂载在page下的templateElements等路径的元素都不会被遍历到，因此会出现匹配不到、choicies为null的问题，递归查询的方案无需手动适配所有可能的路径，自动覆盖全JSON结构。

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