Notes on SQL

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

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