Advertisements
Home > JSON, SQL Server 2016 > Reading JSON string with Nested array of elements | SQL Server 2016 – Part 3

Reading JSON string with Nested array of elements | SQL Server 2016 – Part 3


In my [previous post] I discussed about how to Import or Read a JSON string and convert it in relational/tabular format in row/column from.

Today in this post I’ll talk about how to read/parse JSON string with nested array of elements, just like XML.
 

Native JSON support in SQL Server 2016 provides you few functions to read and parse your JSON string into relational format and these are:

– OPENJSON() Table valued function: parses JSON text and returns rowset view of JSON.

– JSON_Value() Scalar function: returns a value from JSON on the specified path.

We will see usage of both the functions in our example below:
 

Here, we have just one nested element, and the OPENJSON() function will get you the child elements values.

–> Method #1.a. Using OPENJSON() function:

DECLARE @json NVARCHAR(1000)
SELECT @json = 
N'{
  "OrderHeader": [
    {
      "OrderID": 100,
      "CustomerID": 2000,

      "OrderDetail": 
        {
          "ProductID": 2000, 
		  "UnitPrice": 350 		
		}
    }
  ]
}'

SELECT 
	OrderID, 
	CustomerID, 
	
	[OrderDetail.ProductID] AS ProductID, 
	[OrderDetail.UnitPrice] AS UnitPrice 

FROM OPENJSON (@json, '$.OrderHeader') 
WITH (
	OrderID INT, 
	CustomerID INT, 

	[OrderDetail.ProductID] INT, 
	[OrderDetail.UnitPrice] INT
) AS Orders 
OrderID	CustomerID	ProductID	UnitPrice
100	2000		2000		350

 

But, if you have more than one nested elements the same query will give just 1 row with NULL values under the child columns, like this.

–> Method #1.b. In case of multiple child elements:

DECLARE @json NVARCHAR(1000)
SELECT @json = 
N'{
  "OrderHeader": [
    {
      "OrderID": 100,
      "CustomerID": 2000,

      "OrderDetail": [
        {
          "ProductID": 2000, 
		  "UnitPrice": 350 		
		}, 		
		{ 		      
		  "ProductID": 3000,
          "UnitPrice": 450
        }
      ]
    }
  ]
}'

SELECT 
	OrderID, 
	CustomerID, 
	
	[OrderDetail.ProductID] AS ProductID, 
	[OrderDetail.UnitPrice] AS UnitPrice 

FROM OPENJSON (@json, '$.OrderHeader') 
WITH (
	OrderID INT, 
	CustomerID INT, 

	[OrderDetail.ProductID] INT, 
	[OrderDetail.UnitPrice] INT
) AS Orders 
OrderID	CustomerID	ProductID	UnitPrice
100	2000		NULL		NULL

You might be expecting 2 rows with same OrderID & CustomerID, with different ProductID & UnitPrice, right?

Instead you get ProductID & UnitPrice column values as NULL. Because, here you are having array of child elements with OrderDetail node (notice the square-bracket after “OrderDetail”: node), thus the Query is not able to find the key on the path.
 

In this case what you have to do is, use the array positions with square brackets (“[” and “]”) in your query and call out separate columns for each child element, like below:

DECLARE @json NVARCHAR(1000)
SELECT @json = 
N'{
  "OrderHeader": [
    {
      "OrderID": 100,
      "CustomerID": 2000,

      "OrderDetail": [
        {
          "ProductID": 2000, 
		  "UnitPrice": 350 		
		}, 		
		{ 		      
		  "ProductID": 3000,
          "UnitPrice": 450
        }
      ]
    }
  ]
}'

SELECT 
	OrderID, 
	CustomerID, 

	[OrderDetail[0]].ProductID] AS ProductID1, 
	[OrderDetail[0]].UnitPrice] AS UnitPrice1, 

	[OrderDetail[1]].ProductID] AS ProductID2, 
	[OrderDetail[1]].UnitPrice] AS UnitPrice2 

FROM OPENJSON (@json, '$.OrderHeader') 
WITH (
	OrderID INT, 
	CustomerID INT, 

	[OrderDetail[0]].ProductID] INT, 
	[OrderDetail[0]].UnitPrice] INT, 

	[OrderDetail[1]].ProductID] INT, 
	[OrderDetail[1]].UnitPrice] INT 
) AS Orders
OrderID	CustomerID	ProductID1	UnitPrice1	ProductID2	UnitPrice2
100	2000		2000		350		3000		450

 

You can also specify the child elements with full path by using the dollar sign “$” inside the WITH() clause (instead at column level above), like below:

–> Method #2. Using OPENJSON() function:

DECLARE @json NVARCHAR(1000)
SELECT @json = 
N'{
  "OrderHeader": [
    {
      "OrderID": 100,
      "CustomerID": 2000,

      "OrderDetail": [
        {
          "ProductID": 2000, 
		  "UnitPrice": 350 		
		}, 		
		{ 		      
		  "ProductID": 3000,
          "UnitPrice": 450
        }
      ]
    }
  ]
}'

SELECT 
	OrderID, 
	CustomerID, 

	ProductID1, 
	UnitPrice1, 

	ProductID2, 
	UnitPrice2

