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