如何在PostgreSQL中提取JSON字段的全部信息

根据你使用的数据库类型，用对应的JSON数组展开函数即可自动处理不同长度的JSON条目，以下是主流数据库的实现方式：

PostgreSQL #

使用jsonb_array_elements（若字段为json类型则用json_array_elements）结合横向连接展开数组：

SELECT 
    u.user_id,
    elem->>'email' AS email,
    elem->>'updated_at' AS updated_at,
    (elem->>'net_income_verified')::numeric AS net_income_verified -- 根据实际数据类型调整转换规则
FROM 
    users u,
    jsonb_array_elements(u.json_field::jsonb) elem;

如果你的json_field是字符串格式而非原生JSON类型，需要先通过::jsonb转换为JSON类型。

MySQL #

利用JSON_TABLE函数解析JSON数组（仅MySQL 8.0及以上版本支持）：

SELECT 
    u.user_id,
    j.email,
    j.updated_at,
    j.net_income_verified
FROM 
    users u,
    JSON_TABLE(
        u.json_field,
        '$[*]' COLUMNS (
            email VARCHAR(255) PATH '$.email',
            updated_at VARCHAR(255) PATH '$.updated_at',
            net_income_verified INT PATH '$.net_income_verified' -- 根据实际数据类型调整
        )
    ) j;

SQL Server #

通过OPENJSON结合CROSS APPLY展开JSON数组：

SELECT 
    u.user_id,
    j.email,
    j.updated_at,
    j.net_income_verified
FROM 
    users u
CROSS APPLY 
    OPENJSON(u.json_field)
    WITH (
        email VARCHAR(255) '$.email',
        updated_at VARCHAR(255) '$.updated_at',
        net_income_verified INT '$.net_income_verified' -- 根据实际数据类型调整
    ) j;

以上方法会自动遍历每个user_id对应的JSON数组，将数组内的每个对象拆分为单独行并关联原user_id，无需手动处理条目数量差异。

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