如何在PostgreSQL中解析非固定路径的JSON数组特定元素？

针对JSON数组内元素顺序不固定的情况，你可以通过拆分JSON数组、提取键值对再聚合的方式，精准获取目标键对应的值，以下是两种实用方案：

方案一：拆分后聚合提取 #

假设你的表名为test_table，存储JSON数组的字段为json_data（类型为jsonb，若为json类型，将以下语句中的jsonb_替换为json_即可），可以用以下SQL查询提取指定键值：

SELECT
  id,
  MAX(CASE WHEN kv.key = 'name' THEN kv.value END) AS name,
  MAX(CASE WHEN kv.key = 'age' THEN kv.value::INT END) AS age,
  MAX(CASE WHEN kv.key = 'Sex' THEN kv.value END) AS sex
FROM test_table,
     jsonb_array_elements(json_data) AS arr,
     jsonb_each_text(arr) AS kv
GROUP BY id;

逻辑说明： #

1. jsonb_array_elements(json_data)：将JSON数组拆分为每行一个独立的JSON对象。
2. jsonb_each_text(arr)：将每个JSON对象拆分为key（键名）和value（键值）的行数据。
3. CASE语句匹配目标键，MAX聚合确保每个键只返回对应的值（因为每个键在数组中仅出现一次）。

方案二：子查询定向提取 #

如果只需要提取少数几个键，也可以用子查询直接定位包含目标键的数组元素：

SELECT
  id,
  -- 提取name字段
  (SELECT arr->>'name' FROM jsonb_array_elements(json_data) arr WHERE arr ? 'name') AS name,
  -- 提取age字段并转为整数
  (SELECT (arr->>'age')::INT FROM jsonb_array_elements(json_data) arr WHERE arr ? 'age') AS age,
  -- 提取Sex字段
  (SELECT arr->>'Sex' FROM jsonb_array_elements(json_data) arr WHERE arr ? 'Sex') AS sex
FROM test_table;

逻辑说明： #

• arr ? 'name'：检查当前JSON对象是否包含name键（仅适用于jsonb类型；若为json类型，替换为json_object_keys(arr) @> ARRAY['name']）。
• 子查询遍历数组元素，找到包含目标键的元素后直接提取对应值。

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