Shredding JSON Nested Arrays

5 Feb

Whilst reading up on SQL Server 2016 JSON functionality I have seen many examples of extracting data from a JSON array. However, I wanted to work out how to extract data from an array within an array – for example, an array of customer data where each customer has an array of order details.

Background
Reading examples from Itzik Ben-Gan’s (b|t) book for 70-761 there are several examples of basic JSON manipulation, some involving a basic array of data. I haven’t had much experience of JSON data within SQL Server, but during my brief period with MongoDB I regularly encountered arrays with arrays – for example an array of customers which contained an array of contact details.
I couldn’t find an example of this when searching and eventually posted my problem on SQL Server Central, where a solution was posted very quickly. I’ve posted the code here primarily for my own notes.

Test Data and execution

The JSON data used here is edited from Itzik’s book. This contains two customers – one has an array with one order and the other has an array with two orders:

Listing 1: JSON Data

DECLARE @json AS NVARCHAR(MAX) = N'
[
{ 
 "Customer":{ 
  "Id":1, 
  "Name":"Customer NRZBB",
  "Order":[{ 
 "Id":10692, 
 "Date":"2015-10-03",
 "Delivery":null
  }]
 }
},
{ 
 "Customer":{ 
  "Id":2, 
  "Name":"Customer NRZCC",
  "Order":[{ 
 "Id":10703, 
 "Date":"2015-10-13",
 "Delivery":null
  },
  { 
 "Id":10704, 
 "Date":"2015-10-14",
 "Delivery":null
  }]
 }
}
]';

In the query below, the JSON_VALUE functions extract at the ‘higher’ array – the Customer (‘Customer.Id’ and ‘Customer.Name’). The CROSS APPLY then uses JSON_QUERY within, to extract at the ‘Order’ level of the data, which is the array within ‘Customer’, so is described as ‘Customer.Order’:

Listing 2: Shredding an array that has arrays within

SELECT
  JSON_VALUE(OJC.[value],'$.Customer.Id')   AS CUSTOMER_ID
 ,JSON_VALUE(OJC.[value],'$.Customer.Name') AS CUSTOMER_NAME
 ,JSON_VALUE(OCV.[value],'$.Id')      AS ORDER_ID
 ,JSON_VALUE(OCV.[value],'$.Date')    AS ORDER_DATE
 ,JSON_VALUE(OCV.[value],'$.Delivery')    AS ORDER_DELIVERY
FROM OPENJSON(@json) OJC
CROSS APPLY OPENJSON(JSON_QUERY(OJC.[value]),'$.Customer.Order') OCV;

Figure 1: Result of orders within customer query

Customer and order details

References:
JSON Formatter (because SSMS doesn’t)
The Ultimate SQL Server JSON Cheat Sheet – Bert Wagner
OPENJSON
JSON_VALUE
JSON_QUERY

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: