JSON_VALUE
Function: It returns the scalar value from the input JSON
string by passing the location of the scalar value.
SYNTAX:
JSON_VALUE ( json_string, json_path )
Example
1: Simple Example of JSON_VALUE function
DECLARE @inputJSON_string NVarchar(Max) = '{"ENAME":"ALOK","JOB":"ADMIN"}'
SELECT
JSON_VALUE(@inputJSON_string,'$.ENAME') AS 'Employee Name',
JSON_VALUE(@inputJSON_string,'$.JOB') AS 'Role Name'
OUTPUT:-
Employee Name
|
Role Name
|
ALOK
|
ADMIN
|
Example
2: JSON path expression is case sensitive. It should
be match with JSON property name.
DECLARE @inputJSON_string NVarchar(Max) = '{"ENAME":"ALOK","JOB":"ADMIN"}'
SELECT
JSON_VALUE(@inputJSON_string,'$.ename') AS 'Employee Name',
JSON_VALUE(@inputJSON_string,'$.job') AS 'Role Name'
OUTPUT:-
Employee Name
|
Role Name
|
NULL
|
NULL
|
Example
3: If JSON property name has an empty space in it
that will gives error
DECLARE @inputJSON_string NVarchar(Max) = '{"E NAME":"ALOK","JOB":"ADMIN"}'
SELECT
JSON_VALUE(@inputJSON_string,'$.E NAME') AS 'Employee Name',
JSON_VALUE(@inputJSON_string,'$.JOB') AS 'Role Name'
OUTPUT:-
Msg 13607,
Level 16, State 4, Line 3
JSON path
is not properly formatted. Unexpected character ' ' is found at position 3.
Example
4: Fetching values from JSON object that have one of
its properties is an Array ([,])
DECLARE @inputJSON_string NVARCHAR(MAX) = '{"ENAME":"ALOK","JOB":"ADMIN","Sites":["www.code-view.com",
"www.code-sample.com", "www.code-sample.xyz"]}'
SELECT
JSON_VALUE(@inputJSON_string,'$.ENAME') AS 'Employee Name',
JSON_VALUE(@inputJSON_string,'$.JOB') AS 'Role Name',
JSON_VALUE(@inputJSON_string,'$.Sites[0]') AS 'Sites1',
JSON_VALUE(@inputJSON_string,'$.Sites[1]') AS 'Sites2',
JSON_VALUE(@inputJSON_string,'$.Sites[2]') AS'Sites2'
OUTPUT:-
Employee Name
|
Role Name
|
Sites1
|
Sites2
|
Sites2
|
ALOK
|
ADMIN
|
www.code-view.com
|
www.code-sample.com
|
www.code-sample.xyz
|
No comments:
Post a comment