Databricks Spark SQL动态JSON数组索引报错及无Schema展开求助

Spark SQL的col:[index]语法仅支持字面量整数索引，不允许引用列作为动态索引，因此使用a:[b]会触发语法错误。

无需指定复杂Schema，可通过以下两种方法实现：

方法1：通用Schema解析+Explode #

将JSON字符串转为array<map<string, string>>类型（通用Schema，适配任意键值对结构的JSON对象），再通过explode展开数组：

SELECT exploded_item
FROM VALUES(
  '[{"name": "Let Us C", "author": "Yashavant Kanetkar"}, {"name": "Rich Dad Poor Dad", "author": "Robert Kiyosaki "}, {"name": "Introduction to Algorithms", "author": "Cormen"}]'
) AS T(a)
LATERAL VIEW explode(from_json(a, 'array<map<string, string>>')) exploded_table AS exploded_item;

执行结果：

如果需要将map的键值对拆分为单独列，可直接引用map的键：

SELECT 
  exploded_item['name'] AS book_name,
  exploded_item['author'] AS book_author
FROM VALUES(
  '[{"name": "Let Us C", "author": "Yashavant Kanetkar"}, {"name": "Rich Dad Poor Dad", "author": "Robert Kiyosaki "}, {"name": "Introduction to Algorithms", "author": "Cormen"}]'
) AS T(a)
LATERAL VIEW explode(from_json(a, 'array<map<string, string>>')) exploded_table AS exploded_item;

方法2：自动推断Schema+Explode #

如果JSON结构相对固定，可通过schema_of_json自动推断Schema，再解析展开：

-- 先获取自动推断的Schema
WITH sample_data AS (
  SELECT '[{"name": "Let Us C", "author": "Yashavant Kanetkar"}, {"name": "Rich Dad Poor Dad", "author": "Robert Kiyosaki "}, {"name": "Introduction to Algorithms", "author": "Cormen"}]' AS json_str
)
SELECT schema_of_json(json_str) AS json_schema FROM sample_data;

-- 使用推断出的Schema解析并展开
SELECT exploded_item
FROM VALUES(
  '[{"name": "Let Us C", "author": "Yashavant Kanetkar"}, {"name": "Rich Dad Poor Dad", "author": "Robert Kiyosaki "}, {"name": "Introduction to Algorithms", "author": "Cormen"}]'
) AS T(a)
LATERAL VIEW explode(from_json(a, 'array<struct<author:string,name:string>>')) exploded_table AS exploded_item;

如果需要根据列值动态获取数组元素，可使用element_at函数（索引从1开始）：

SELECT element_at(from_json(a, 'array<map<string, string>>'), b) AS target_item
FROM VALUES(
  '[{"name": "Let Us C", "author": "Yashavant Kanetkar"}, {"name": "Rich Dad Poor Dad", "author": "Robert Kiyosaki "}, {"name": "Introduction to Algorithms", "author": "Cormen"}]',
  1
) AS T(a,b);

执行结果：

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