Read JSON data in MS SQL

Starting from MS SQL server 2016 you can convert JSON data to tabular format. It is possible using the newly introduced OPENJSON function.

Some basic intro about JSON format you can find in my other blog post “How to Format MS SQL data in JSON format” that you can find here.

OPENJSON function is a rowset function (table-valued function) and returns an object that can be used just like a normal table or a view.

The function returns a table with

Default schema – when the schema is not specified
Explicit schema - User-defined schema meaning the returned columns are defined by the user

Let’s go more in deep with both approaches.

OPENJSON with default schema

Syntax:

OPENJSON (Expression, [Path])

Expression – JSON object in Unicode text format
Path – optional argument to specify a fragment (sub-node) of the input expression

When the schema is not specified the OPENJSON function returns a table result with three columns:

Key – Non nullable NVARCHAR (4000) column that contains the name of a JSON property or the index of the element in the specified array.
Value – Nullable NVARCHAR (MAX) column that contains the value of the property.
Type – TINYINT column representing the data type of the value:
- 0 -> null
- 1 -> string
- 2 -> int
- 3 -> true/false
- 4 -> array
- 5 -> object

Let’s start with a basic example.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name: "Damir Matešić",
"BlogURL": "http:\\blog.matesic.info",
"Born": 1979,
"Spouse":null,
"BornAfterWoodstock": true,
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';
SELECT * FROM OPENJSON(@JSON_data)

I was testing something before using SQL old compatibility level (SQL 2014) and I totally forgot about it. So, the result of this query is a simple error message:

“Msg 208, Level 16, State 1, Line 25
Invalid object name 'OPENJSON'.”

Let’s change the compatibility level of the database to a new one (SQL 2016+) and re run the query again.

Voila, another error message:

“Msg 13609, Level 16, State 4, Line 49
JSON text is not properly formatted. Unexpected character 'J' is found at position 11.”

If you take a closer look at the JSON input, you will notice that I forgot to close the quotation mark after the "Name" key/element. The SQL engine don’t forgive us mistakes (in most cases).

So, let’s finally correct our input JSON data and try to see OPENJSON and MS SQL in action.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "Damir Matešić",
"BlogURL": "http:\/\/blog.matesic.info",
"Born": 1979,
"Spouse":null,
"BornAfterWoodstock": true,
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';
SELECT * FROM OPENJSON(@JSON_data)

How it was expected we got a single row per every JSON property with mentioned Key, Value and Type columns. Using OPENJSON function only the first level properties are returned. As you can see the type column is a numeric (TINYINT) value (0 - null, 1 - string, etc.). I will create a help function that will return us a more readable result.

Example:

CREATE OR ALTER FUNCTION dbo.GetJSONDataType
(
	@JSONDataType TINYINT
)
RETURNS VARCHAR(32)
AS
BEGIN
	RETURN CASE 
		WHEN @JSONDataType = 0 THEN 'null'
		WHEN @JSONDataType = 1 THEN 'string'
		WHEN @JSONDataType = 2 THEN 'int'
		WHEN @JSONDataType = 3 THEN 'true/false'
		WHEN @JSONDataType = 4 THEN 'array'
		WHEN @JSONDataType = 5 THEN 'object'
	END
END

Now, let's repeat the example using the new function.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "Damir Matešić",
"BlogURL": "http:\/\/blog.matesic.info",
"Born": 1979,
"Spouse":null,
"BornAfterWoodstock": true,
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';
SELECT *, dbo.GetJSONDataType(type) AS data_type FROM OPENJSON(@JSON_data);

Now we can read the result's more easily.

Let’s try to use the optional Path argument and try to retrieve the "Parents" node.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "Damir Matešić",
"BlogURL": "http:\/\/blog.matesic.info",
"Born": 1979,
"Spouse":null,
"BornAfterWoodstock": true,
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';
SELECT *, dbo.GetJSONDataType(type) AS data_type FROM OPENJSON(@JSON_data, '$.Parents');

We were able to retrieve the "Parents" node and the result is a table with two rows (type 1 - string). Now let’s see what happens if we try to retrieve a non-existing node (e.g. “Friends”).

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "Damir Matešić",
"BlogURL": "http:\/\/blog.matesic.info",
"Born": 1979,
"Spouse":null,
"BornAfterWoodstock": true,
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';
SELECT *, dbo.GetJSONDataType(type) AS data_type FROM OPENJSON(@JSON_data, '$.Friends');

OPENJSON will return an empty result set if we try to retrieve a non-existing node. However, we can use the strict option mode and then the error will be raised.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "Damir Matešić",
"BlogURL": "http:\/\/blog.matesic.info",
"Born": 1979,
"Spouse":null,
"BornAfterWoodstock": true,
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';
SELECT *, dbo.GetJSONDataType(type) AS data_type FROM OPENJSON(@JSON_data, 'strict $.Friends');

Since we used the strict mode and the node was not existing the SQL engine raised the following exception:

“Msg 13608, Level 16, State 3, Line 148
Property cannot be found on the specified JSON path.”

Now, let’s select the “FavoriteDrinks” node from the JSON document.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "Damir Matešić",
"BlogURL": "http:\/\/blog.matesic.info",
"Born": 1979,
"Spouse":null,
"BornAfterWoodstock": true,
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';
SELECT *, dbo.GetJSONDataType(type) AS data_type FROM OPENJSON(@JSON_data, '$.FavoriteDrinks');

The result set is 4 rows (because of 4 favorite drinks) but you will notice that the SQL engine marked them as objects (type 5 – object).

