SQL Server 2016将API JSON解析到表：嵌套数据提取问题求助

我来帮你搞定这个JSON解析的问题！SQL Server 2016的JSON功能虽然好用，但嵌套结构的处理确实容易踩坑，尤其是Jobs这种层级的字段，咱们一步步排查：

第一步：先确认你的JSON结构类型 #

Jobs通常有两种常见结构——数组类型（多个工作经历）或者单个嵌套对象（单个工作经历），不同结构的解析写法完全不同。我先给两种典型示例，你可以对照自己的JSON：

示例1：Jobs是数组类型 #

{
  "FirstName": "John",
  "LastName": "Doe",
  "Jobs": [
    {
      "JobTitle": "Software Engineer",
      "Company": "Tech Corp",
      "StartDate": "2020-01-01"
    },
    {
      "JobTitle": "Senior Engineer",
      "Company": "Global Inc",
      "StartDate": "2023-05-01"
    }
  ]
}

示例2：Jobs是单个嵌套对象 #

{
  "FirstName": "Jane",
  "LastName": "Smith",
  "Jobs": {
    "JobTitle": "Data Analyst",
    "Company": "Data Co",
    "StartDate": "2021-03-15"
  }
}

第二步：针对不同结构的正确SQL写法 #

处理Jobs数组类型 #

如果Jobs是数组，你需要用OPENJSON配合CROSS APPLY来展开数组中的每一项，这样才能提取嵌套字段：

DECLARE @json NVARCHAR(MAX) = N'{
  "FirstName": "John",
  "LastName": "Doe",
  "Jobs": [
    {
      "JobTitle": "Software Engineer",
      "Company": "Tech Corp",
      "StartDate": "2020-01-01"
    },
    {
      "JobTitle": "Senior Engineer",
      "Company": "Global Inc",
      "StartDate": "2023-05-01"
    }
  ]
}'

SELECT 
  JSON_VALUE(@json, '$.FirstName') AS FirstName,
  JSON_VALUE(@json, '$.LastName') AS LastName,
  j.JobTitle,
  j.Company,
  j.StartDate
FROM OPENJSON(@json, '$.Jobs')
WITH (
  JobTitle NVARCHAR(100) '$.JobTitle',
  Company NVARCHAR(100) '$.Company',
  StartDate DATE '$.StartDate'
) AS j

处理Jobs单个嵌套对象 #

如果Jobs是单个对象，直接用JSON_VALUE指定完整路径即可提取嵌套字段：

DECLARE @json NVARCHAR(MAX) = N'{
  "FirstName": "Jane",
  "LastName": "Smith",
  "Jobs": {
    "JobTitle": "Data Analyst",
    "Company": "Data Co",
    "StartDate": "2021-03-15"
  }
}'

SELECT 
  JSON_VALUE(@json, '$.FirstName') AS FirstName,
  JSON_VALUE(@json, '$.LastName') AS LastName,
  JSON_VALUE(@json, '$.Jobs.JobTitle') AS JobTitle,
  JSON_VALUE(@json, '$.Jobs.Company') AS Company,
  JSON_VALUE(@json, '$.Jobs.StartDate') AS StartDate

第三步：常见错误排查 #

如果还是不行，你可以检查这几个常见问题：

• 路径写法错误：确保路径以$开头，嵌套字段用.连接，数组元素要加索引（比如取第一个工作经历：$.Jobs[0].JobTitle）
• 混淆JSON_VALUE和JSON_QUERY：JSON_VALUE用于提取单个标量值（字符串、数字、日期），JSON_QUERY用于提取整个JSON对象或数组（不会转义），别用错函数
• JSON格式不合法：先用SELECT ISJSON(@json)检查你的JSON是否有效，返回1才是合法的，比如有没有遗漏逗号、引号不匹配、括号不闭合等问题
• 数据类型不匹配：如果StartDate是日期类型，确保JSON里的字符串能被SQL Server转为DATE类型，否则会返回NULL

如果你的JSON结构和上面的示例不同，或者当前的SQL代码有特殊写法，可以把具体的JSON和SQL贴出来，我再帮你精准调整！

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