如何用SQL提取JSON中嵌套的addresses等字段？

原始JSON数据 #

{
  "customer": {
    "spendingHold": false,
    "createdAt": "2023-03-08T00:00:00.000Z",
    "addresses": [
      {
        "country": "USA",
        "preferences": {
          "contact": {
            "allowed": {
              "mail": true
            }
          }
        },
        "city": "Place",
        "postalCode": "11111",
        "street1": "123 Circle",
        "street2": null,
        "id": "1234567890",
        "type": "home",
        "region": "ST",
        "primary": true
      }
    ],
    "contact": {
      "allowed": {
        "times": null,
        "transactional": true
      }
    }
  },
  "creationSource": "created"
}

已实现的基础SQL #

SELECT
  JSON_EXTRACT_SCALAR(json, "$.customer.spendingHold") AS spending_hold
FROM dataset

目标查询结果 #

解决方案SQL #

针对addresses数组及嵌套字段的提取，需要先展开数组再提取字段，使用JSON_EXTRACT_ARRAY配合UNNEST实现：

SELECT
  JSON_EXTRACT_SCALAR(json, "$.customer.spendingHold") AS spendingHold,
  JSON_EXTRACT_SCALAR(json, "$.customer.createdAt") AS createdAt,
  JSON_EXTRACT_SCALAR(addr, "$.country") AS country,
  JSON_EXTRACT_SCALAR(addr, "$.preferences.contact.allowed.mail") AS mail,
  JSON_EXTRACT_SCALAR(addr, "$.city") AS city
FROM dataset,
UNNEST(JSON_EXTRACT_ARRAY(json, "$.customer.addresses")) AS addr

说明 #

1. JSON_EXTRACT_ARRAY(json, "$.customer.addresses")：提取customer节点下的addresses数组
2. UNNEST(...) AS addr：将数组展开为独立行，每一行对应一个地址对象
3. 从展开后的addr对象中，通过JSON路径提取嵌套的country、mail、city字段

若存在多个地址记录，该查询会为每个地址生成一行结果，符合关系型数据表的结构逻辑。

内容的提问来源于stack exchange，提问作者Showad Huda