Tag Archives: JSON in Sql Server

FOR JSON Clause in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , , ,

Sql Server 2016 FOR JSON clause can be used to convert the query result to JSON format. It gives flexibility to the developers to convert the result to JSON format in the back-end Sql Server itself, instead of doing it in the client applications.

The FOR JSON clause is very much similar to the FOR XML clause. Basically, it converts each row in the result as a JSON object, column names and values are converted as JSON objects name and value pair.

There are two variants of the FOR JSON clause as listed below:

FOR JSON AUTO

The FOR JSON AUTO clause is very much similar to the FOR XML AUTO clause. It automatically formats the JSON output based on the Column/Tables order in the Select statement.

FOR JSON PATH

The FOR JSON PATH clause is very much similar to the FOR XML PATH clause. It gives more control over the resulting JSON structure.

[ALSO READ]:

Let us understand these two variants of the FOR JSON clause with extensive list of examples:

Sql Server FOR JSON CLAUSE

To understand FOR JSON feature, let us create a demo database with Customer table with sample data as shown in the below image by the following script.

Customer Table
Script:

CREATE DATABASE Sql2016SqlHints
GO
USE Sql2016SqlHints
GO
CREATE TABLE dbo.Customer
(Id INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(100), 
 State NVARCHAR(50), Country NVARCHAR(50))
GO
INSERT INTO dbo.Customer (Name, State, Country)
VALUES ('Basavaraj', 'KA', 'India'),
       ('Kalpana', 'NY', NULL)
GO

Example 1: Basic FOR JSON PATH example

SELECT 'Basavaraj' FirstName, 'Biradar' LastName
FOR JSON PATH

RESULT:
{“FirstName”:”Basavaraj”,”LastName”:”Biradar”}

Example 2: Basic FOR JSON AUTO requires at-least one table for generating the JSON output

SELECT 'Basavaraj' FirstName, 'Biradar' LastName
FOR JSON AUTO

RESULT:

Msg 13600, Level 16, State 1, Line 10
FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name.

From the above result it is clear that the FOR JSON AUTO clause works only if at-least one table is mentioned in the from clause.

Example 3: FOR JSON PATH/AUTO example where column names are not specified in the select list, instead * is mentioned

PRINT '******* FOR JSON PATH output *******'
SELECT * FROM dbo.Customer FOR JSON PATH
GO
PRINT '******* FOR JSON AUTO output *******'
SELECT * FROM dbo.Customer FOR JSON AUTO
GO

RESULT:
FOR JSON Sql Select All Columns
Example 4: FOR JSON PATH/AUTO example where required columns in the JSON output are specified in the SELECT clause

PRINT '******* FOR JSON PATH output *******'
SELECT Id, Name, State, Country FROM dbo.Customer FOR JSON PATH
GO
PRINT '******* FOR JSON AUTO output *******'
SELECT Id, Name, State, Country FROM dbo.Customer FOR JSON AUTO

RESULT:
FOR JSON Sql Select Specified Columns

Example 5: To include NULL values in the JSON output, we need to specify the property INCLUDE_NULL_VALUES in the FOR JSON clause. If this option is not specified, in case of NULL value the name-value pair will be removed from the JSON output. Like country in the previous example for the customer Kalpana.

PRINT '******* FOR JSON PATH output *******'
SELECT Id, Name, State, Country FROM dbo.Customer
FOR JSON PATH, INCLUDE_NULL_VALUES
GO
PRINT '******* FOR JSON AUTO output *******'
SELECT Id, Name, State, Country FROM dbo.Customer
FOR JSON AUTO,INCLUDE_NULL_VALUES

RESULT:
SQL FOR JSON INCLUDE NULL VALUES in Output

Example 6: We can use the ROOT option in the FOR JSON clause to generate a wrapper object around the generated JSON output. In the below example the ROOT option creates a Customers JSON wrapper object around the generated JSON output:

PRINT '******* FOR JSON PATH output *******'
SELECT Id, Name, State, Country FROM dbo.Customer
FOR JSON PATH, ROOT('Customers')
GO
PRINT '******* FOR JSON AUTO output *******'
SELECT Id, Name, State, Country FROM dbo.Customer
FOR JSON AUTO, ROOT('Customers')

RESULT:
Sql FOR JSON Clause with ROOT option
Example 7: In case of FOR JSON PATH clause using “.” Symbol in the column aliases, we can name the each object in the resultant JSON array as shown below:

SELECT Id [Customer.Id], Name [Customer.Name],
 State [Customer.State], Country [Customer.Country]
