A Practical Guide to Deal With JSON Data in SQL

SQL Server / DBA Leave a comment

As promised in the previous post while talking about Azure IP ranges and service tags, I mentioned that I will be writing next post to read JSON files in a structured relational format so let’s take a look at different possibilities to convert JSON data into rows and columns.

Anytime, you want to read or traverse JSON data, you might be interested in looking at it as –

  1. What all different key/value pairs are there or
  2. To fetch the value of a particular key or
  3. To process the nested JSON i.e. where the value itself is a JSON object

As an example, let us refer to the same JSON file that we talked about in the previous article that you can download from the Downloads => Azure IP Ranges menu of this blog.

You can simply declare a variable in T-SQL to hold your JSON data which is nothing but the content of our Azure IP ranges file as shown in the snapshot below –

The file is huge with more than 80K+ rows but still you can do a copy/paste in SQL Server Management Studio and assign it to the variable. Nothing extra needs to be done…simply copy the entire content of JSON file and paste it as the variable content within single quote.

Now, to see the first level of objects, run the following T-SQL statement – 

-- NOTE that @json variable content is truncated here
-- so replace the variable content with the JSON file content that we talked about

DECLARE @json NVARCHAR(MAX)

SET @json='{
  "changeNumber": 160,
  "cloud": "Public",
  "values": [
    {
      "name": "ActionGroup",
      "id": "ActionGroup",
      "properties": {
        "changeNumber": 10,
        "region": "",
'
SELECT * FROM OPENJSON(@json)

OPENJSON is a table-valued function (TVF) that parses the JSON and returns the data in rows and columns as shown below –

You can see how easily your JSON data has been converted into relational format without any complex manipulations. The resultset above shows that our JSON file had 3 keys at top level and the value of 3rd key is further a JSON. Third column indicates the type of data it is holding e.g. 1 is for string data and 2 is there for numbers. To be precise –

Note: OPENJSON function is available only under the compatibility level 130 or higher.

You can check the database compatibility level by running the following command and it works for Azure SQL databases too – 

SELECT compatibility_level, * FROM sys.databases WHERE database_id = DB_ID()

You can use the WHERE clause on a particular key as well – 

SELECT * FROM OPENJSON(@json) WHERE [key] = 'values'

Before we discuss further how to read the nested JSON or the value for a particular key, let me point out that OPENJSON has converted your data into relational format without you specifying the schema of resulting table. But, you can also specify the schema using WITH clause as below – 

SELECT * FROM OPENJSON(@json) 
WITH
(
	  JKey		VARCHAR(100)	'$.changeNumber'
	, cloud		VARCHAR(100)	'$.cloud'
	, Jvalue	NVARCHAR(MAX)	'$.values' AS JSON
)

While defining the schema, we explicitly mentioned that there would be three columns and each column to contain the content as defined by the path there, which are nothing but the keys from the top level i.e. $. Since we knew that third key (i.e. values) is a JSON itself so we defined it as JSON. The output looks like –

Note that when using AS JSON while defining the schema, the data type must be NVARCHAR(MAX) which is obvious as it represents JSON data…there is NO JSON data type in SQL

Also, note that key names e.g. as mentioned $.changeNumber in our example in JSON path are case-sensitive

Now, to read a particular key directly or to read nested JSON, let me introduce two more functions –

  1. JSON_VALUE which returns a scalar value and
  2. JSON_QUERY which returns an object or an array

So, whenever you need to read a scalar value, use JSON_VALUE and to read anything else, use JSON_QUERY function.

We are not going to mention much details of these two functions here but will simply see how to use them while reading a JSON data. Probably, will write another post to highlight more details of these two functions.

For now, let us just see how we used these two functions instead of OPENJSON to parse the data – 

SELECT 
  changeNumber	= JSON_VALUE(@json, '$.changeNumber')
, cloud			= JSON_VALUE(@json, '$.cloud')
, [values]		= JSON_QUERY(@json, '$.values')

But, how exactly to process the nested JSON rather than just reading it as JSON object? This can be done by using CROSS or OUTER APPLY operators to refer back to the parent rows. Let us use this to read more content of our JSON file and from nested JSON too as – 

SELECT JKey, cloud, JValueName, JValueID
, JValuePropertiesPlatform
, AddressPrefix
FROM OPENJSON(@json)
WITH
(
	  JKey		VARCHAR(100)	'$.changeNumber'
	, cloud		VARCHAR(100)	'$.cloud'
	, Jvalue	NVARCHAR(MAX)	'$.values' AS JSON
)
OUTER APPLY OPENJSON(JValue)
WITH
(
	  JValueName		VARCHAR(100) '$.name'
	, JValueID			VARCHAR(100) '$.id'
	, JValueProperties	NVARCHAR(MAX) '$.properties' AS JSON
)
OUTER APPLY OPENJSON(JValueProperties)
WITH
(
	  JValuePropertiesPlatform			VARCHAR(100)	'$.platform'
	, JValuePropertiesAddressPrefixes	NVARCHAR(MAX)	'$.addressPrefixes' AS JSON
)
OUTER APPLY OPENJSON(JValuePropertiesAddressPrefixes)
WITH
(
	AddressPrefix	VARCHAR(500) '$'
)
--WHERE JValueName LIKE 'DataFactory.WestEurope%'

We picked up name and id from values JSON and then platform from properties JSON followed by addresses from the addressprefixes JSON keys i.e.

which gives us the output as –

You can use the WHERE clause too to filter the output as we mentioned in the last statement. Just uncomment the WHERE clause in previous query and run it again…you will get the data corresponding to WHERE condition as –

So, we can summarize it as that you can parse your JSON data using OPENJSON function or JSON_VALUE and JSON_QUERY functions with a flexibility to define your own schema of resulting dataset using WITH clause. To process a nested JSON format, OUTER APPLY operator comes handy to refer back to parent rows.

Many thanks for reading!

Leave a Reply