Notes on SQL

Random articles from a puzzled DBA

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.

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

  "Name":"Customer NRZBB",
  "Name":"Customer NRZCC",

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

  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

Figure 1: Result of orders within customer query

Customer and order details

JSON Formatter (because SSMS doesn’t)
The Ultimate SQL Server JSON Cheat Sheet – Bert Wagner

Leave a Reply

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

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

Connecting to %s

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

%d bloggers like this: