The JSON_QUERY function in SQL Server is a useful tool for working with JSON data. Introduced in SQL Server 2016, it is part of the suite of JSON functions that allow you to parse, query, and manipulate JSON documents stored in a database. The primary purpose of JSON_QUERY is to extract a JSON object or array from a JSON string.
Syntax
JSON_QUERY ( expression [ , path ] )
expression: The JSON string from which you want to extract a JSON object or array.
path: An optional argument specifying the path to the JSON object or array you want to extract. This path is written using JSON Path syntax.
Key Features
Extracts JSON Objects or Arrays
Unlike the JSON_VALUE function, which retrieves scalar values (like strings or numbers) from JSON data, JSON_QUERY returns a JSON fragment (either an object or an array).
Returns NULL for Nonexistent Paths
If the specified path does not exist in the JSON string, JSON_QUERY returns NULL.
Maintains JSON Structure
The extracted JSON fragment retains its structure, making JSON_QUERY ideal for scenarios where you need to preserve nested objects or arrays.
Examples
Basic Extraction
Assume you have a JSON string ‘{ “name”: “John”, “address”: { “city”: “Seattle”, “zip”: “98101” } }’.
SELECT JSON_QUERY('{"name": "John", "address": { "city": "Seattle", "zip": "98101" }}', '$.address') AS Address;
Result:
{ "city": "Seattle", "zip": "98101" }
Extracting an Array
Consider a JSON string containing an array of values:
‘{ “id”: 1, “tags”: [“SQL”, “JSON”, “Server”] }’.
SELECT JSON_QUERY('{"id": 1, "tags": ["SQL", "JSON", "Server"]}', '$.tags') AS Tags;
Result:
["SQL", "JSON", "Server"]
Handling Missing Paths
If the path does not exist, JSON_QUERY returns NULL.
SELECT JSON_QUERY('{"name": "John"}', '$.address') AS Address;
Result:
NULL
Common Use Cases
Preserving Nested JSON Data
When working with APIs or NoSQL-style data models, it is common to store nested JSON data in SQL Server. JSON_QUERY helps retrieve such data in its original structure.
Combining with FOR JSON
You can use JSON_QUERY in conjunction with FOR JSON to build or manipulate complex JSON outputs.
Filtering JSON Data
When combined with JSON_VALUE, you can filter rows based on specific conditions in the JSON while retrieving nested structures with JSON_QUERY.
Considerations
Error Handling: If the specified path points to a scalar value instead of a JSON object or array, JSON_QUERY returns NULL.
Performance: Parsing JSON can add overhead to query execution, so use these functions judiciously in performance-critical scenarios.
By using JSON_QUERY, SQL Server enables you to work with JSON data effectively, facilitating integration between relational and semi-structured data.