SQL Server: Transform arrays of JSON objects into table format

June 19, 2023

Following is the given JSON data:

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
        }
      ]
    }
  }
}';

Need to display:

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

My try:

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

But getting wrong output.

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

>Solution :

You need an additional OPENJSON() call and an APPLY operator to parse the nested JSON content. In this case you need to use the AS JSON modifier in the WITH clause to specify that the referenced property ($.EmployeeDetails.StoreDetail.Store) contains an inner JSON array.

SELECT j1.BusinessEntityID, j2.AnnualSales, j2.BusinessType
FROM OPENJSON(@jdata) WITH ( 
   BusinessEntityID VARCHAR(20) '$.EmployeeDetails.BusinessEntityID',
   Store NVARCHAR(max) '$.EmployeeDetails.StoreDetail.Store' AS JSON
) j1
OUTER APPLY OPENJSON(j1.Store) WITH (
  AnnualSales integer '$.AnnualSales',
  BusinessType VARCHAR(100) '$.BusinessType'
) j2

Of course, a combination of JSON_VALUE() and one OPENJSON() call is also an option:

SELECT 
  JSON_VALUE(@jdata, '$.EmployeeDetails.BusinessEntityID') AS BusinessEntityID, 
  AnnualSales,
  BusinessType
FROM OPENJSON(@jdata, '$.EmployeeDetails.StoreDetail.Store') WITH (
  AnnualSales integer '$.AnnualSales',
  BusinessType VARCHAR(100) '$.BusinessType'
) j