FROM dbo.Customer FOR JSON PATH, ROOT('Customers')
GO

RESULT:
SQL FOR JSON using dot symbol in column name aliases
Using “.” symbol in the column aliases doesn’t have any effect in the resulting JSON output in case of FOR JSON AUTO as shown below:

SELECT Id [Customer.Id], Name [Customer.Name] 
FROM dbo.Customer FOR JSON AUTO, ROOT('Customers')

RESULT:
SQL FOR JSON AUTO using dot symbol in the column aliases
Example 8: We can convert each row into a JSON object with multiple sub-objects by using “.” Symbol in the column alias as shown below:

SELECT Id [Customer.Id], Name [Customer.Name], 
 State [Address.State], Country [Address.Country]
FROM dbo.Customer FOR JSON PATH, ROOT('Customers')

RESULT:
FOR JSON PATH dot symbol in the column aliases 2
Example 9: We can convert each row into a nested JSON object by using “.” Symbol in the column aliases as shown below:

SELECT Id [Customer.Id], Name [Customer.Name], 
 State [Customer.Address.State], 
 Country [Customer.Address.Country]
FROM dbo.Customer FOR JSON PATH, ROOT('Customers')

RESULT:
FOR JSON PATH dot symbol in the column aliases to produce nested JSON output
[ALSO READ]:

lax and strict JSON Path modes in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , , , , , ,

In this article let us understand lax and strict JSON Path modes in Sql Server 2016 in the context of a JSON_VALUE, JSON_QUERY and OPENJSON functions with extensive list of examples.

[ALSO READ]:

lax and strict JSON Path modes influence on the JSON_VALUE function output

JSON_VALUE is a scalar function that returns the scalar value from the input JSON text from the specified JSON path location.


SYNTAX of JSON_VALUE function: 

JSON_VALUE ( json_string,  [json_path_mode]json_path )

WHERE:

json_string is the JSON string from which the scalar value will be extracted.

json_path is the location of the scalar value in the json_string.

json_path_mode is optional in the json_path. json_path can be prefixed by the json_path_mode. json_path_mode can be lax or strict. Lax is the default path mode, if json_path is invalid (i.e. it is not present in the json_string) then it returns null, but if path mode is strict it will raise an error.

Let us understand the JSON Path Modes with below extensive list of examples:

Example 1: Let us try to get a non-existent property from a JSON string

SELECT JSON_VALUE(
 '{"FirstName":"Basavaraj","LastName":"Biradar"}',
 '$.City') 'City'

RESULT:
JSON Path mode lax and strict Example 1

From the result it is clear that JSON_Value function returns a NULL value if it is not able find a value at the specified JSON path. In this case path is trying to find a property City at the root level which is not present so it has returned the value NULL.

The default the path mode is lax, because of that we got NULL as a result if the JSON path is not present in the JSON string.

Example 2: This example demonstrates how we can specify the JSON path mode. Let us re-write the previous example with JSON path mode as strict

SELECT JSON_VALUE(
 '{"FirstName":"Basavaraj","LastName":"Biradar"}',
 'strict$.City') 'City'

RESULT:

Msg 13608, Level 16, State 1, Line 1
Property cannot be found in specified path.

From the result it is clear that if JSON path mode is strict then the JSON_VALUE function will return an error instead of returning a NULL value in case if it is not able to find value at the specified JSON path. In the above example the JSON path expression ‘strict$.City’ starts with the word strict, this keyword enforces the JSON path mode as strict. If we don’t specify this keyword by default it is lax. Specifying lax is optional as it is the default JSON path mode.

Example 3: Let us execute the previous example by explicitly specifying the lax JSON path mode

SELECT JSON_VALUE(
 '"FirstName":"Basavaraj","LastName":"Biradar"}',
 'lax$.City') 'City'

RESULT:
JSON Path mode lax and strict Example 3

From the result it is clear that lax is the default JSON path mode, if it is specified or not specified and JSON path doesn’t exists in the JSON string then it returns NULL value.

Example 4: This is an example of strict JSON path mode where the JSON_VALUE function returning a scalar value as the JSON path is valid.

SELECT JSON_VALUE(
 '{"FirstName":"Basavaraj","LastName":"Biradar"}',
 'strict$.FirstName') 'FirstName'

RESULT:
JSON Path mode lax and strict Example 4

Example 5: In this example let us see whether JSON path mode lax or strict is case sensitive. Re-Write the previous example mby specifying the JSON path mode strict in upper case and verify the result

