如何在Azure Data Lake中使用query_file查询JSON文件数据子集？

问题原因 #

你的JSON文件是对象数组格式（如[{"col1":1,"col2":2},...]），默认使用DelimitedJsonDialect配合SELECT * from DataLakeStorage能返回全量数据，但直接指定列名时，查询语法无法定位到数组内部的对象属性，导致返回空结果。

可行解决方案：用OPENJSON解析数组+指定列提取 #

ADLS的query_file支持SQL-like语法，通过OPENJSON函数可以展开顶层JSON数组，再提取目标列属性。同时需要适配JSON数组的文件格式。

修改后代码示例 #

import io
import pandas as pd

from azure.storage.filedatalake import DataLakeServiceClient, JsonDialect
from azure.identity import ClientSecretCredential

# 初始化客户端（保持你的原有配置）
adls_client = DataLakeServiceClient(account_url='...', credential=ClientSecretCredential(...))
filesystem_client = adls_client.get_file_system_client('...')
file_client = filesystem_client.get_file_client('test_dataframe.json')

# 针对完整JSON数组，使用JsonDialect而非DelimitedJsonDialect
input_format = JsonDialect()
# 构造查询语句：展开数组并提取指定列
reader = file_client.query_file(
    """
    SELECT 
        JSON_VALUE(j.value, '$.col1') AS col1,
        JSON_VALUE(j.value, '$.col2') AS col2  # 可添加更多需要的列
    FROM OPENJSON(BulkColumn) AS j
    """,
    file_format=input_format
)

# 读取查询结果并转为DataFrame
json_str = reader.readall().decode('utf8')
# 查询返回JSON Lines格式，需指定lines=True
df = pd.read_json(io.StringIO(json_str), lines=True)

关键说明 #

1. 文件格式适配：将DelimitedJsonDialect替换为JsonDialect，前者用于每行一个JSON对象的场景（JSON Lines），后者适配整个文件为单个JSON数组的格式。
2. 查询语法解析：
   • BulkColumn是ADLS查询内置变量，指代整个文件的内容。
   • OPENJSON(BulkColumn)将顶层JSON数组展开为一行行的对象记录。
   • JSON_VALUE(j.value, '$.col1')提取数组中每个对象的col1属性，AS col1指定返回的列名。
3. 结果解析：查询返回的是JSON Lines格式（每行一个对象），因此pd.read_json需要添加lines=True参数才能正确解析。

扩展用法 #

• 如需筛选行，可添加WHERE条件：

  SELECT JSON_VALUE(j.value, '$.col1') AS col1
  FROM OPENJSON(BulkColumn) AS j
  WHERE JSON_VALUE(j.value, '$.col1') > 10
  

• 如需提取嵌套属性，调整JSON路径即可：

  JSON_VALUE(j.value, '$.nested_object.inner_col') AS inner_col
  

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