PostgreSQL深度嵌套JSON字段查询及分步构建方法咨询

阿华AIGC实验室

2026-5-8

解析嵌套动态JSON并扁平化输出的PostgreSQL解决方案

一、直接得到期望结果的查询语句

下面这个查询可以直接输出你想要的格式，我已经帮你处理了所有嵌套层级的解析：

SELECT
    t.id,
    l2.l2_label,
    data.data_label,
    (data.data_value ->> 'value1')::integer AS value1
FROM
    testing t,
    -- 拆解level1下的所有动态键值对，得到二级标签和对应的数组
    json_each(t.contents -> 'level1') AS l2(l2_label, l2_array),
    -- 将二级标签对应的数组拆分为单个对象（每个对象包含level3）
    json_array_elements(l2.l2_array) AS l3_obj,
    -- 拆解level3下的所有动态键值对，得到数据标签和对应的value对象
    json_each(l3_obj -> 'level3') AS data(data_label, data_value);

执行这个查询后，返回的结果就和你期望的完全一致：

id	l2_label	data_label	value1
1	level2	a	1
1	level2	b	3
1	level2	d	5
1	level2	e	7

二、分步构建这类查询的方法

其实这类嵌套JSON的解析，核心就是一层一层拆解，从外到内逐步扁平化。我结合你已经写的查询，一步步带你构建：

1. 第一步：拆解顶层level1的动态键

你已经完成了这一步，通过json_each(contents -> 'level1')把level1下的所有键（比如示例中的level2）拆出来，同时用json_array_elements把对应的数组拆成单个对象：

SELECT
    id,
    key AS l2_label,
    json_array_elements(value) AS l3_obj
FROM testing, json_each(contents -> 'level1');

这一步的结果里，l3_obj就是每个包含level3的JSON对象，接下来我们要从这里面提取内容。

2. 第二步：提取level3的内容

现在我们需要从每个l3_obj里取出level3对应的JSON对象，用-> 'level3'即可：

SELECT
    id,
    key AS l2_label,
    json_array_elements(value) -> 'level3' AS level3_data
FROM testing, json_each(contents -> 'level1');

这时候level3_data列就是包含a、b这类动态键的JSON对象了。

3. 第三步：拆解level3下的动态键值对

用json_each把level3_data里的键值对拆出来，这样就能得到每个数据标签（a、b、d、e）和对应的value对象：

SELECT
    id,
    l2.l2_label,
    data.key AS data_label,
    data.value AS data_value
FROM testing,
     json_each(contents -> 'level1') AS l2(l2_label, l2_array),
     json_array_elements(l2_array) AS l3_obj,
     json_each(l3_obj -> 'level3') AS data;

这里我给每个拆解的结果加了别名（比如l2、data），让查询更清晰，避免字段名冲突。