FROM OPENJSON (@json, '$.OrderHeader')
WITH (
	OrderID    INT '$.OrderID',
	CustomerID INT '$.CustomerID',

	ProductID1 INT '$.OrderDetail[0].ProductID',
	UnitPrice1 INT '$.OrderDetail[0].UnitPrice', 

	ProductID2 INT '$.OrderDetail[1].ProductID',
	UnitPrice2 INT '$.OrderDetail[1].UnitPrice' 
) AS Orders
OrderID	CustomerID	ProductID1	UnitPrice1	ProductID2	UnitPrice2
100	2000		2000		350		3000		450

Ok, so by using the key path and the array position we can get the child elements value in our Query result-set by using above 2 methods.
 

But instead of having them in separate columns how about pulling them in separate rows, this will also make your query dynamic as you would not know the number of child-elements before hand, right?

This can be done by CROSS APPLYing the JSON child node with the parent node and using the JSON_Value() function, like shown below:

–> Method #3. Using JSON_Value() with OPENJSON() function:

DECLARE @json NVARCHAR(1000)
SELECT @json = 
N'{
  "OrderHeader": [
    {
      "OrderID": 100,
      "CustomerID": 2000,

      "OrderDetail": [
        {
          "ProductID": 2000, 
		  "UnitPrice": 350 		
		}, 		
		{ 		      
		  "ProductID": 3000,
          "UnitPrice": 450
        },
		{ 		      
		  "ProductID": 4000,
          "UnitPrice": 550
        }
      ]
    }
  ]
}'

SELECT 
	JSON_Value (c.value, '$.OrderID') as OrderID, 
	JSON_Value (c.value, '$.CustomerID') as CustomerID, 
	JSON_Value (p.value, '$.ProductID') as ProductID, 
	JSON_Value (p.value, '$.UnitPrice') as UnitPrice

FROM OPENJSON (@json, '$.OrderHeader') as c
CROSS APPLY OPENJSON (c.value, '$.OrderDetail') as p
OrderID	CustomerID	ProductID	UnitPrice
100	2000		2000		350
100	2000		3000		450
100	2000		4000		550

 

Ok, that’s it for today.

In my [next post] I’ll talk about storing JSON string in a table and doing some hands-on with it.


Advertisements
  1. Harish
    July 22, 2017 at 12:49 am

    Hi Manoj,

    I am trying to extract values from an array in a JSON. I have loaded these JSON as records in a table. Is there a way to make OPENJSON work on every json record in the table in one single query? Or, should I write a cursor to extract values record by record (Or Json by Json)

  2. Pankaj
    June 28, 2017 at 11:40 am

    Hi Sravu,
    Try this code:
    SELECT
    JSON_Value (@var, ‘$.StudentId’) as studentid,
    JSON_Value (c.value, ‘$.city’) as city,
    JSON_Value (c.value, ‘$.state’) as state,
    JSON_Value (c.value, ‘$.zip’) as zip
    FROM OPENJSON (@var,’$.locations’) as c

    There is no need to chunk it based on studentid as that is by default a separator here so you can extract that directly from the json string.
    Hope it helps!
    Thanks

  3. sravu
    June 28, 2017 at 3:38 am

    HI Manoj, i am using your blog to retrieve values from a json and its not working for reason.. could you please help me .. thank you. really do appreciate your help:

    declare @var nvarchar(max)

    set @var='{
    “StudentId”: “88”,
    “locations”: [
    {
    “city”: “New York”,
    “state”: “NY”,
    “zip”: “10001”
    },
    {
    “city”: “Edison”,
    “state”: “NJ”,
    “zip”: “08820”
    }
    ]
    }’

     SELECT
    JSON_Value (c.value, '$.StudentId') as studentid, 
    JSON_Value (p.value, '$.city') as city, 
    JSON_Value (p.value, '$.state') as state, 
    JSON_Value (p.value, '$.zip') as zip
    

    FROM OPENJSON (@var, ‘$’) as c
    CROSS APPLY OPENJSON (c.value, ‘$.locations’) as p

  4. Pankaj
    April 28, 2017 at 7:20 pm

    Dear Manoj,
    I am using Method#3, json_value, my data have 5 key and value rows and out of these 1 block is having multiple values, so I am not able to combine the other as those seems independent, e.g. in your example say we have OrderHeader which has 3 values then we have another element at same level as OrderHeader say, its Sale2017 and it only have 1 block , something like:
    DECLARE @json NVARCHAR(1000)
    SELECT @json =
    N'{
    “OrderHeader”: [
    {
    “OrderID”: 100,
    “CustomerID”: 2000,

      "OrderDetail": [
        {
          "ProductID": 2000, 
          "UnitPrice": 350      
        },      
        {             
          "ProductID": 3000,
          "UnitPrice": 450
        },
        {             
          "ProductID": 4000,
          "UnitPrice": 550
        }
      ]
    }
    

    ],
    “Sale2017”:{
    “TotalUnit”:100000,
    “GrossAmount”:200000
    }
    }’

    Then how we can handle this, thanks in advance for your kind help.

    • Pankaj
      May 1, 2017 at 5:33 pm

      I figured out the solution, thanks.

  5. Le Thien Ha
    September 19, 2016 at 10:56 am

    Dear Manoj,

    I tried your sample, however , there is a problem with 1.b case.
    When I execute 1.b case the result is null for the array ( OrderID + CustomerID still show value )

    Could you please check it again ?

    • September 19, 2016 at 11:02 am

      Hi @Le, that’s expected, OrderID & CustomerID will have values, but the rest two columns ProductID & UnitPrice will be NULL.

      ~Manoj

  1. January 5, 2017 at 12:04 pm
  2. November 2, 2015 at 7:01 am
  3. November 1, 2015 at 3:53 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: