PostgreSQL如何将JSON父级timestamp映射到嵌套数组元素并入库

核心逻辑：先提取JSON顶层的公共字段值，通过JSON数组拆分行函数将嵌套数组展开为多行记录，公共字段会自动匹配到每一条展开的数组记录上，直接批量插入即可，不需要手动循环遍历数组。

可直接使用的函数实现 #

如果你的入参使用PostgreSQL推荐的jsonb类型（PostgREST默认适配类型，查询性能更好），函数写法如下：

CREATE OR REPLACE FUNCTION insert_tbl_logs(input_data jsonb)
RETURNS void AS $$
DECLARE
  -- 提前提取顶层公共timestamp字段，转换为对应时间类型
  log_time timestamptz := (input_data ->> 'timestamp')::timestamptz;
BEGIN
  INSERT INTO tblLogs (timestamp, id, capacity, usage, available)
  SELECT
    log_time,
    (elem ->> 'id')::integer,
    (elem ->> 'capacity')::integer,
    (elem ->> 'usage')::integer,
    (elem ->> 'available')::integer
  -- 将uses嵌套数组拆分为逐行的JSON元素
  FROM jsonb_array_elements(input_data -> 'uses') AS elem;
END;
$$ LANGUAGE plpgsql;

如果你的入参使用普通json类型，只需要把jsonb_array_elements替换为json_array_elements，其余字段提取、类型转换逻辑完全一致。

快速测试写法 #

不需要封装函数也可以直接执行插入，方便你调试逻辑：

WITH raw_input AS (
  SELECT '{
  "timestamp": "2022-02-04T09:55:21+00:00",
  "uses": [
    {"id": 3, "name": "Name1", "capacity": 300, "usage": 0, "available": 300},
    {"id": 4, "name": "Name2", "capacity": 450, "usage": 120, "available": 330},
    {"id": 5, "name": "Name3", "capacity": 50, "usage": 11, "available": 39}
  ]
}'::jsonb AS data
)
INSERT INTO tblLogs (timestamp, id, capacity, usage, available)
SELECT
  (data ->> 'timestamp')::timestamptz,
  (elem ->> 'id')::integer,
  (elem ->> 'capacity')::integer,
  (elem ->> 'usage')::integer,
  (elem ->> 'available')::integer
FROM raw_input, jsonb_array_elements(data -> 'uses') AS elem;

注意事项 #

• 操作符->>用于提取JSON字段的文本值，必须显式转换为和目标表字段匹配的数据类型，避免隐式转换导致的类型错误
• 不需要手动编写循环遍历数组，PostgreSQL的集合式操作会自动完成数组拆行，顶层提取的公共字段会自动关联到所有拆分出的数组行
• 对接PostgREST时，只需要给函数配置正确的访问权限，入参设置为jsonb类型即可直接接收前端传入的嵌套JSON结构

内容的提问来源于stack exchange，提问作者jw-sgt