如何在ADF与SQL中解析含未知编号问卷项的嵌套JSON数据？

一、SQL Server 解决方案 #

利用OPENJSON配合CROSS APPLY可以遍历未知键的嵌套对象，无需硬编码问题编号。假设你的JSON结构如下：

{
  "survey_id": "S123",
  "questions": {
    "q_789": {"text": "你的年龄？", "answer": "30"},
    "q_123": {"text": "你的职业？", "answer": "工程师"}
  }
}

执行以下SQL语句即可提取所有问题记录：

SELECT 
  j.survey_id,
  q.question_id,
  q.question_text,
  q.answer
FROM OPENJSON(@json)
WITH (
  survey_id NVARCHAR(50) '$.survey_id',
  questions NVARCHAR(MAX) '$.questions' AS JSON
) j
CROSS APPLY OPENJSON(j.questions)
WITH (
  question_text NVARCHAR(MAX) '$.text',
  answer NVARCHAR(MAX) '$.answer'
) q

核心逻辑：先用OPENJSON解析外层结构，将questions字段作为JSON类型传递；再通过CROSS APPLY OPENJSON遍历questions的所有键值对，键即为问题编号，同时提取内部的文本和答案。

二、Azure Data Factory (ADF) 解决方案 #

使用ADF数据流的派生列+展开组件处理：

1. 数据源配置：导入JSON数据集，确保嵌套的questions字段被识别为复杂类型
2. 添加派生列：生成包含所有问题的数组列，表达式为：

   mapValues(questions, (key, value) => merge(value, createMap('question_id', key)))
   

   该表达式会将questions对象转换为数组，每个元素包含question_id、text、answer三个字段
3. 添加展开组件：选择上述生成的数组列作为展开对象，展开后即可得到每条问题对应一行的扁平结构
4. 写入SQL表：将展开后的数据流输出到目标SQL表

三、替代工具推荐 #

如果ADF和SQL的方式仍无法满足需求，可尝试以下工具：

• Azure Functions：编写Python/C#脚本，直接调用API获取JSON，遍历对象的所有键生成记录后写入SQL。完全自定义逻辑，适配任意JSON结构
• Python Pandas：利用列表推导式将嵌套对象转换为扁平化列表，再通过pandas.DataFrame写入SQL。示例代码：

  import pandas as pd
  
  # 假设json_data为API返回的JSON数据
  questions_list = [
      {"survey_id": json_data["survey_id"], "question_id": k, **v} 
      for k, v in json_data["questions"].items()
  ]
  df = pd.DataFrame(questions_list)
  # df.to_sql('survey_responses', con=your_sql_connection, if_exists='append', index=False)
  

• Azure Logic Apps：通过"Parse JSON"动作解析数据后，使用"Select"动作配合"Get items from object"遍历所有问题键，生成结构化数组后插入SQL表，可视化配置无需大量代码

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