SQL查询嵌套JSON列：提取二级对象并保留一级键的方案

我需要从表中某列的嵌套JSON里提取值，目前能拿到返回值，但无法同时获取对应的一级JSON键，且查询仅返回1行数据。

示例JSON #

{
    "key1": {
        "data1": "data1 object",
        "data2": "data2 object"
    },
    "key2": {
        "data1": "data1 object",
        "data2": "data2 object"
    }
}

其中一级键（如key1、key2）是动态生成的，二级的data类键是固定的。

期望结果（两种均可） #

结果1（保留二级结构） #

{
    "key1": {
        "data1": "data1 object"
    },
    "key2": {
        "data1": "data1 object"
    }
}

结果2（直接映射键值） #

{
    "key1": "data1 object",
    "key2": "data1 object"
}

当前未生效的查询 #

SELECT
    json->>"$.*.data1" AS data
FROM table
WHERE id=1;

当前输出 #

[
    "data1 object",
    "data1 object"
]

解决方案（以PostgreSQL为例） #

方案1：获取直接键值映射（对应结果2） #

通过json_each拆分顶层JSON为键值对，筛选出data1的值后重新聚合：

SELECT json_object_agg(k, v->>'data1') AS result
FROM your_table,
     json_each(json_column) AS j(k, v)
WHERE id = 1;

执行后返回：

{"key1": "data1 object", "key2": "data1 object"}

方案2：保留二级嵌套结构（对应结果1） #

调整聚合逻辑，保留data1的嵌套格式：

SELECT json_object_agg(k, json_build_object('data1', v->>'data1')) AS result
FROM your_table,
     json_each(json_column) AS j(k, v)
WHERE id = 1;

执行后返回：

{"key1": {"data1": "data1 object"}, "key2": {"data1": "data1 object"}}

注意事项 #

• 若你的JSON列类型是jsonb，只需将json_each替换为jsonb_each，json_object_agg替换为jsonb_object_agg即可兼容。

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