JSON Built in functions in SQL Server

By | 1st October 2019
0 Comment
Contents hide
1 ISJSON
2 JSON_VALUE
2.1 Lax and Strict Mode
3 JSON_QUERY
3.1 Lax and Strict Mode
3.2 Example 1:
3.3 Example 2:

In this post, I will explain JSON built in functions in SQL server to work with JSON string. The JSON built in functions introduced in SQL Server 2016 are,

  • OPENJSON
  • ISJSON
  • JSON_VALUE
  • JSON_QUERY
  • JSON_MODIFY

In the previous post, I explained OPENJSON function in detail with examples. Now, I will explain rest of the functions with examples.

ISJSON

ISJSON function validates the JSON string is correctly formatted or not. It returns 1 when the JSON is valid and 0 when invalid.

ISJSON(JSON_String)

--Valid JSON returns 1
SELECT ISJSON(' {"Name":"Sean Paul", "Gender":"M"}')
SELECT ISJSON(' {"Name":"Sean Paul", "Age":30}')

--Invalid JSON returns 0
SELECT ISJSON(' {"Name":"Sean Paul", "Gender":M}')

JSON_VALUE

JSON_VALUE is the one of the built in functions to parse and extract a scalar value for a specified key from the JSON string.

JSON_VALUE(JSON_String, Path)

DECLARE @JSON NVARCHAR(1000) = N'{"Name":"Sean Paul", "Gender":"M", "Skills":["C#", "SQL", ".NET"], "Address":{"State":"MS","Country":"USA","ZIP":39759}}'
--Returns the value of the property 'Name'
SELECT JSON_VALUE(@JSON, '$.Name')
--Returns the first value from the 'Skills' array
SELECT JSON_VALUE(@JSON, '$.Skills[0]')
--Returns the ZIP value from the 'Address' object
SELECT JSON_VALUE(@JSON, '$.Address.ZIP')

Lax and Strict Mode

It helps to determine whether the function should return NULL or an error message. If the path specified is invalid or the return value is greater than 4000 characters:

  • In lax mode, JSON_VALUE function returns NULL.
  • In strict mode, JSON_VALUE function returns an error.
DECLARE @JSON NVARCHAR(1000) = N'{"Name":"Sean Paul", "Gender":"M", "Skills":["C#", "SQL", ".NET"], "Address":{"State":"MS","Country":"USA","ZIP":39759}}'
--Lax mode: The function returns NULL if the path is invalid
SELECT JSON_VALUE(@JSON, 'lax $.Age')
--Since it is the default mode, 'lax' keyword is not required
SELECT JSON_VALUE(@JSON, '$.Age')
--Strict mode: The function returns an error if the path is invalid
SELECT JSON_VALUE(@JSON, 'strict $.Age')

The final query in the above example returns the error ‘Property cannot be found on the specified JSON path’.

JSON_QUERY

JSON_QUERY function helps to extract the JSON array, object or array of objects from the JSON string.

JSON_QUERY(JSON_String, Path)

DECLARE @JSON NVARCHAR(1000) = N'{"Name":"Sean Paul", "Gender":"M", "Skills":["C#", "SQL", ".NET"], "Address":{"State":"MS","Country":"USA","ZIP":39759}}'

--Returns the 'Skills' array
SELECT JSON_QUERY(@JSON, '$.Skills')
--Returns the 'Address' object
SELECT JSON_QUERY(@JSON, '$.Address')

Lax and Strict Mode

If the path specified is invalid or the return value is not an array or object:

  • In lax mode, JSON_QUERY function returns NULL.
  • In strict mode, JSON_QUERY function returns an error.

Example 1: 

DECLARE @JSON NVARCHAR(1000) = N'{"Name":"Sean Paul", "Gender":"M", "Skills":["C#", "SQL", ".NET"], "Address":{"State":"MS","Country":"USA","ZIP":39759}}'

--The type of 'Gender' property is not an array or object
--Lax mode: Returns NULL 
SELECT JSON_QUERY(@JSON, 'lax $.Gender')
--Strict mode: Returns Error 
SELECT JSON_QUERY(@JSON, 'strict $.Gender')

Example 2: 

DECLARE @JSON NVARCHAR(1000) = N'{"Name":"Sean Paul", "Gender":"M", "Skills":["C#", "SQL", ".NET"], "Address":{"State":"MS","Country":"USA","ZIP":39759}}'

--'Age' property is missing in the JSON string
--Lax mode: Returns NULL
SELECT JSON_QUERY(@JSON, 'lax $.Age')
--Strict mode: Returns error
SELECT JSON_QUERY(@JSON, 'strict $.Age')