I changed the JSON input to contains spaces in the key part of the JSON document.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "Damir Matešić",
"Blog URL": "http:\/\/blog.matesic.info",
"Born": 1979,
"Spouse":null,
"Born after woodstock": true,
"Favorite drinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';

First, I will try to select rows without the Path argument:

SELECT *, dbo.GetJSONDataType(type) AS data_type FROM OPENJSON(@JSON_data);

The result is returned normally. We can see that the keys are here no matter they contain spaces. Let’s try to use the Path argument to retrieve "Favorite drinks" node:

SELECT *, dbo.GetJSONDataType(type) AS data_type FROM OPENJSON(@JSON_data, '$.Favorite drinks');

In this case an exception is raised:

“Msg 13607, Level 16, State 4, Line 196
JSON path is not properly formatted. Unexpected character ' ' is found at position 10.”

The select statement is wrong. Here is the right one:

SELECT *, dbo.GetJSONDataType(type) AS data_type FROM OPENJSON(@JSON_data, '$."Favorite drinks"');

OPENJSON with explicit schema

Something similar like reading XML data in MS SQL server, we can specify our own schema when reading JSON data. In this case the function will also return a table, but the output columns and their data types can be specified by the user.

The schema for the JSON data can be specified using the optional WITH keyword at the end of the OPENJSON function.

Syntax:

OPENJSON (Expression, [Path])

[

WITH (

columnName dataType [columnPath] [AS JSON]

[, columnName dataType [columnPath] [AS JSON] ]

)

]

columnName – Name of the output column
dataType – Data type of the output column
columnPath – Optional argument to specify a fragment (sub-node) of the column
AS JSON – Optional argument to specify that the referenced property contains an inner JSON object or array. If used, the column must be NVARCHAR(MAX) data type.

When using the WITH keyword at least one column must be specified.

So, let’s define the schema for our JSON example data.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "Damir Matešić",
"BlogURL": "http:\/\/blog.matesic.info",
"Born": 1979,
"Spouse":null,
"BornAfterWoodstock": true,
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';
SELECT * FROM OPENJSON(@JSON_data) WITH (
    Name NVARCHAR(256) '$.Name',
    [Blog URL] NVARCHAR(256) '$.BlogURL',
    Born INT '$.Born',
    Spouse NVARCHAR(256) '$.Spouse',
    [Favorite drinks] NVARCHAR(MAX) '$.FavoriteDrinks' AS JSON,
    Parents NVARCHAR(MAX) '$.Parents' AS JSON
) Data

The result is expected, and we get all the data nicely like we specified in the schema.

Let’s play a little bit and see some variations of the schema part to see what happens.

Example:

SELECT * FROM OPENJSON(@JSON_data) WITH (
    Name NVARCHAR(256) '$.Name',
    [Blog URL] NVARCHAR(256) '$.BlogURL',
    Born INT '$.Born',
    Spouse NVARCHAR(256) '$.Spouse',
    [Favorite drinks] NVARCHAR(MAX) '$.FavoriteDrinks',
    Parents NVARCHAR(MAX) '$.Parents' 
) Data

In this example we didn't specify the "Favorite drinks" and the "Parents" columns using AS JSON and the engine returned us NULL values. Maybe we were expecting to get a string value (like inner XML or something) but since the engine realized that both elements are objects it returned to us NULL values.

Example:

SELECT * FROM OPENJSON(@JSON_data) WITH (
    Name NVARCHAR(256) '$.Name',
    [Blog URL] NVARCHAR(256) '$.BlogURL',
    Born INT '$.Born',
    Spouse NVARCHAR(256) '$.Spouse',
    [Favorite drinks] VARCHAR(MAX) '$.FavoriteDrinks' AS JSON,
    Parents VARCHAR(MAX) '$.Parents' AS JSON
) Data

As told before, when using AS JSON keyword the column data type must be of NVARCHAR(MAX) data type this schema is not valid and an exception has been raised:

“Msg 13618, Level 16, State 1, Line 275
AS JSON option can be specified only for column of nvarchar(max) type in WITH clause.”

At the end let’s try to use the CROSS APPLY operator and get favorite drinks details.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "Damir Matešić",
"BlogURL": "http:\/\/blog.matesic.info",
"Born": 1979,
"Spouse":null,
"BornAfterWoodstock": true,
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}],
"Parents": {"Mom": "Iva","Dad": "Boris"}
}';
SELECT * FROM OPENJSON(@JSON_data) WITH (
    Name NVARCHAR(256) '$.Name',
    [Blog URL] NVARCHAR(256) '$.BlogURL',
    Born INT '$.Born',
    Spouse NVARCHAR(256) '$.Spouse',
    [Favorite drinks] NVARCHAR(MAX) '$.FavoriteDrinks' AS JSON,
    Parents NVARCHAR(MAX) '$.Parents' AS JSON
) Data
CROSS APPLY OPENJSON([Favorite drinks])
WITH
(
    Name NVARCHAR(256) '$.Name',
    Drink NVARCHAR(256) '$.Drink'
)DrinkData;

*MS SQL Server sample database "WideWorldImporters" can be downloaded at: https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

*You can download the complete SQL Script with all examples from the post here: MS SQL Script.sql (9.78 kb)

IS [NOT] DISTINCT FROM (The Distinct Predicate)

MS SQL GENERATE_SERIES

MS SQL TRIM, LTRIM, RTRIM

MS SQL STRING_SPLIT

LEAST()

GREATEST()

HierarchyID data type Performance, tips & tricks

HierarchyID data type Methods

Hierarchical data in MS SQL

Last Actual Plan with sys.dm_exec_query_plan_stats

Read JSON data in MS SQL

OPENJSON with default schema

OPENJSON with explicit schema

Related posts