如何在SQL中从JSON字符串提取各item_id对应的serial_numbers

原代码的问题在于路径错误，serial_numbers 并非最外层JSON的直接属性，而是嵌套在 results[0].order_lines 数组的每个元素中，且本身是数组类型，无法直接通过单层WITH子句提取。以下是两种正确的实现方案：

场景1：每个序列号单独一行显示 #

DECLARE @json NVARCHAR(MAX);
SET @json=N'[{
  "next": null,
  "previous": null,
  "results": [
    {
      "order_id": 79727,
      "original_transaction_date": "2020-04-04T21:31:13.270741+08:00",
      "order_lines": [
        {
          "customs_value": {
            "amount": 320,
            "currency": "USD"
          },
          "quantity": 1,
          "item_id": 9982,
          "item_sku": "SP7",
          "product_id": 978,
          "has_batteries": false,
          "customs_description": "Portable Microcurrent & Electrolysis Generator",
          "local_customs_description": "便携式微电流和电解发生器",
          "has_expiration": false,
          "gross_width": 15,
          "description": "Silver Pulser 7",
          "serial_numbers": [
            "SP000000001"
          ],
          "gross_weight": 0.793,
          "product_quantity": 1,
          "packaging_type": "ship_ready",
          "harmonized_code": "8543.70.00.00",
          "item_type": "base_item",
          "upc": null,
          "nmfc": null,
          "image_url": "https://s3.amazonaws.com/xxxx-client-uploads/items/images/photo/e8ea41b3cf424dca9cbbbffd5613dd19.jpg",
          "gross_length": 20,
          "has_liquids": false,
          "customs_category": "",
          "country_of_manufacture": "CN",
          "sku": "SP7",
          "carton_sku": null,
          "gross_height": 8,
          "tax": null,
          "duty": null
        },
        {
          "customs_value": {
            "amount": 225,
            "currency": "USD"
          },
          "quantity": 2,
          "item_id": 9970,
          "item_sku": "LWP1",
          "product_id": 968,
          "has_batteries": false,
          "customs_description": "Flexible LED Light Pad",
          "local_customs_description": "",
          "has_expiration": false,
          "gross_width": 18,
          "description": "Lightworks Pad 1",
          "serial_numbers": [
            "LP000000001",
            "LP000000002"
          ],
          "gross_weight": 2.2,
          "product_quantity": 1,
          "packaging_type": "xxxx",
          "harmonized_code": "8541.40.20.00",
          "item_type": "base_item",
          "upc": null,
          "nmfc": null,
          "image_url": "https://s3.amazonaws.com/xxxx-client-uploads/items/images/photo/554f8f49363e437c80746618b21d909d.jpg",
          "gross_length": 8,
          "has_liquids": false,
          "customs_category": "",
          "country_of_manufacture": "CN",
          "sku": "LWP1",
          "carton_sku": null,
          "gross_height": 33,
          "tax": null,
          "duty": null
        },
        {
          "customs_value": {
            "amount": 395,
            "currency": "USD"
          },
          "quantity": 3,
          "item_id": 9980,
          "item_sku": "MP6",
          "product_id": 977,
          "has_batteries": false,
          "customs_description": "Portable Magnetic Field Generator",
          "local_customs_description": "便携式磁场发生器",
          "has_expiration": false,
          "gross_width": 22,
          "description": "Magnetic Pulser 6",
          "serial_numbers": [
            "MP000000001",
            "MP000000002",
            "MP000000003"
          ],
          "gross_weight": 2.2,
          "product_quantity": 1,
          "packaging_type": "xxxxx",
          "harmonized_code": "8543.70.00.00",
          "item_type": "base_item",
          "upc": null,
          "nmfc": null,
          "image_url": "https://s3.amazonaws.com/xxxx-client-uploads/items/images/photo/c03243581aa847adb172d4d90fcf71d2.jpg",
          "gross_length": 35,
          "has_liquids": false,
          "customs_category": "",
          "country_of_manufacture": "CN",
          "sku": "MP6",
          "carton_sku": null,
          "gross_height": 9,
          "tax": null,
          "duty": null
        }
      ],
      "shipping_address": {
        "country": "CA",
        "addressee": "Roger Rabbit",
        "address_1": "123 Bunny Hole Lane",
        "address_2": "",
        "address_3": "",
        "city": "Peter Cottontail Town",
        "state": "BC",
        "postal_code": "V0H1K0",
        "company": "",
        "phone": "2505551212",
        "email": "jd@sota.com",
        "tax_id": ""
      },
      "is_editable": false,
      "status": "fulfilled",
      "exception_code": "does_not_meet_courier_requirements___restrictions",
      "tax_paid_by": "dap",
      "insurance_value": {
        "amount": 50,
        "currency": "USD"
      },
      "shipping_cost_value": null,
      "shipping_cost_value_tax": null,
      "source": "Fulfillment Portal",
      "customer_reference": "12345678",
      "reference": "FS0079727",
      "create_date": "2020-04-04T21:31:13.289517+08:00",
      "update_date": "2020-04-04T23:16:48.407941+08:00",
      "shipment_date": "2020-04-04T21:31:13.275002+08:00",
      "courier_id": 20,
      "courier": {
        "courier_id": 20,
        "name": "Hong Kong Post Air Parcel",
        "aftership_slug": null
      },
      "tracking_number": "KK9813xxxx97006HK",
      "tracking_url": "https://track.aftership.com/xxxx",
      "tracking_status": null,
      "customer_shipping_option": "",
      "order_type": "stock",
      "cancellation_fee": "3.00",
      "fulfilment_date": "2020-04-04T23:15:31.982284+08:00",
      "warehouse": {
        "name": "Warehouse #1",
        "id": 11,
        "local_address": "仓库本地地址18",
        "default_address": "55 xxxxxxSt, Suite 512, Tsing Yi,  HK, HK, 852 3706 8391"
      },
      "tags": [],
      "reason_of_export": "purchase",
      "remarks": "",
      "commercial_invoice": "https://xxxxx-shipping.s3.amazonaws.com/commercial_invoice/a8dd5bb8f72f44a8a2537b7a29bac347.pdf",
      "shipping_label": null,
      "attachment_file": null
    }
  ]
}]';

