Databricks SQL解析嵌套JSON：多数组列Explode报错求助

报错信息：

[UNSUPPORTED_GENERATOR.MULTI_GENERATOR] The generator is not supported: only one generator allowed

原执行SQL：

SELECT
explode(array_join(from_json(food:Fruits.children[*].`Fruit Quantity`, 'array<string>'), ' - ')) AS `Fruit Quantity`,
explode(array_join(from_json(food:Fruits.children[*].`Fruit Weight`, 'array<string>'), ' - ')) AS `Fruit Weight`,
explode(array_join(from_json(food:Fruits.children[*].`Fruit Packaging`, 'array<string>'), ' - ')) AS `Fruit Packaging`
FROM
input_table

输入数据样例：

"breakfast": {
"carbs": [
{
"carbs name": "No food"
}

]

},
"Fruits": {
"batch": "batch01",
"children": [
{
"Fruit name": "apple01",
"Fruit Quantity": 3,
"Fruit Weight": "50g",
"Fruit Packaging": "Basket"
},
{
"Fruit name": "apple01",
"Fruit Quantity": 3,
"Fruit Weight": "50g",
"Fruit Packaging": ""
},
{
"Fruit name": "apple02",
"Fruit Quantity": 5,
"Fruit Weight": "100g",
"Fruit Packaging": "Foil"
}
]
}
} |
Day 2 | {dinner:["bread", "egg"]}

需求：

• 无CTE或子查询的SQL方案
• food列存在不同数据类型，需保留对这些列的独立处理能力，Lateral view explode会排除其他数据类型，不符合需求

直接对food:Fruits.children数组使用explode_outer（保留无Fruits的行），一次性解析结构体数组并提取字段，避免多次explode报错：

SELECT
  Day,
  -- 提取Fruits子项字段，无数据时返回NULL
  exploded_child.`Fruit Quantity`,
  exploded_child.`Fruit Weight`,
  exploded_child.`Fruit Packaging`,
  -- 保留对其他food字段的处理能力，示例提取dinner数组
  food:dinner AS dinner_list
FROM input_table
LATERAL VIEW explode_outer(from_json(food:Fruits.children, 'array<struct<`Fruit Quantity`:int, `Fruit Weight`:string, `Fruit Packaging`:string>>')) exploded_child

关键说明： #

1. explode_outer替代explode，确保没有Fruits.children的行（如Day2）不会被过滤，完整保留原数据
2. 用from_json直接将children解析为结构体数组，一次性获取所有子字段，避免多次生成器操作
3. 可同时保留对其他类型food字段的提取逻辑，满足不同数据结构的独立处理需求

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