如何在PostgreSQL中处理JSON数据并将其存入数组类型列

前提说明 #

假设你的表名为 users，若实际表名不同可自行替换；若notes 字段类型为 JSON，如果是 JSONB 类型，将下文SQL中的json前缀替换为jsonb即可。

1. 提取 referrals_to 字段实现 #

方案1：永久新增列并写入数据 #

• 第一步：新增数组类型的新列

ALTER TABLE users ADD COLUMN referrals_to VARCHAR(255)[];

• 第二步：将JSON中的link_to数组内容写入新列

UPDATE users
SET referrals_to = ARRAY(SELECT json_array_elements_text(notes->'link_to'))::VARCHAR(255)[];

方案2：仅查询不修改表结构 #

SELECT 
    name,
    notes,
    ARRAY(SELECT json_array_elements_text(notes->'link_to'))::VARCHAR(255)[] AS referrals_to
FROM users;

2. 提取 referrals_from 字段实现 #

方案1：永久新增列并写入数据 #

需要先完成referrals_to字段的创建和数据写入，再执行以下操作：

• 第一步：新增数组类型的新列

ALTER TABLE users ADD COLUMN referrals_from VARCHAR(255)[];

• 第二步：关联计算所有引用当前name的来源列表

UPDATE users u1
SET referrals_from = COALESCE(
    (SELECT ARRAY_AGG(DISTINCT u2.name) FROM users u2 WHERE u1.name = ANY(u2.referrals_to)),
    '{}'::VARCHAR(255)[]
);

方案2：仅查询不修改表结构 #

无需提前创建列，可一次查询返回所有结果：

WITH referrals_to_data AS (
    SELECT
        name,
        notes,
        ARRAY(SELECT json_array_elements_text(notes->'link_to'))::VARCHAR(255)[] AS referrals_to
    FROM users
)
SELECT
    r1.name,
    r1.notes,
    r1.referrals_to,
    COALESCE(ARRAY_AGG(DISTINCT r2.name), '{}'::VARCHAR(255)[]) AS referrals_from
FROM referrals_to_data r1
LEFT JOIN referrals_to_data r2 ON r1.name = ANY(r2.referrals_to)
GROUP BY r1.name, r1.notes, r1.referrals_to;

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