Home » SQL JSON_QUERY Function

SQL JSON_QUERY Function

SQL JSON_QUERY is a JSON function which extracts an object or an array from a JSON string.





JSON_QUERY function introduced in SQL Server 2016.

You can not extract a scalar value using JSON_QUERY function. It always extract JSON object or JSON Array. To extract scalar value you can use JSON_VALUE function.

SYNTAX

JSON_QUERY ( json_string ,[path mode] json_path )

json_string is the string that contains the JSON string.

json_path is the path of the JSON string which specifies the path of JSON Object or the Array to extract in the json_string.

[path mode]: It is an optional, It can be specified with json_path, it can be either lax or strict.

Lax is the default path_mode, if specified json_path is not present in the json_string then it returns null, but if path_mode is strict it will always raise an error.

Lets look at an example of JSON_QUERY function in SQL Server.

Extracting the JSON Object  and JSON Array from JSON String

Lets extracts the color array using the JSON_QUERY function.

DECLARE @string_Json NVARCHAR(150)
          = '{"Item":"Iphone 5S",
            "Price":25000,
           "Color":["Black", "Grey", "Gold"]}'
SELECT JSON_QUERY(@string_Json,'$.Color') AS JSON_Color

Lets extract the second JSON object from the ItemList key.

DECLARE @string_Json NVARCHAR(4000);
SET @string_Json = N'{
"ItemList":
[ {
"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]
},
{
"Item":"Iphone 7",
"Price":25000,
"Color":["Black", "Grey"]
} 
]
}';
SELECT JSON_QUERY(@string_Json,'$.ItemList[1]') AS JSON_Color

Using JSON_QUERY function to read a scalar value from JSON String

JSON_QUERY function always returns a JSON object or JSON Array, so you can not use JSON_QUERY function to read the scalar value.

Lets try to extract the scalar value that is first element of color array using JSON_QUERY function.

DECLARE @string_Json NVARCHAR(MAX)
= '{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'

SELECT JSON_QUERY(@string_Json,'$.Color[0]') AS JSON_Color

As you can see, it returns null means JSON_QUERY does not read scalar value.

JSON_QUERY VS JSON_VALUE

As you have seen in above example, JSON_QUERY does not extract a scalar value from JSON string, so to get the scalar value from JSON string you can use JSON_VALUE function as shown below.

DECLARE @string_Json NVARCHAR(MAX)
= '{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'

SELECT JSON_VALUE(@string_Json,'$.Color[0]') AS JSON_Color





As you can see, it returns the value of first element of color array and this is one of the difference between JSON_QUERY and JSON_VALUE function that is  JSON_QUERY returns the JSON object and JSON array while JSON_VALUE function returns only scalar value from JSON string.

Extracting the complete JSON from JSON String

Extracting the complete JSON from JSON string you can use the ‘$’ argument as shown below.

DECLARE @string_Json NVARCHAR(MAX);
SET @string_Json = N'{
"ItemList":
[ {
"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]
},
{
"Item":"Iphone 7",
"Price":25000,
"Color":["Black", "Grey"]
} 
]
}';
SELECT JSON_QUERY(@string_Json,'$') AS Complete_JSONString

As you can see, It returns complete JSON string.

Using Path Mode lax and strict with JSON_QUERY function

JSON_QUERY function uses a default path mode lax, you can also specified it explicitly with Json path.




In following statement use lax path mode with JSON path and try to read scalar value from JSON string. Obviously the scalar is value is not an object or an array so, JSON_QUERY function returns null instead of raising an error as the path mode lax is used which returns null when value is not JSON object or JSON array.

DECLARE @string_Json NVARCHAR(MAX)
= '{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'

SELECT JSON_QUERY(@string_Json,'lax$.Color[0]') AS JSON_Color

Lets use a strict path mode, if you try to read a scalar value using strict path mode then you will get an error as shown below.

DECLARE @string_Json NVARCHAR(MAX)
= '{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'

SELECT JSON_QUERY(@string_Json,'strict$.Color[0]') AS JSON_Color

As you can see, using a strict path mode returns an error as result is not a JSON object or JSON array.

Also Read..

FOR JSON Clause

JSON_MODIFY

JSON_VALUE




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.