JSON_QUERY Function in Sql Server 2016

JSON_QUERY Function: It gives JSON fragment from the input JSON string from the specified JSON path.

SYNTAX:  JSON_QUERY ( inputJSON_string, json_path )

inputJSON_string is the JSON string from which the JSON fragment will be extracted.

json_path is the location of the JSON string in the inputJSON_string.

Example 1: using the JSON_QUERY function get the Sites 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_QUERY(@inputJSON_string,'$.Sites') Sites

OUTPUT:-

Sites

["www.code-view.com", "www.code-sample.com", "www.code-sample.xyz"]

REMARK: - $ symbol implies the json_string and $. Sites means Sites property in the json_string at the root level.

Example 2: Fetch the complete JSON string from the root level.

DECLARE @inputJSON_string NVARCHAR(MAX) = '{"ENAME":"ALOK","JOB":"ADMIN","Sites":["www.code-view.com", "www.code-sample.com", "www.code-sample.xyz"]}'

SELECT JSON_QUERY(@inputJSON_string,'$') Sites

OUTPUT:-

Sites

{"ENAME":"ALOK","JOB":"ADMIN","Sites":["www.code-view.com", "www.code-sample.com", "www.code-sample.xyz"]}