JSON_QUERY function

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.