Menu

SQL Server JSON Functions

SQL Server provides JSON functions for working with JSON text stored in character columns, and newer SQL Server platforms also provide a native json data type. The functions differ by task: validation checks syntax, extraction reads values, OPENJSON expands data into rows, and modification or construction functions return JSON text. For their shared key, array, and path-mode syntax, see the SQL Server JSON path guide.

Choose a JSON function

Task Function What it does
Check that a value is JSON ISJSON() Returns 1, 0, or NULL; SQL Server 2022 adds optional top-level type constraints.
Check whether a path is present JSON_PATH_EXISTS() Returns 1 or 0 for a path check, and NULL for SQL NULL input.
Check whether a value occurs at a JSON path JSON_CONTAINS() SQL Server 2025 adds a generally available typed scalar check; supported in Azure SQL Database, Azure SQL Managed Instance, and Fabric SQL database, but not Fabric Data Warehouse.
Read one scalar property JSON_VALUE() Returns a scalar value from a JSON path; by default, the return value is nvarchar(4000).
Read an object or array JSON_QUERY() SQL Server 2025 array wildcards and WITH ARRAY WRAPPER are GA and require native json input.
Expand a document into rows and columns OPENJSON() Returns object properties or array elements as rows; JSON text normally requires compatibility level 130 or later.
Change a value at a path JSON_MODIFY() Updates, inserts, deletes, or appends a value and returns the JSON document.
Build an array from expressions JSON_ARRAY() Constructs an array from SQL expressions; NULL ON NULL and ABSENT ON NULL control SQL NULL values.
Aggregate rows into an array JSON_ARRAYAGG() Builds a JSON array per result group and supports ordering its elements.
Aggregate key/value pairs into an object JSON_OBJECTAGG() Builds one JSON object per result group; NULL ON NULL is the default.
Build an object or serialize query rows JSON_OBJECT(), FOR JSON Creates an object from key/value pairs or formats query rows as JSON. Availability varies by SQL Server version and platform.

Aggregate availability: JSON_ARRAYAGG() and JSON_OBJECTAGG() are currently preview features in SQL Server 2025 (17.x). Microsoft lists them as generally available in Azure SQL Database, Azure SQL Managed Instance with the SQL Server 2025 or Always-up-to-date update policy, SQL database in Microsoft Fabric, and Fabric Data Warehouse. Check the current JSON_ARRAYAGG() and JSON_OBJECTAGG() references before using them on a specific service.

The examples and rules for JSON functions operating on varchar or nvarchar do not automatically describe native json storage. Check the target platform and function version before using SQL Server 2025 features. For JSON validity alone, start with ISJSON(); it does not enforce a schema or reject duplicate property names at the same level.

For the full Microsoft function and platform matrix, see JSON functions (Transact-SQL).

Advertisement

  1. ISJSON()

    Learn how SQL Server ISJSON() validates JSON text, returns NULL for SQL NULL, and checks top-level JSON types on SQL Server 2022 and later.

  2. JSON path expressions

    Learn SQL Server JSON path syntax for keys, array indexes, lax and strict modes, and SQL Server 2025 array wildcards.

  3. JSON_ARRAY()

    Learn how SQL Server JSON_ARRAY() builds arrays from SQL values, handles NULL elements, nests JSON constructors, and returns JSON text.

  4. JSON_ARRAYAGG()

    Learn SQL Server JSON_ARRAYAGG() row ordering and NULL handling, plus its preview status in SQL Server 2025 and availability on Azure SQL and Fabric.

  5. JSON_CONTAINS()

    Learn how SQL Server 2025 JSON_CONTAINS() searches JSON paths, compares typed scalar values, handles arrays, and supports LIKE matching.

  6. JSON_MODIFY()

    Learn how SQL Server JSON_MODIFY() updates or inserts properties, appends array items, deletes keys with NULL, and inserts JSON fragments safely.

  7. JSON_OBJECT()

    Learn how SQL Server JSON_OBJECT() constructs objects from key/value pairs, controls NULL properties, and nests JSON arrays or objects.

  8. JSON_OBJECTAGG()

    Learn SQL Server JSON_OBJECTAGG() key/value aggregation and NULL handling, plus its SQL Server 2025 preview status and Azure SQL and Fabric availability.

  9. JSON_PATH_EXISTS()

    Learn how SQL Server JSON_PATH_EXISTS() tests for a path, distinguishes a JSON null from a missing key, and filters JSON rows.

  10. JSON_QUERY()

    Learn SQL Server JSON_QUERY() object and array extraction, lax and strict paths, and SQL Server 2025 array wildcards with WITH ARRAY WRAPPER for native json input.

  11. JSON_VALUE()

    Learn SQL Server JSON_VALUE() scalar extraction, lax and strict paths, the 4,000-character limit, and SQL Server 2025 RETURNING for native json input.

  12. OPENJSON()

    Learn how SQL Server OPENJSON() returns JSON properties and array items as rows, maps values with WITH, and keeps nested objects with AS JSON.