SELECT JSON_VALUE(
 '{"FirstName":"Basavaraj","LastName":"Biradar"}',
 'STRICT$.FirstName') 'FirstName'

RESULT:

Msg 13607, Level 16, State 3, Line 1
JSON path is not properly formatted. Unexpected character found at position 0.

From the result it is clear that we need specify the JSON path modes lax or strict only in lower case.

lax and strict JSON Path modes influence on the JSON_QUERY function output

Example 1: Try to get a JSON scalar value (i.e. non-JSON object or array)

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'$.Name') Name

RESULT:
JSON_QUERY Sql Example 3

Example 2:Let us try to re-execute the example 1 by re-setting the JSON path mode as strict

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'strict$.Name') Name

RESULT:

Msg 13608, Level 16, State 2, Line 4
Property cannot be found in specified path.

Let us try executing the above query by explicitly specifying the default JSON path mode lax

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'lax$.Name') Name

RESULT:
JSON_QUERY Sql Example 4 2

lax and strict JSON path modes influence on the OPENJSON JSON function output

let us understand the influence of the lax/strict JSON path modes on the OPENJSON function output with extensive list of examples

Example 1: In the below example the json_path ‘$.City’ mentioned in the OPENJSON function doesn’t exists in the input JSON string. And in this json_path the JSON path mode has not be specified explicitly, so it goes with default JSON path mode lax.

DECLARE @json_string NVARCHAR(MAX) = N'
{"Id":1,"Name":"Basavaraj",
  "Address":{"State":"KA","Country":"India"}}'
SELECT [key],value
FROM OPENJSON(@json_string, '$.City')

RESULT:
OPENJSON lax and strict JSON path modes Example 1

Let us execute this example by specifying the JSON path mode as strict

DECLARE @json_string NVARCHAR(MAX) = 
	N'{"Id":1,"Name":"Basavaraj",
	   "Address":{"State":"KA","Country":"India"}}'
SELECT [key],value
FROM OPENJSON(@json_string, 'strict$.City')

RESULT:

Msg 13608, Level 16, State 3, Line 4
Property cannot be found in specified path.

Let us execute this example by explicitly specifying the default JSON path mode lax

DECLARE @json_string NVARCHAR(MAX) = 
	N'{"Id":1,"Name":"Basavaraj",
	   "Address":{"State":"KA","Country":"India"}}'
SELECT [key],value
FROM OPENJSON(@json_string, 'lax$.City')

RESULT:
OPENJSON lax and strict JSON path modes Example 1 2

Example 2: In the below OPENJSON function with explicit schema declaration, DOB column value JSON path ‘$.Dob’ doesn’t exists in the input JSON string. In the json_path the JSON path mode has not be specified explicitly, so it goes with default JSON path mode which is lax.

