如何将单个JSON参数数据插入PostgreSQL的两个关联表？

我之前也碰到过类似的JSON拆分插入难题，用PostgreSQL的jsonb系列函数完全能搞定，咱们一步步来梳理：

1. 先明确表结构与示例JSON #

首先假设你的两张表结构如下（如果和实际表字段有差异，直接调整对应字段即可）：

CREATE TABLE Item (
    item_id VARCHAR(50) PRIMARY KEY,
    fulfiller_id VARCHAR(50) NOT NULL,
    order_id VARCHAR(50) NOT NULL,
    sku_code VARCHAR(50) NOT NULL,
    product_name VARCHAR(100), -- 示例商品额外字段，按需添加
    price NUMERIC(10,2)        -- 示例商品额外字段，按需添加
);

CREATE TABLE ItemDetails (
    id SERIAL PRIMARY KEY,
    item_id VARCHAR(50) REFERENCES Item(item_id),
    order_details_url VARCHAR(255) NOT NULL,
    task_id VARCHAR(50) NOT NULL,
    quantity INT NOT NULL
);

再假设你要处理的JSON参数结构类似这样（包含订单级字段、商品数组，每个商品下嵌套详情数组）：

{
  "orderId": "ORD-12345",
  "fulfillerId": "FUL-67890",
  "items": [
    {
      "itemId": "ITEM-001",
      "skuCode": "SKU-LAPTOP-001",
      "productName": "XPS 15 Laptop",
      "price": 1299.99,
      "details": [
        {
          "orderDetailsUrl": "https://example.com/details/ORD-12345/ITEM-001/1",
          "taskId": "TASK-001",
          "quantity": 1
        },
        {
          "orderDetailsUrl": "https://example.com/details/ORD-12345/ITEM-001/2",
          "taskId": "TASK-002",
          "quantity": 2
        }
      ]
    },
    {
      "itemId": "ITEM-002",
      "skuCode": "SKU-MOUSE-001",
      "productName": "Wireless Mouse",
      "price": 29.99,
      "details": [
        {
          "orderDetailsUrl": "https://example.com/details/ORD-12345/ITEM-002/1",
          "taskId": "TASK-003",
          "quantity": 1
        }
      ]
    }
  ]
}

2. 用WITH语句一次性完成双表插入 #

PostgreSQL的WITH子句可以先把JSON解析后的临时数据存储起来，再分别插入两张表，还能保证数据一致性：

WITH parsed_json AS (
    -- 解析订单级字段，同时展开商品数组
    SELECT
        j->>'orderId' AS order_id,
        j->>'fulfillerId' AS fulfiller_id,
        -- 把items数组拆分成单行商品对象
        jsonb_array_elements(j->'items') AS item_json
    FROM (
        -- 替换成你的JSON参数，比如用变量或直接传入JSON字符串
        SELECT '{"orderId": "ORD-12345", "fulfillerId": "FUL-67890", "items": [{"itemId": "ITEM-001", "skuCode": "SKU-LAPTOP-001", "productName": "XPS 15 Laptop", "price": 1299.99, "details": [{"orderDetailsUrl": "https://example.com/details/ORD-12345/ITEM-001/1", "taskId": "TASK-001", "quantity": 1}, {"orderDetailsUrl": "https://example.com/details/ORD-12345/ITEM-001/2", "taskId": "TASK-002", "quantity": 2}]}, {"itemId": "ITEM-002", "skuCode": "SKU-MOUSE-001", "productName": "Wireless Mouse", "price": 29.99, "details": [{"orderDetailsUrl": "https://example.com/details/ORD-12345/ITEM-002/1", "taskId": "TASK-003", "quantity": 1}]}]}'::jsonb AS j
    ) AS input
),
inserted_items AS (
    -- 先插入Item表，返回已插入的item_id用于关联
    INSERT INTO Item (item_id, fulfiller_id, order_id, sku_code, product_name, price)
    SELECT
        item_json->>'itemId' AS item_id,
        fulfiller_id,
        order_id,
        item_json->>'skuCode' AS sku_code,
        item_json->>'productName' AS product_name,
        (item_json->>'price')::NUMERIC(10,2) AS price
    FROM parsed_json
    RETURNING item_id
)
-- 插入ItemDetails表，关联Item表的item_id
INSERT INTO ItemDetails (item_id, order_details_url, task_id, quantity)
SELECT
    ii.item_id,
    detail->>'orderDetailsUrl' AS order_details_url,
    detail->>'taskId' AS task_id,
    (detail->>'quantity')::INT AS quantity
FROM parsed_json pj
JOIN inserted_items ii ON pj.item_json->>'itemId' = ii.item_id,
-- 展开每个商品的details数组
jsonb_array_elements(pj.item_json->'details') AS detail;

3. 核心函数说明 #

• jsonb_array_elements(j->'items'): 将JSON中的items数组拆分成多行，每行对应一个独立的商品对象
• jsonb_array_elements(pj.item_json->'details'): 把单个商品下的details数组拆分成多行，对应每条详情记录
• ->>: 提取JSON字段的文本值，->则保留JSON对象类型，根据实际需求选择
• RETURNING: 插入Item表后返回已插入的item_id，用来关联插入ItemDetails表，避免重复解析数据

4. 适配单个商品的JSON场景 #

如果你的JSON是单个商品（无items数组），只需微调SQL即可：

WITH parsed_json AS (
    SELECT
        j->>'orderId' AS order_id,
        j->>'fulfillerId' AS fulfiller_id,
        j->'item' AS item_json -- 假设单个商品放在item字段下
    FROM (
        SELECT '{"orderId": "ORD-12345", "fulfillerId": "FUL-67890", "item": {"itemId": "ITEM-001", "skuCode": "SKU-LAPTOP-001", "productName": "XPS 15 Laptop", "price": 1299.99, "details": [{"orderDetailsUrl": "https://example.com/details/ORD-12345/ITEM-001/1", "taskId": "TASK-001", "quantity": 1}]}}'::jsonb AS j
    ) AS input
),
inserted_items AS (
    INSERT INTO Item (item_id, fulfiller_id, order_id, sku_code, product_name, price)
    SELECT
        item_json->>'itemId' AS item_id,
        fulfiller_id,
        order_id,
        item_json->>'skuCode' AS sku_code,
        item_json->>'productName' AS product_name,
        (item_json->>'price')::NUMERIC(10,2) AS price
    FROM parsed_json
    RETURNING item_id
)
INSERT INTO ItemDetails (item_id, order_details_url, task_id, quantity)
SELECT
    ii.item_id,
    detail->>'orderDetailsUrl' AS order_details_url,
    detail->>'taskId' AS task_id,
    (detail->>'quantity')::INT AS quantity
FROM parsed_json pj
JOIN inserted_items ii ON pj.item_json->>'itemId' = ii.item_id,
jsonb_array_elements(pj.item_json->'details') AS detail;

你可以根据实际的JSON结构和表字段，调整对应的字段名、类型转换逻辑就行～

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