Spark SQL中展开JSON：将所有键转换为列的实现方法

问题概述 #

需要把包含多层嵌套数组的JSON字符串转换为扁平的关系型表结构，将所有JSON键映射为表列，并展开嵌套数组的每个元素到独立行中。

输入数据 #

WITH dataset AS (
   SELECT '{
      "id": 1,
      "name": "John Doe",
      "age": 30,
      "contacts": [
         {
            "type": "email",
            "value": "john.doe@example.com"
         },
         {
            "type": "phone",
            "value": "555-1234"
         }
      ],
      "orders": [
         {
            "orderId": "A123",
            "products": [
               {
                  "productId": "P001",
                  "name": "Product 1",
                  "quantity": 2
               },
               {
                  "productId": "P002",
                  "name": "Product 2",
                  "quantity": 1
               }
            ],
            "totalAmount": 150.99
         },
         {
            "orderId": "B456",
            "products": [
               {
                  "productId": "P003",
                  "name": "Product 3",
                  "quantity": 3
               }
            ],
            "totalAmount": 75.50
         }
      ]
   }' AS myblob
)

预期输出 #

需要展开所有嵌套数组（contacts、orders、products），保留顶层字段并将嵌套字段映射为表列，最终得到类似你给出的扁平表结构（注：原始JSON中无street、city等地址字段，为示例补充字段，实际处理时可按需调整）。

解决方案（以Presto/Trino SQL为例） #

使用JSON解析函数和数组展开函数UNNEST逐层处理嵌套结构：

WITH dataset AS (
   SELECT '{
      "id": 1,
      "name": "John Doe",
      "age": 30,
      "contacts": [
         {
            "type": "email",
            "value": "john.doe@example.com"
         },
         {
            "type": "phone",
            "value": "555-1234"
         }
      ],
      "orders": [
         {
            "orderId": "A123",
            "products": [
               {
                  "productId": "P001",
                  "name": "Product 1",
                  "quantity": 2
               },
               {
                  "productId": "P002",
                  "name": "Product 2",
                  "quantity": 1
               }
            ],
            "totalAmount": 150.99
         },
         {
            "orderId": "B456",
            "products": [
               {
                  "productId": "P003",
                  "name": "Product 3",
                  "quantity": 3
               }
            ],
            "totalAmount": 75.50
         }
      ]
   }' AS myblob
)
SELECT
  data.id,
  data.name,
  data.age,
  -- 原始JSON无地址字段，此处按示例填充固定值，实际请替换为真实逻辑
  '123 Main St' AS street,
  'Anytown' AS city,
  'CA' AS state,
  '12345' AS zipcode,
  contact.type AS contact_type,
  contact.value AS contact_value,
  order_item.orderId AS order_id,
  product.productId AS product_id,
  product.name AS product_name,
  product.quantity,
  order_item.totalAmount
FROM dataset,
  JSON_PARSE(myblob) AS data,
  UNNEST(data.contacts) AS t(contact),
  UNNEST(data.orders) AS t(order_item),
  UNNEST(order_item.products) AS t(product);

关键步骤说明 #

1. JSON解析：JSON_PARSE将字符串类型的JSON转为可操作的JSON对象；
2. 数组展开：通过多次使用UNNEST，依次展开contacts、orders、products三层嵌套数组，每一层展开都会生成对应数量的行；
3. 字段映射：从各级JSON对象中提取字段，并重命名为符合表结构的列名；
4. 补充字段处理：针对示例中额外的地址字段，因原始JSON未提供，用固定值填充，实际场景中可根据真实JSON结构调整。

内容的提问来源于stack exchange，提问作者Pavan Aithal