如何通过Databricks SQL处理含不同键的JSON数组并保留类型

解决方案概述 #

通过将原始JSON字符串解析为结构化的数组+Map类型，再利用数组遍历和键值提取函数，将每个异构JSON对象转换为统一的键值对结构体，同时保留原始数据类型。

具体SQL实现 #

假设你的Delta表名为vendor_data，存储JSON数组的列名为json_array_col，执行以下SQL：

WITH parsed_data AS (
  SELECT
    -- 将JSON字符串解析为数组，每个元素是Map结构，variant类型保留原始数据类型
    from_json(json_array_col, 'array<map<string, variant>>') AS parsed_array
  FROM vendor_data
),
transformed_result AS (
  SELECT
    -- 生成结构化的键值对数组
    transform(
      parsed_array,
      obj -> struct(
        map_keys(obj)[0] AS key_name,
        map_values(obj)[0] AS key_value
      )
    ) AS key_value_array,
    -- 若需要输出JSON字符串格式，使用to_json转换
    to_json(
      transform(
        parsed_array,
        obj -> struct(
          map_keys(obj)[0] AS key_name,
          map_values(obj)[0] AS key_value
        )
      )
    ) AS key_value_json
  FROM parsed_data
)
SELECT * FROM transformed_result;

关键步骤解释 #

1. 解析JSON字符串为结构化类型
   使用from_json函数，指定schema为array<map<string, variant>>：

   • array<...>对应原始的JSON数组结构
   • map<string, variant>将每个单个键值对的JSON对象转换为Map，variant类型支持存储任意原始数据类型（整数、字符串等），避免类型丢失。

2. 遍历数组生成统一结构体
   使用transform函数遍历解析后的数组：

   • map_keys(obj)[0]提取单个Map中的唯一键名（匹配示例中每个JSON对象仅一个键的场景）
   • map_values(obj)[0]提取对应的值，保留原始类型
   • struct(...)将键名和值封装为统一格式的结构体，最终组成目标数组。

3. 可选：转换为JSON字符串
   若需要输出JSON格式的字符串结果，使用to_json函数将结构化数组转换为JSON字符串，得到你需要的[{"key_name":"key1","key_value":10},...]格式。

解决原有问题的原因 #

你之前使用explode拆分后仅得到JSON字符串，未将其解析为结构化类型，导致无法提取未知键且丢失原始类型。本方案先将JSON解析为Map结构，直接操作键值对，既保留了数据类型，又能动态提取未知键名。

内容的提问来源于stack exchange，提问作者Anupam Chand