SQL Server：将JSON对象数组转换为表格格式出错求助

问题场景 #

现有如下JSON数据：

DECLARE @Jdata NVARCHAR(MAX) = 
'{
  "EmployeeDetails": {
    "BusinessEntityID": 3,
    "NationalIDNumber": 509647174,
    "JobTitle": "Engineering Manager",
    "BirthDate": "1974-11-12",
    "MaritalStatus": "M",
    "Gender": "M",
    "StoreDetail": {
      "Store": [
        {
          "AnnualSales": 800000,
          "AnnualRevenue": 80000,
          "BankName": "Guardian Bank",
          "BusinessType": "BM",
          "YearOpened": 1987,
          "Specialty": "Touring",
          "SquareFeet": 21000
        },
        {
          "AnnualSales": 300000,
          "AnnualRevenue": 30000,
          "BankName": "International Bank",
          "BusinessType": "BM",
          "YearOpened": 1982,
          "Specialty": "Road",
          "SquareFeet": 9000
        }
      ]
    }
  }
}';

需要转换为如下格式的表格：

BusinessEntityID |  AnnualSales  |  BusinessType 
-------------------------------------------------
3                   300000          BM
3                   800000          BM

尝试执行以下SQL代码后，AnnualSales和BusinessType字段返回NULL：

select *
from OPENJSON(@jdata)
WITH( 
BusinessEntityID VARCHAR(20) '$.EmployeeDetails.BusinessEntityID',
AnnualSales integer '$.EmployeeDetails.StoreDetail.Store.AnnualSales',
BusinessType VARCHAR(100) '$.EmployeeDetails.StoreDetail.Store.BusinessType'
) as a

错误输出：

BusinessEntityID |  AnnualSales  |  BusinessType 
-------------------------------------------------
3                   NULL            NULL

错误原因 #

Store是JSON数组，直接用$.EmployeeDetails.StoreDetail.Store.AnnualSales无法遍历数组内的元素，OPENJSON默认处理顶层对象时，无法直接解析数组内的字段，导致返回NULL。

正确解决方案 #

方法1：嵌套OPENJSON + CROSS APPLY #

先解析外层EmployeeDetails获取BusinessEntityID，并保留Store数组的JSON结构，再通过CROSS APPLY展开数组元素：

SELECT 
  ed.BusinessEntityID,
  s.AnnualSales,
  s.BusinessType
FROM OPENJSON(@Jdata, '$.EmployeeDetails')
WITH (
  BusinessEntityID INT '$.BusinessEntityID',
  Store NVARCHAR(MAX) '$.StoreDetail.Store' AS JSON -- 保留数组的JSON格式
) AS ed
CROSS APPLY OPENJSON(ed.Store)
WITH (
  AnnualSales INT '$.AnnualSales',
  BusinessType VARCHAR(10) '$.BusinessType'
) AS s

方法2：直接定位数组 + JSON_VALUE获取外层字段 #

直接定位到Store数组，用JSON_VALUE从外层JSON中获取BusinessEntityID，确保每个数组元素都关联同一个ID：

SELECT 
  JSON_VALUE(@Jdata, '$.EmployeeDetails.BusinessEntityID') AS BusinessEntityID,
  AnnualSales,
  BusinessType
FROM OPENJSON(@Jdata, '$.EmployeeDetails.StoreDetail.Store')
WITH (
  AnnualSales INT '$.AnnualSales',
  BusinessType VARCHAR(10) '$.BusinessType'
)

正确输出结果 #

BusinessEntityID |  AnnualSales  |  BusinessType 
-------------------------------------------------
3                   800000          BM
3                   300000          BM

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