Home ยป SQL JSON_VALUE Function

SQL JSON_VALUE Function

JSON_VALUE is a JSON function which is used to extracts scalar value from JSON string.





It returns a single text value of type nvarchar.

It can not be used to extract JSON object or JSON array from JSON string, for this you can use JSON_QUERY function.

SYNTAX

JSON_VALUE ( json_string , [path mode] json_path )

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

json_path is the path of the JSON string which specifies the path of scalar value 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 using JSON_VALUE function in SQL.

Extracting a value of property using JSON_VALUE function

Lets extract the value of Item property from following JSON string.

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

SELECT JSON_VALUE(@string_Json,'$.Item') AS JSON_Color

Extracting the value from JSON Object and Array

Lets take a look on few set of examples on extracting a scalar value from JSON object and Array using JSON_VALUE function.

Example 1:

Lets extract the third value of JSON array Color from JSON String.

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

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

Example 2:

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_VALUE(@string_Json,'$.ItemList[1].Item') AS Item,
JSON_VALUE(@string_Json,'$.ItemList[1].Color[0]') AS Color

Example 3:

DECLARE @string_Json NVARCHAR(MAX); 
SET @string_Json = 
N'{ 
"ItemList": [ 
{
"Item":"Iphone 5S", 
"Price":25000, 
"Varient":[{"Color":"Grey", "Price":25000},
{"Color":"Silver", "Price":15000},
{"Color":"Black", "Price":20000}]
},
{ "Item":"Iphone 7",
"Price":25000, 
"Varient":[{"Color":"Grey", "Price":45000},
{"Color":"Silver", "Price":35000},
{"Color":"Black", "Price":25000}]
}
] 
}';

SELECT JSON_VALUE(@string_Json,'$.ItemList[1].Varient[2].Price') AS Price

 

Extracting JSON object or JSON Array using JSON_VALUE function

When you try to extract a JSON object or JSON array using JSON_VALUE function it returns an error as it always extract a scalar value.



Lets try to extract JSON array Color from below JSON string.

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

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

As you can see, it returns null because you are trying to extract a JSON array from JSON string and JSON_VALUE function extract only scalar value.

JSON_VALUE VS JSON_QUERY

As you have seen in above example, JSON_VALUE function does not extract JSON object or JSON array from JSON string. If you want to extract JSON object or JSON array from JSON string then can use JSON_QUERY function as shown below.

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

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

As you can JSON_QUERY returns JSON array from JSON string, this is the difference between JSON_VALUE and JSON_QUERY. JSON_VALUE extract only scalar value from JSON string while JSON_QUERY extracts a JSON object or JSON array from JSON string.

Also Read..

FOR JSON Clause

JSON_MODIFY

JSON_QUERY



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.