如何在PostgreSQL中使用SQL+jsonb查询从无序JSON数组提取员工工作电话号码

当然没问题，PostgreSQL的jsonb类型提供了非常灵活的数组展开和筛选能力，不需要额外工具就能精准提取你要的数据。

具体SQL语句 #

直接用嵌套的jsonb_array_elements展开嵌套数组，再筛选工作电话即可：

SELECT
  worker->>'name' AS employee_name,
  phone->>'number' AS work_phone
FROM
  contacts,
  jsonb_array_elements(json->'workers') AS worker,
  jsonb_array_elements(worker->'phones') AS phone
WHERE
  phone->>'type' = 'WORK';

语句解释 #

1. 展开workers数组：jsonb_array_elements(json->'workers') AS worker 把每条contacts记录里的workers数组拆分成单独的行，每一行对应一个员工对象；
2. 展开phones数组：jsonb_array_elements(worker->'phones') AS phone 接着把每个员工的phones数组也拆分成单独的行，每一行对应一个电话号码对象；
3. 筛选工作电话：WHERE phone->>'type' = 'WORK' 只保留类型为WORK的电话号码记录；
4. 提取字段：worker->>'name' 和 phone->>'number' 分别取出员工姓名和工作电话号码（->>操作符用于提取json字段的文本值）。

处理特殊情况（可选） #

如果存在同一个员工有多个工作电话的场景，你可以加上DISTINCT来避免重复记录：

SELECT DISTINCT
  worker->>'name' AS employee_name,
  phone->>'number' AS work_phone
FROM
  contacts,
  jsonb_array_elements(json->'workers') AS worker,
  jsonb_array_elements(worker->'phones') AS phone
WHERE
  phone->>'type' = 'WORK';

用你提供的测试数据运行上面的SQL，会得到完全符合需求的结果：

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