SQL Server: Transform arrays of JSON objects into table format

Question

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

Zhorov · Accepted Answer · 2023-06-19T11:54:32.423

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

score 2 · Answer 2 · answered Jun 19 '23 at 11:39

You need two levels of OPENJSON. The first one needs to retrieve the inner array using AS JSON. Then you feed that into the next using CROSS APPLY.

You can also put the '$.EmployeeDetails' path into the OPENJSON call itself to avoid having to repeat it

SELECT
  emp.BusinessEntityID,
  store.*
FROM OPENJSON(@Jdata, '$.EmployeeDetails')
  WITH ( 
    BusinessEntityID varchar(20),
    Store nvarchar(max) '$.StoreDetail.Store' AS JSON
  ) AS emp
CROSS APPLY OPENJSON(emp.Store)
  WITH (
    AnnualSales integer,
    BusinessType varchar(100)
  ) AS store;

db<>fiddle

SQL Server: Transform arrays of JSON objects into table format

2 Answers2