Question 189618 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

SQL Server: Transform arrays of JSON objects into table format

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
Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    To transform the given array of JSON objects into a table format in SQL Server, you can use the OPENJSON function. Here’s an example of how you can extract the desired columns BusinessEntityID, AnnualSales, and BusinessType:

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

SELECT
    JSON_VALUE(store.value, '$.AnnualSales') AS AnnualSales,
    JSON_VALUE(store.value, '$.BusinessType') AS BusinessType,
    JSON_VALUE(@Jdata, '$.EmployeeDetails.BusinessEntityID') AS BusinessEntityID
FROM
    OPENJSON(@Jdata, '$.EmployeeDetails.StoreDetail.Store') store;

    This will give you the result in a table format with columns BusinessEntityID, AnnualSales, and BusinessType:

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

    Make sure to replace @Jdata with the actual variable or JSON string you want to parse.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search