-- 提取每个item_id对应的所有序列号，每行一个序列号
SELECT 
    ol.item_id,
    sn.serial_number
FROM OPENJSON(@json, '$.results')
WITH (
    order_lines NVARCHAR(MAX) '$.order_lines' AS JSON
) AS res
CROSS APPLY OPENJSON(res.order_lines)
WITH (
    item_id INT '$.item_id',
    serial_numbers NVARCHAR(MAX) '$.serial_numbers' AS JSON
) AS ol
CROSS APPLY OPENJSON(ol.serial_numbers)
WITH (
    serial_number VARCHAR(50) '$'
) AS sn;

场景2：每个item_id一行，序列号用逗号分隔合并 #

-- 合并每个item_id的序列号为单行字符串
SELECT 
    ol.item_id,
    STRING_AGG(sn.serial_number, ', ') AS serial_numbers
FROM OPENJSON(@json, '$.results')
WITH (
    order_lines NVARCHAR(MAX) '$.order_lines' AS JSON
) AS res
CROSS APPLY OPENJSON(res.order_lines)
WITH (
    item_id INT '$.item_id',
    serial_numbers NVARCHAR(MAX) '$.serial_numbers' AS JSON
) AS ol
CROSS APPLY OPENJSON(ol.serial_numbers)
WITH (
    serial_number VARCHAR(50) '$'
) AS sn
GROUP BY ol.item_id;

代码说明 #

1. 第一层OPENJSON(@json, '$.results')：定位到JSON中的results数组，提取其中的order_lines数组（标记为AS JSON以便后续解析）
2. 第二层CROSS APPLY OPENJSON(res.order_lines)：解析每个订单的order_lines数组，获取每个商品的item_id和serial_numbers数组（同样标记为AS JSON）
3. 第三层CROSS APPLY OPENJSON(ol.serial_numbers)：解析每个商品的serial_numbers数组，得到单个序列号
4. 场景2使用STRING_AGG函数将同一item_id的所有序列号合并为一个字符串，适合需要紧凑结果的场景

内容的提问来源于stack exchange，提问作者Russell Torlage