JSON_QUERY#
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.
Syntax#
JSON_QUERY ( expression [ , path ] )
Arguments#
expression
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.
path
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).
Examples#
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.
DECLARE @jsonInfo NVARCHAR(MAX)
SET @jsonInfo=N'{"info":{"address":[{"town":"Warsaw"},{"town":"Miami"}]}}';
SElect @town=JSON_QUERY(@jsonInfo,'$.info.address'); -- [{"town":"Warsaw"},{"town":"Miami"}]