DECLARE @json_string NVARCHAR(MAX) = 
 N'{"Customers":
    [{"Id":1,"Name":"Basavaraj",
            "Address":{"State":"KA","Country":"India"}},
     {"Id":2,"Name":"Kalpana",
            "Address":{"State":"NY","Country":"United State"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Customers')
WITH(EmployeeId INT '$.Id', EmployeeName Varchar(100) '$.Name',
		DOB Varchar(10) '$.Dob')

RESULT:
OPENJSON lax and strict JSON path modes Example 2

Let us execute the previous query by specifying the JSON path mode as strict i.e. ‘strict$.Dob’

DECLARE @json_string NVARCHAR(MAX) = N'
{"Customers":
    [{"Id":1,"Name":"Basavaraj",
      "Address":{"State":"KA","Country":"India"}},
     {"Id":2,"Name":"Kalpana",
      "Address":{"State":"NY","Country":"United State"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Customers')
WITH(EmployeeId INT '$.Id', EmployeeName Varchar(100) '$.Name',
		DOB Varchar(10) 'strict$.Dob')

RESULT:

Msg 13608, Level 16, State 6, Line 9
Property cannot be found in specified path.

Let us execute the above query by specifying the default JSON path mode explicitly ‘lax$.Dob’

DECLARE @json_string NVARCHAR(MAX) = N'
{"Customers":
    [{"Id":1,"Name":"Basavaraj",
      "Address":{"State":"KA","Country":"India"}},
     {"Id":2,"Name":"Kalpana",
      "Address":{"State":"NY","Country":"United State"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Customers')
WITH(EmployeeId INT '$.Id', EmployeeName Varchar(100) '$.Name',
		DOB Varchar(10) 'lax$.Dob')

RESULT:
OPENJSON lax and strict JSON path modes Example 2 2
[ALSO READ]:

JSON_QUERY Function in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , , ,

JSON_QUERY is one of the new JSON function introduced in Sql Server 2016 to query the JSON data. JSON_QUERY basically returns the JSON fragment (i.e. JSON object or an array) from the input JSON string from the specified JSON path.

SYNTAX:

JSON_QUERY ( json_string,  json_path )

WHERE:

json_string is the JSON string from which the JSON fragment will be extracted.

json_path is the location of the JSON string in the json_string. Within json_path we can specify the path mode, it can be lax or strict. Lax is the default path mode, if json_path is invalid (i.e. it is not present in the json_string) then it returns null, but if path mode is strict it will raise an error.

This function will return error even in the scenario if the specified json_path is resulting in a scalar value other than the JSON object or array. Where as JSON_VALUE works the other way, it returns an error in case the JSON path is

[ALSO READ]:

Let us understand JSON_QUERY function with extensive list of examples:

Example 1: In this example let us try to get the Hobbies array using the JSON_QUERY function

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'$.Hobbies') Hobbies

RESULT:
JSON_QUERY Sql Example 1 1

Here in the json_path the $ symbol implies the json_string and $. Hobbies means Hobbies property in the json_string at the root level.

Let us try doing the same using the JSON_VALUE function

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_VALUE(@json_str,'$.Hobbies') Hobbies

RESULT:
JSON_QUERY Sql Example 1 2

From the result it is clear that JSON_VALUE function is not for reading the JSON object or array, instead it is for reading the scalar JSON values like string, integer etc.

Example 2: Try to get the complete JSON string from the root

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
--Get customer details
SELECT JSON_QUERY(@json_str,'$') JSON
GO

RESULT:
JSON_QUERY Sql Example 2

Example 3: Try to get a JSON scalar value (i.e. non-JSON object or array)

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'$.Name') Name

RESULT:
JSON_QUERY Sql Example 3
[ALSO READ]:lax and strict JSON Path modes in Sql Server 2016
Example 4: lax and strict JSON path modes in JSON_QUERY

Let us try to re-execute the example 3 by setting the JSON path mode as strict

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'strict$.Name') Name

RESULT:

Msg 13608, Level 16, State 2, Line 4
Property cannot be found in specified path.

Let us try executing the above query by explicitly specifying the default JSON path mode lax

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'lax$.Name') Name

RESULT:
JSON_QUERY Sql Example 4 2
Example 5: In this example try to read on of the array element which in-turn is not a JSON object or an array

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'$.Hobbies[1]') Hobby

RESULT:
JSON_QUERY Sql Example 5 1

Try to execute the above query using the JSON_VALUE function instead of JSON_QUERY function

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_VALUE(@json_str,'$.Hobbies[1]') Hobby

RESULT:
JSON_QUERY Sql Example 5 2

From these examples it is clear that we can use the JSON_QUERY function to extract a JSON object or an array only, but not for the scalar values like string, integer etc.

Example 6: Try to extract complete JSON from the JSON string

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Customers":
	[{"Id":1,"Name":"Basavaraj",
	  "Address":{"State":"KA","Country":"India"}},
	 {"Id":2,"Name":"Kalpana",
	  "Address":{"State":"NY","Country":"United State"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$') CompleteJSON

RESULT:
JSON_QUERY Sql Example 6

Example 7: In this example try to extract the Customers array

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Customers":
	[{"Id":1,"Name":"Basavaraj",
			"Address":{"State":"KA","Country":"India"}},
	 {"Id":2,"Name":"Kalpana",
			"Address":{"State":"NY","Country":"United State"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$.Customers') CustomersArray

RESULT:
JSON_QUERY Sql Example 7

Example 8: In this example extract the first customer JSON object

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Customers":
	[{"Id":1,"Name":"Basavaraj",
			"Address":{"State":"KA","Country":"India"}},
	 {"Id":2,"Name":"Kalpana",
			"Address":{"State":"NY","Country":"United State"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$.Customers[0]') CustomerObject

RESULT:
JSON_QUERY Sql Example 8

Example 9: In this example try to extract the first customer’s address object

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Customers":
	[{"Id":1,"Name":"Basavaraj",
			"Address":{"State":"KA","Country":"India"}},
	 {"Id":2,"Name":"Kalpana",
			"Address":{"State":"NY","Country":"United State"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$.Customers[0].Address') AddressObject

RESULT:
JSON_QUERY Sql Example 9

[ALSO READ]: