Latest Post

Thursday, 27 July 2017

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"]}

Government Jobs