Databricks中SparkSQL/PySpark查询JSON嵌套数组的方法咨询

• ce_data字段被识别为string类型的核心原因：你读取的外层JSON文件中，ce_data字段本身是以字符串形式存储的嵌套JSON，Spark读取JSON文件时只会解析外层JSON结构，不会自动对字符串类型的字段值做二次JSON解析，因此printSchema只会显示其为string类型，不会识别内部嵌套的属性和数组结构。
• 报错原因：你直接使用.语法访问字符串类型字段的嵌套属性，Spark要求.语法只能作用于struct类型字段，因此抛出类型不匹配错误。

无需额外导入第三方依赖，以下操作均基于Spark内置函数实现，Databricks环境默认支持。

步骤1：定义ce_data内部嵌套结构的Schema #

你可以选择两种方式定义Schema：

• 方式1：通过JSON样例自动推导（适合快速测试）

-- 替换样例字符串为你实际的ce_data单条内容即可自动生成Schema
DECLARE ce_data_schema STRING;
SET ce_data_schema = (SELECT schema_of_json('{"lodgedDate":"1970-03-03","documentsUploaded":[],"additionalExecutorList":[]}'));

• 方式2：手动定义DDL格式Schema（结构更可控，适合生产环境）

DECLARE ce_data_schema STRING;
-- 以下STRUCT内部的字段名、类型、数组嵌套结构请根据你实际的ce_data结构调整
SET ce_data_schema = 'STRUCT<lodgedDate: STRING, documentsUploaded: ARRAY<STRUCT<docId: STRING, docName: STRING>>, additionalExecutorList: ARRAY<STRUCT<executorId: STRING, executorName: STRING>>>';

如果你使用的Databricks Runtime版本不支持DECLARE语法，可以直接把Schema字符串写在后续from_json函数的第二个参数位置。

步骤2：将string类型的ce_data转为struct类型并查询 #

基础查询：筛选lodgedDate符合条件的数据 #

SELECT 
  ce_data_struct.* 
FROM (
  SELECT 
    from_json(ce_data, ${ce_data_schema}) AS ce_data_struct 
  FROM testtable
) t
WHERE ce_data_struct.lodgedDate = '1970-03-03'

你也可以创建临时视图简化后续查询：

CREATE OR REPLACE TEMP VIEW testtable_parsed AS
SELECT 
  *,
  from_json(ce_data, ${ce_data_schema}) AS ce_data_struct
FROM testtable;

-- 后续直接查询临时视图即可
SELECT * FROM testtable_parsed WHERE ce_data_struct.lodgedDate = '1970-03-03';

数组查询：访问嵌套数组中的元素 #

如果要提取数组字段的内容，可以用explode函数展开数组后查询，示例如下：

-- 查询指定lodgedDate下所有上传的文档名称
SELECT 
  doc.docName
FROM testtable_parsed
LATERAL VIEW explode(ce_data_struct.documentsUploaded) exploded_docs AS doc
WHERE ce_data_struct.lodgedDate = '1970-03-03';

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