PostgreSQL 14.17：将JSONB数组对象关联至同对象属性的SQL查询

解决方案SQL #

直接使用jsonb_array_elements函数逐层展开嵌套的JSON数组，提取所需字段即可，无需自定义函数：

SELECT
    dep.data->>'department_name' AS department_name,
    (emp.data->'employee_id')::integer AS employee_id,
    emp.data->>'name' AS name,
    (emp.data->'salary')::numeric AS salary -- 可选保留，不需要可删除该行
FROM
    organization_data,
    jsonb_array_elements(data) AS dep(data),
    jsonb_array_elements(dep.data->'employees') AS emp(data);

执行逻辑说明 #

1. 展开部门数组：jsonb_array_elements(data)将表中data字段的顶层JSON数组（每个元素对应一个部门对象）拆分为多行，每一行代表一个部门数据。
2. 展开员工数组：针对每个部门的employees子数组，再次使用jsonb_array_elements(dep.data->'employees')拆分，得到每个部门下的单个员工数据行。
3. 提取字段：
   • 使用->>直接提取字符串类型字段（如department_name、name）；
   • 使用->提取JSON值后显式转换为对应类型（如employee_id转整数、salary转数值），保证字段类型符合关系表规范。

输出结果（基于给定测试数据） #

department_name | employee_id | name  | salary
----------------|-------------|-------|--------
sales           | 1           | Joe   | 10000
sales           | 3           | Linda | 30000
sales           | 2           | Mary  | 12000
sales           | 4           | Jack  | 11000

注：若期望第二个部门显示为accounting，需修改原始插入数据中的对应字段值，上述SQL仅基于给定的插入数据生成结果。

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