如何利用PostgreSQL带点符号列生成嵌套JSON(B)结果？

当然可以简化操作，不用手动嵌套jsonb_build_object。下面提供两种实用方法，自动解析列名中的点分隔层级，生成目标嵌套JSONB结构：

方法1：用递归CTE+聚合函数实现（无需自定义函数） #

适合临时查询，不需要创建额外对象。假设你的表名为flat_table，包含id、title.en、title.fr、category.name.en这些列：

WITH recursive column_hierarchy AS (
  -- 把每行转成JSONB，拆分出所有列名和对应值，并将列名按点拆分为路径数组
  SELECT
    id,
    regexp_split_to_array(column_name, '\.') AS path,
    column_value
  FROM
    flat_table,
    jsonb_each_text(to_jsonb(flat_table) - 'id') AS j(column_name, column_value)
),
nested_json AS (
  -- 从最底层的键值对开始构建
  SELECT
    id,
    path[array_length(path, 1)] AS key,
    path[1:array_length(path, 1)-1] AS parent_path,
    to_jsonb(column_value) AS value
  FROM column_hierarchy
  WHERE array_length(path, 1) = 1
  UNION ALL
  -- 递归向上合并层级，把底层JSON嵌套到父层级中
  SELECT
    nh.id,
    ch.path[array_length(ch.path, 1)] AS key,
    ch.path[1:array_length(ch.path, 1)-1] AS parent_path,
    jsonb_build_object(ch.path[array_length(ch.path, 1)], nh.value) AS value
  FROM nested_json nh
  JOIN column_hierarchy ch ON
    nh.id = ch.id AND
    nh.parent_path = ch.path[1:array_length(ch.path, 1)-1]
)
-- 聚合每个id对应的所有层级，生成最终嵌套JSONB
SELECT
  id,
  (SELECT jsonb_object_agg(key, value) FROM nested_json WHERE id = ft.id) AS nested_json
FROM flat_table ft
GROUP BY id;

方法2：创建自定义PL/pgSQL函数（适合重复使用） #

如果需要频繁执行这类转换，写一个自定义函数会更简洁：

CREATE OR REPLACE FUNCTION build_nested_jsonb(p_row jsonb) RETURNS jsonb AS $$
DECLARE
  result jsonb := '{}'::jsonb;
  key text;
  value jsonb;
  path text[];
BEGIN
  -- 遍历行转成的JSONB中的每个键值对
  FOR key, value IN SELECT * FROM jsonb_each(p_row) LOOP
    -- 将列名按点拆分为嵌套路径数组
    path := regexp_split_to_array(key, '\.');
    -- 把值插入到对应的嵌套路径位置
    result := jsonb_set(result, path, value, true);
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

使用时只需一行SQL：

SELECT id, build_nested_jsonb(to_jsonb(flat_table) - 'id') AS nested_json
FROM flat_table;

对比手动嵌套的优势 #

手动写法需要硬编码每个层级的jsonb_build_object，比如：

SELECT
  id,
  jsonb_build_object(
    'title', jsonb_build_object(
      'en', "title.en",
      'fr', "title.fr"
    ),
    'category', jsonb_build_object(
      'name', jsonb_build_object(
        'en', "category.name.en"
      )
    )
  ) AS nested_json
FROM flat_table;

当列数量多、层级深时，手动写法不仅繁琐，还容易出错。上面两种方法能自动解析列名的层级结构，大幅减少重复代码。

注意事项 #

• 确保列名中的点是严格的层级分隔符，不要包含需要保留的点（如果有，需要先转义再处理）
• 若存在重复的路径（比如两个列对应同一个嵌套键），jsonb_set会覆盖已有值，需保证列名的唯一性

内容的提问来源于stack exchange，提问作者behnam-io