Extracts an object or an array from a JSON string.

To extract a scalar value from a JSON string instead of an object or an array, see JSON_VALUE.


JSON_QUERY ( expression [ , path ] )



An expression. Typically the name of a variable or a column that contains JSON text.

If JSON_QUERY finds JSON that is not valid in expression before it finds the value identified by path, the function returns an error. If JSON_QUERY doesn’t find the value identified by path, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in expression.


A JSON path that specifies the object or the array to extract.

The default value for path is ‘$’. As a result, if you don’t provide a value for path, JSON_QUERY returns the input expression.

If the format of path isn’t valid, JSON_QUERY returns an error.

Return value#

Returns a JSON fragment of type nvarchar(max).


The following example returnS a JSON fragment from a CustomFields column in query results. The following example extracts the value of the JSON property town into a local variable.


SET @jsonInfo=N'{"info":{"address":[{"town":"Warsaw"},{"town":"Miami"}]}}';

SElect @town=JSON_QUERY(@jsonInfo,'$.info.address'); -- [{"town":"Warsaw"},{"town":"Miami"}]

See Also#