PostgreSQL中向jsonb字段插入嵌套JSON数据的问题求助

你当前的SQL只是将字段直接拼接成扁平JSON对象，没有构建birth和Name的嵌套层级。要生成预期的嵌套结构，需要明确构造每个嵌套对象，以下是两种可行的写法：

方法1：使用json_build_object直接构造嵌套结构 #

json_build_object可以直接创建包含嵌套JSON对象的结构，直观易懂：

INSERT INTO emp(info)
SELECT 
  json_build_object(
    'birth', json_build_object('date', to_char(birth_date, 'YYYY-MM-DD')),
    'Name', json_build_object('surname', lastname, 'firstname', given_name)
  )::jsonb
FROM stg.employees;

• 用to_char(birth_date, 'YYYY-MM-DD')将日期格式化为纯日期字符串，避免结果中出现T00:00:00的时间部分；
• 外层json_build_object创建顶层键，每个嵌套值用内层json_build_object生成对应结构。

方法2：结合row_to_json与LATERAL子查询构造嵌套行 #

如果偏好使用row_to_json，可以通过LATERAL子查询分别构建嵌套层级的行结构，再转换为JSON：

INSERT INTO emp(info)
SELECT 
  row_to_json(x)::jsonb
FROM (
  SELECT 
    row_to_json(b) AS birth,
    row_to_json(n) AS Name
  FROM stg.employees e
  LATERAL (SELECT to_char(e.birth_date, 'YYYY-MM-DD') AS date) b
  LATERAL (SELECT e.lastname AS surname, e.given_name AS firstname) n
) x;

• LATERAL子查询b和n分别生成birth和Name对应的行数据；
• 外层row_to_json将这两个嵌套JSON对象组合成最终的结构。

执行以上任意一种写法，都能得到你预期的嵌套JSON：

{
  "birth": {"date": "1980-04-28"},
  "Name": {"surname": "James","firstname": "Jacob"}
}

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