Advertisement

Latest Post

Thursday, 27 July 2017

WHAT IS JSON_VALUE FUNCTION IN SQL SERVER? Feature of JSON_VALUE function

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



Government Jobs