SQL Server 2019中JSON_QUERY路径错误致无法提取答案数据

核心问题分析 #

你提取Answers时仅返回表头无数据，大概率是JSON路径错误或未正确处理嵌套数组的解析规则：

• Answers并非直接位于JSON根节点，而是嵌套在SectionSubmission数组的每个元素中
• 解析嵌套数组时未使用AS JSON关键字保留子JSON的结构，导致OPENJSON无法识别数组格式

正确解决方案 #

1. 确认JSON结构（以典型司机预启动检查表结构为例） #

假设你的FormDetails列JSON结构如下：

{
  "SubmitId": 1001,
  "FormSubmission": {
    "FormId": "DRIVER_PRESTART",
    "SubmitDate": "2024-06-01T08:30:00",
    "DriverId": "D007"
  },
  "SectionSubmission": [
    {
      "SectionId": "SEC001",
      "SectionName": "车辆基础检查",
      "Answers": [
        {
          "QuestionId": "Q001",
          "QuestionText": "车辆外观是否完好？",
          "AnswerValue": "是",
          "IsQualified": true
        },
        {
          "QuestionId": "Q002",
          "QuestionText": "刹车系统是否正常？",
          "AnswerValue": "是",
          "IsQualified": true
        }
      ]
    },
    {
      "SectionId": "SEC002",
      "SectionName": "证件检查",
      "Answers": [
        {
          "QuestionId": "Q003",
          "QuestionText": "驾驶证是否在有效期内？",
          "AnswerValue": "是",
          "IsQualified": true
        }
      ]
    }
  ]
}

2. 正确提取Answers的SQL代码 #

无需使用WHILE循环，通过多层CROSS APPLY OPENJSON即可一次性解析所有嵌套数据：

SELECT
  -- 主表主键
  dpc.SubmitId,
  -- FormSubmission字段
  fs.FormId,
  fs.SubmitDate,
  fs.DriverId,
  -- SectionSubmission字段
  ss.SectionId,
  ss.SectionName,
  -- Answers字段
  ans.QuestionId,
  ans.QuestionText,
  ans.AnswerValue,
  ans.IsQualified
FROM DriverPreStartChecks dpc
-- 解析根节点下的FormSubmission对象
CROSS APPLY OPENJSON(dpc.FormDetails, '$.FormSubmission')
WITH (
  FormId VARCHAR(50) '$.FormId',
  SubmitDate DATETIME '$.SubmitDate',
  DriverId VARCHAR(20) '$.DriverId'
) fs
-- 展开SectionSubmission数组，同时用AS JSON保留Answers的JSON结构
CROSS APPLY OPENJSON(dpc.FormDetails, '$.SectionSubmission')
WITH (
  SectionId VARCHAR(50) '$.SectionId',
  SectionName NVARCHAR(100) '$.SectionName',
  Answers NVARCHAR(MAX) '$.Answers' AS JSON -- 关键：标记为JSON类型，以便后续解析
) ss
-- 展开每个Section下的Answers数组
CROSS APPLY OPENJSON(ss.Answers)
WITH (
  QuestionId VARCHAR(50) '$.QuestionId',
  QuestionText NVARCHAR(200) '$.QuestionText',
  AnswerValue NVARCHAR(100) '$.AnswerValue',
  IsQualified BIT '$.IsQualified'
) ans

问题排查步骤 #

1. 验证JSON路径正确性：执行以下语句检查是否能正确获取Answers数组

SELECT
  SubmitId,
  -- 检查第一个Section的Answers
  JSON_QUERY(FormDetails, '$.SectionSubmission[0].Answers') AS FirstSectionAnswers
FROM DriverPreStartChecks
WHERE SubmitId = 你的测试SubmitId

• 如果返回NULL，说明你的JSON中Answers的层级与假设不符，需要调整路径
• 如果返回有效JSON数组，说明路径正确，问题出在解析时未使用AS JSON

2. 检查原代码的错误点

• 若你之前直接使用JSON_QUERY(FormDetails, '$.Answers')，路径错误，因为Answers不在根节点
• 若你解析SectionSubmission时未加AS JSON，OPENJSON会将Answers视为普通字符串，无法展开数组

替代方案：若JSON结构有差异 #

如果你的Answers是直接位于根节点的数组（而非嵌套在SectionSubmission下），则简化为：

SELECT
  dpc.SubmitId,
  fs.FormId,
  ans.QuestionId,
  ans.AnswerValue
FROM DriverPreStartChecks dpc
CROSS APPLY OPENJSON(dpc.FormDetails, '$.FormSubmission')
WITH (FormId VARCHAR(50) '$.FormId') fs
CROSS APPLY OPENJSON(dpc.FormDetails, '$.Answers')
WITH (
  QuestionId VARCHAR(50) '$.QuestionId',
  AnswerValue NVARCHAR(100) '$.AnswerValue'
) ans

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