如何在PostgreSQL表中插入嵌套JSON数组数据

我们从第三方API获取用户数据，需要存入PostgreSQL表中。目前将API返回的JSON数据传入Postgres函数tests，数据是应用新用户信息，要求在记录不存在时插入users表。

原SQL代码 #

with recursive jsondata AS(
    SELECT 
        data ->0->>'rmid' as rmid,
        data ->0->>'camid' as camid,
        data ->0->'userlist' as children 
    FROM (
        SELECT '[{"rmid":1,"camid":2,"userlist":[{"userid":34,"username":33},{"userid":35,"username":36}]},{"rmid":3,"camid":4,"userlist":[{"userid":44,"username":43},{"userid":44,"username":43}]}]'::jsonb as data
    ) s
    
    UNION 
    
    SELECT
        value ->0->> 'rmid',
        value ->0->> 'camid',
        value ->0-> 'children'
    FROM jsondata,jsonb_array_elements(jsondata.children)
    )SELECT 
    rmid,camid,
    jsonb_array_elements(children) ->> 'userid' as userid,
    jsonb_array_elements(children) ->> 'username' as username
FROM jsondata WHERE children IS NOT NULL

当前输出 #

仅提取了JSON数组第一个索引对象的用户数据，仅返回rmid=1, camid=2对应的两条用户记录，rmid=3, camid=4的用户记录未被处理。

期望输出 #

提取JSON数组中所有索引对应的对象数据，最终得到4条完整记录：

• rmid=1, camid=2, userid=34, username=33
• rmid=1, camid=2, userid=35, username=36
• rmid=3, camid=4, userid=44, username=43
• rmid=3, camid=4, userid=44, username=43

解决方案 #

原代码的核心问题是递归CTE初始阶段只取了JSON数组的第一个元素（data->0），且当前JSON结构是单层嵌套，不需要递归处理。直接通过两次jsonb_array_elements展开数组即可：

SELECT
    item ->> 'rmid' AS rmid,
    item ->> 'camid' AS camid,
    user_item ->> 'userid' AS userid,
    user_item ->> 'username' AS username
FROM (
    SELECT '[{"rmid":1,"camid":2,"userlist":[{"userid":34,"username":33},{"userid":35,"username":36}]},{"rmid":3,"camid":4,"userlist":[{"userid":44,"username":43},{"userid":44,"username":43}]}]'::jsonb AS data
) s,
jsonb_array_elements(data) AS item,
jsonb_array_elements(item -> 'userlist') AS user_item;

代码说明 #

1. jsonb_array_elements(data)：展开顶层JSON数组，得到每个包含rmid、camid和userlist的对象
2. jsonb_array_elements(item -> 'userlist')：展开每个对象下的userlist子数组，得到单个用户的详细数据
3. 提取所需字段，得到所有关联的用户记录

如果要实现「记录不存在时插入」的需求，可结合INSERT ... ON CONFLICT语法（假设userid是表的唯一约束字段，需根据实际表结构调整）：

INSERT INTO users (rmid, camid, userid, username)
SELECT
    item ->> 'rmid' AS rmid,
    item ->> 'camid' AS camid,
    user_item ->> 'userid' AS userid,
    user_item ->> 'username' AS username
FROM (
    SELECT '[...]'::jsonb AS data -- 替换为实际传入的API JSON数据
) s,
jsonb_array_elements(data) AS item,
jsonb_array_elements(item -> 'userlist') AS user_item
ON CONFLICT (userid) DO NOTHING;

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