如何将JSON对象数组（或jsonb_array）按顺序转为嵌套JSON对象？

假设你的数据存储在PostgreSQL中，我们可以通过递归CTE结合JSON函数实现需求，无需自定义聚合函数。以下是具体实现步骤：

1. 基础数据准备 #

假设你有一张表json_data，其中arr_col字段存储目标JSON数组：

CREATE TABLE json_data (
    arr_col JSONB
);

INSERT INTO json_data VALUES (
    '[
        {"id": 1, "name": "level 1"},
        {"id": 3, "name": "level 2"},
        {"id": 8, "name": "level 3"}
    ]'::JSONB
);

2. 核心查询逻辑 #

通过unnest ... WITH ORDINALITY拆分数组并保留顺序，再用递归CTE从最底层元素开始向上嵌套：

WITH RECURSIVE nested AS (
    -- 初始步骤：拆分数组，获取每个元素及其位置序号，倒序取最后一个元素作为递归起点
    SELECT 
        elem,
        pos
    FROM json_data,
         unnest(arr_col) WITH ORDINALITY AS t(elem, pos)
    ORDER BY pos DESC
    LIMIT 1

    UNION ALL

    -- 递归步骤：将当前嵌套结果作为上一个元素的child，合并成新的JSON对象
    SELECT
        prev.elem || jsonb_build_object('child', curr.elem) AS elem,
        prev.pos
    FROM (
        SELECT elem, pos
        FROM json_data,
             unnest(arr_col) WITH ORDINALITY AS t(elem, pos)
    ) prev
    JOIN nested curr ON prev.pos = curr.pos - 1
)
-- 取序号最小的元素，即为最终的完整嵌套结构
SELECT elem AS nested_result
FROM nested
ORDER BY pos ASC
LIMIT 1;

3. 结果说明 #

执行上述查询后，会得到你需要的嵌套结构：

{
  "id": 1,
  "name": "level 1",
  "child": {
    "id": 3,
    "name": "level 2",
    "child": {
      "id": 8,
      "name": "level 3"
    }
  }
}

关键逻辑解析 #

• unnest(arr_col) WITH ORDINALITY：将JSON数组拆分为多行，同时生成pos字段记录每个元素在原数组中的顺序位置。
• 递归CTE的初始分支：选取数组的最后一个元素作为嵌套的最底层。
• 递归分支：每次找到上一个位置的元素，将当前已嵌套的结构通过jsonb_build_object添加为它的child字段，形成新的嵌套对象。
• 最终选取pos最小的元素：即原数组的第一个元素，此时它已经包含了所有后续元素的嵌套结构。

边界情况处理 #

• 如果数组只有一个元素：递归不会执行，直接返回该元素（无child字段）。
• 如果数组为空：查询返回空结果，可根据需求添加默认值处理。

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