Implement equivalent of JSON_QUERY

[EitanBlumin]

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.

In SQL Server 2017 (14.x) and in Azure SQL Database, you can provide a variable as the value of path.

The JSON path can specify lax or strict mode for parsing. If you don't specify the parsing mode, lax mode is the default. For more info, see JSON Path Expressions (SQL Server).

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 collation of the returned value is the same as the collation of the input expression.

If the value is not an object or an array:

In lax mode, JSON_QUERY returns null.

In strict mode, JSON_QUERY returns an error.

Remarks

Lax mode and strict mode

The following table compares the behavior of JSON_QUERY in lax mode and in strict mode. For more info about the optional path mode specification (lax or strict), see JSON Path Expressions (SQL Server).

Path	Return value in lax mode	Return value in strict mode	More info
$	Returns the entire JSON text.	Returns the entire JSON text.	N/a
$.info.type	NULL	Error	Not an object or array.Use JSON_VALUEinstead.
$.info.address.town	NULL	Error	Not an object or array.Use JSON_VALUEinstead.
$.info."address"	N'{ "town":"Bristol", "county":"Avon", "country":"England" }'	N'{ "town":"Bristol", "county":"Avon", "country":"England" }'	N/a
$.info.tags	N'[ "Sport", "Water polo"]'	N'[ "Sport", "Water polo"]'	N/a
$.info.type[0]	NULL	Error	Not an array.
$.info.none	NULL	Error	Property does not exist.

Using JSON_QUERY with FOR JSON

JSON_QUERY returns a valid JSON fragment. As a result, FOR JSON doesn't escape special characters in the JSON_QUERY return value.

If you're returning results with FOR JSON, and you're including data that's already in JSON format (in a column or as the result of an expression), wrap the JSON data with JSON_QUERY without the path parameter.

Source: JSON_QUERY (Transact-SQL)