PostgreSQL 10.18中将JSON联系人数组转为最多5列的方案问询

你可以直接通过PostgreSQL原生的JSON数组下标访问语法实现需求，下标从0开始计数，超出数组长度的位置会自动返回NULL，不需要额外安装插件，完全兼容10.18版本。

假设你的业务表名为user_info，存储联系人数组的JSON列名为contacts，示例查询语句如下：

SELECT
  -- 第1位联系人
  contacts->0->>'name' AS contact_1_name,
  contacts->0->>'adresse' AS contact_1_adresse,
  -- 第2位联系人
  contacts->1->>'name' AS contact_2_name,
  contacts->1->>'adresse' AS contact_2_adresse,
  -- 第3位联系人
  contacts->2->>'name' AS contact_3_name,
  contacts->2->>'adresse' AS contact_3_adresse,
  -- 第4位联系人
  contacts->3->>'name' AS contact_4_name,
  contacts->3->>'adresse' AS contact_4_adresse,
  -- 第5位联系人
  contacts->4->>'name' AS contact_5_name,
  contacts->4->>'adresse' AS contact_5_adresse
FROM user_info;

扩展用法 #

• 如果你需要筛选至少有1位联系人的记录，可在WHERE条件中添加json_array_length(contacts) > 0
• 如果你需要先对联系人按指定字段排序后再取前5位，可先拆分数组排序后再聚合，示例如下：

WITH ordered_contacts AS (
  SELECT
    -- 保留原表的其他主键/业务字段
    id,
    json_agg(item ORDER BY item->>'name') AS sorted_contacts
  FROM user_info,
       json_array_elements(contacts) AS item
  GROUP BY id
)
SELECT
  sorted_contacts->0->>'name' AS contact_1_name,
  sorted_contacts->0->>'adresse' AS contact_1_adresse,
  -- 其余2-5位联系人写法同上
  sorted_contacts->4->>'name' AS contact_5_name,
  sorted_contacts->4->>'adresse' AS contact_5_adresse
FROM ordered_contacts;

如果你的列是JSONB类型，上述所有语法完全通用，无需修改。

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