如何将含对象数组的JSON插入PostgreSQL并关联外层weekId

嗨，这个问题我之前也碰到过，核心就是要把外层的weekId和数组展开后的每一行绑定在一起，下面给你两种实用的实现方式：

1. 直接基于JSON数据的插入语句 #

假设你的JSON数据已经存在（比如在变量或表字段里），可以用CTE先包装JSON，再关联展开的数组：

-- 先定义示例JSON数据（实际使用时可替换为你的变量或表字段）
WITH json_input AS (
  SELECT '{ "weekId":20, "weekDays":[ { "day_of_week":"Monday", "working_hours":22.5, "description":"text", "date":"May 22, 2019" }, { "day_of_week":"Tuesday", "working_hours":22.5, "description":"text", "date":"May 22, 2019" } ] }'::json AS data
)
INSERT INTO timesheet(week_id, day_of_week, working_hours, description, date)
SELECT 
  (data->>'weekId')::int AS week_id, -- 提取外层weekId并转成整数类型
  x.day_of_week,
  x.working_hours,
  x.description,
  (x.date)::date AS date -- 将JSON中的日期字符串转为数据库日期类型
FROM json_input,
     -- 展开weekDays数组，定义每个字段的对应类型
     json_to_recordset(data->'weekDays') AS x(
       day_of_week varchar, 
       working_hours real, 
       description text, 
       date varchar
     );

这里的关键是用逗号分隔json_input和json_to_recordset，这相当于隐式的CROSS JOIN，会把外层的weekId和数组里的每一行自动组合，确保每条插入的记录都带上对应的weekId。

2. 封装为PostgreSQL函数 #

如果需要反复调用这个逻辑，把它封装成函数会更方便维护：

CREATE OR REPLACE FUNCTION insert_timesheet(p_json json)
RETURNS void AS $$
BEGIN
  INSERT INTO timesheet(week_id, day_of_week, working_hours, description, date)
  SELECT 
    (p_json->>'weekId')::int AS week_id,
    x.day_of_week,
    x.working_hours,
    x.description,
    (x.date)::date AS date
  FROM json_to_recordset(p_json->'weekDays') AS x(
    day_of_week varchar, 
    working_hours real, 
    description text, 
    date varchar
  );
END;
$$ LANGUAGE plpgsql;

调用函数时直接传入JSON参数即可：

SELECT insert_timesheet('{ "weekId":20, "weekDays":[ { "day_of_week":"Monday", "working_hours":22.5, "description":"text", "date":"May 22, 2019" }, { "day_of_week":"Tuesday", "working_hours":22.5, "description":"text", "date":"May 22, 2019" } ] }'::json);

几个注意点 #

• 确保你的timesheet表已经包含week_id字段，如果没有，先执行ALTER TABLE timesheet ADD COLUMN week_id int;添加
• 类型转换要和表字段匹配：比如weekId如果是大整数就用::bigint，working_hours需要高精度的话可以用::numeric，日期类型如果是timestamp就用::timestamp
• 如果你的JSON是jsonb类型（PostgreSQL推荐的二进制JSON类型），只需把json_to_recordset换成jsonb_to_recordset，其他语法完全一致

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