Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
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 (Transact-SQL). For info about the differences between JSON_VALUE and JSON_QUERY
, see Compare JSON_VALUE and JSON_QUERY.
Transact-SQL syntax conventions
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.
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.
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.
Consider the following JSON text:
{
"info": {
"type": 1,
"address": {
"town": "Cheltenham",
"county": "Gloucestershire",
"country": "England"
},
"tags": ["Sport", "Water polo"]
},
"type": "Basic"
}
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_VALUE instead. |
$.info.address.town |
NULL |
Error | Not an object or array. Use JSON_VALUE instead. |
$.info."address" |
N'{ "town":"Cheltenham", "county":"Gloucestershire", "country":"England" }' |
N'{ "town":"Cheltenham", "county":"Gloucestershire", "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. |
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.
The following example shows how to return a JSON fragment from a CustomFields
column in query results.
SELECT PersonID,FullName,
JSON_QUERY(CustomFields,'$.OtherLanguages') AS Languages
FROM Application.People
The following example shows how to include JSON fragments in the output of the FOR JSON clause.
SELECT StockItemID, StockItemName,
JSON_QUERY(Tags) as Tags,
JSON_QUERY(CONCAT('["',ValidFrom,'","',ValidTo,'"]')) ValidityPeriod
FROM Warehouse.StockItems
FOR JSON PATH
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register today