寻求适用于Databricks的dbt宏：扁平化任意嵌套JSON列

核心逻辑拆解 #

Databricks没有直接对应FLATTEN的函数，但可以组合以下SQL能力实现等效效果：

• explode: 展开JSON数组元素
• json_object_keys: 提取JSON对象的所有键，配合explode遍历键值对
• LATERAL VIEW: 关联展开后的行，和Snowflake的LATERAL语义一致
• get_json_object/json_tuple: 提取JSON字段的具体值

分场景实现（数组/对象） #

场景1：扁平化JSON数组列 #

假设表中json_array_col列是数组类型JSON（如[{"id":1,"name":"a"},{"id":2,"name":"b"}]），展开数组元素：

SELECT
  t.*,
  flattened.value AS array_element
FROM
  my_delta_table t
LATERAL VIEW explode(from_json(json_array_col, 'array<json>')) flattened AS value

如果JSON结构固定，可替换array<json>为具体Struct类型（如array<struct<id:int, name:string>>），提升性能并获得类型校验。

场景2：扁平化JSON对象列 #

假设表中json_object_col列是对象类型JSON（如{"key1":"val1","key2":"val2"}），遍历键值对：

SELECT
  t.*,
  obj_keys.key AS json_key,
  get_json_object(json_object_col, concat('$.', obj_keys.key)) AS json_value
FROM
  my_delta_table t
LATERAL VIEW explode(json_object_keys(json_object_col)) obj_keys AS key

封装通用dbt宏 #

以下宏支持数组/对象两种类型，可指定目标列、别名：

{% macro flatten_json(column_name, alias='flattened', json_type='array') %}
  {% if json_type == 'array' %}
    LATERAL VIEW explode(from_json({{ column_name }}, 'array<json>')) {{ alias }} AS value
  {% elif json_type == 'object' %}
    LATERAL VIEW explode(json_object_keys({{ column_name }})) {{ alias }} AS key
    , get_json_object({{ column_name }}, concat('$.', {{ alias }}.key)) AS {{ alias }}_value
  {% else %}
    {% do exceptions.raise_compiler_error("Unsupported json_type: " ~ json_type ~ ". Must be 'array' or 'object'.") %}
  {% endif %}
{% endmacro %}

宏使用示例 #

1. 扁平化数组列：

SELECT
  t.*,
  flattened.value
FROM
  my_delta_table t
{{ flatten_json('json_array_col', alias='flattened', json_type='array') }}

2. 扁平化对象列：

SELECT
  t.*,
  flattened.key,
  flattened_value
FROM
  my_delta_table t
{{ flatten_json('json_object_col', alias='flattened', json_type='object') }}

处理多层嵌套JSON #

对于多层嵌套结构（如数组包含对象、对象包含数组），可链式调用宏：

SELECT
  t.*,
  arr_flattened.value AS level1_obj,
  obj_flattened.key AS level2_key,
  obj_flattened_value AS level2_value
FROM
  my_delta_table t
{{ flatten_json('nested_json_array', alias='arr_flattened', json_type='array') }}
{{ flatten_json('arr_flattened.value', alias='obj_flattened', json_type='object') }}

注意事项 #

• 固定结构的JSON优先使用具体Struct/Map类型，避免通用json类型带来的性能损耗
• 批量提取字段时，可用json_tuple替代多次get_json_object，简化代码
• 可扩展宏参数支持自定义JSON Schema，适配更复杂的业务场景

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