Using the OPENJSON Function In SQL Server

In this post we look at using the OPENJSON function in SQL Server to parse some example JSON documents. As a quick note, I would not use SQL Server to parse JSON, as object oriented languages offer much better tools for this than a SQL back-end and allow for building a custom object that extracts the JSON document and can transform it easily. However, for the sake of demonstration, we’ll use SQL Server for a few examples to see how we can use the OPENJSON function to parse JSON documents.

Easy JSON Example

The below T-SQL extracts a simple JSON document which only has minor complexity with arrays for some fields. We return three data sets: one data set without any fields containing arrays. The next two sets we return include both the name (which we would use for a join) and breaking down the data halls or office further. The purpose of this would be to get that one-to-many relationship that we could join, with each of these latter two containing the “many” (ie: many offices per data center or many data halls per data center).

DECLARE @datacenter NVARCHAR(MAX)

SET @datacenter = 
N'{
 "Name": "Farfield V",
 "Location": "Farfield",
 "Floors": 3,
 "DataHalls": ["1A","1B","2A","3B"],
 "Offices": ["2B","3A"]
}'

---- Gets our data centers
SELECT *
FROM OPENJSON(@datacenter)
WITH (
 DCName VARCHAR(25) '$.Name'
 , DCLocation VARCHAR(25) '$.Location'
 , Floors TINYINT '$.Floors'
)

---- Gets our offices per data center
SELECT DCName, Office
FROM OPENJSON(@datacenter)
WITH (
 DCName VARCHAR(25) '$.Name'
 , Offices NVARCHAR(MAX) '$.Offices' AS JSON
) AS dc
CROSS APPLY OPENJSON(dc.Offices) WITH (
 Office VARCHAR(10) '$'
) AS office

---- Gets our data halls per data center
SELECT DCName, DataHall
FROM OPENJSON(@datacenter)
WITH (
 DCName VARCHAR(25) '$.Name'
 , DataHalls NVARCHAR(MAX) '$.DataHalls' AS JSON
) AS dc
CROSS APPLY OPENJSON(dc.DataHalls) WITH (
 DataHall VARCHAR(10) '$'
) AS datahall

Result for our easy JSON example

As a note, we use CROSS APPLY on the data hall and office fields because we need to extract each array element from the field. If you were to query the field directly (Offices VARCHAR(MAX) '$.Offices'), you would end up with a NULL because these are arrays not single values. In addition, we don’t want to return the full array, but each individual value — we we must multiply each value isolated by the name of the data center in this case and we end up with our one-to-many relationship. In the case of pyspark, you would use the method explode.

More Complex JSON Example

In the below example, we see a common pattern that we would run across with JSON — a structure that does not always consistently follow from one value to the next (if there were hundreds of documents, this would become even clearer). In the below example, we have some consistent fields in our document such as WorkoutDate, Notes, Period, etc. However the Injury field is not always present. This highlights an example of where a document oriented database, like MongoDB, carries strengths — in many cases, a field wouldn’t apply. Rather than NULL it and end up with a massive column set that we see in some tables, we can simply leave it out. We’ll notice that when we use OPENJSON with SQL Server, it will create this column with a NULL when the field in the document is missing.

DECLARE @workoutdoc NVARCHAR(MAX)
SET @workoutdoc =   
N'[  
 {  
  "Workout": {  
   "WorkoutDate": "2017-01-01",  
   "Notes": " Jump rope circuit"  
  },  
  "Period": "Morning",  
  "Details": {  
   "Type": "Regular",  
   "Time": 300  
  }
 },  
 {  
  "Workout": {  
   "WorkoutDate": "2017-01-02",  
   "Notes": " Sprints"  
  },  
  "Period": "Morning",  
  "Details": {  
   "Type": "Burst",  
   "Time": 15  
  },
  "Injury": "Light hamstring tear"
 }  
]'  
   
SELECT * FROM  
OPENJSON (@workoutdoc)  
WITH (   
 WorkoutDate DATE '$.Workout.WorkoutDate'
 , WorkoutPeriod VARCHAR(25) '$.Period'
 , Notes VARCHAR(100) '$.Workout.Notes'
 , WorkoutType VARCHAR(50) '$.Details.Type'
 , TimeInSecs INT '$.Details.Time'
 , WorkoutPeriod VARCHAR(25) '$.Injury'
) 

Result for our more complex JSON example

As we see, this more complex JSON structure is flattened to fit our table. If you are going to use T-SQL to query JSON, you will often need to dig further into the JSON such as this in order to extract data from the fields.

As a quick note here, I would not store fitness data with a document structured like this. This example is only used for demonstration purposes.

Note: all images in the post are either created through actual code runs or from Pixabay. The written content of this post is copyright; all rights reserved. None of the written content in this post may be used in any artificial intelligence.