The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

JSON: read, transform, and produce documents from SQL Server

An API—an interface through which two systems exchange requests and responses—or an integration can deliver a JSON document that we need to validate and convert into rows. JSON is a structured text format with objects, arrays, properties, and values. SQL Server includes functions for querying values, opening arrays, modifying properties, and generating structured output.

These functions use paths, expressions that identify the part of the document we want to locate. A path starts at $, the root representing the complete document. Validate the input before depending on its content.

JSON operations

ISJSON: check the document syntax

ISJSON returns 1 when an expression contains valid JSON and 0 when it does not. It checks the document shape, not whether its properties satisfy the system’s rules.

ISJSON(Expresion)

JSON_VALUE: extract a scalar value

JSON_VALUE uses a path to locate one text, number, or logical value. A scalar value does not contain another object or array.

JSON_VALUE(Documento, Ruta)

JSON_QUERY: preserve an object or array

JSON_QUERY extracts a fragment containing an object or array and preserves its JSON structure.

JSON_QUERY(Documento, Ruta)

OPENJSON: convert JSON into rows

OPENJSON traverses an object or array and returns a tabular set. A tabular set has rows and columns that can be queried like a table.

OPENJSON(Documento, Ruta)

JSON_MODIFY: change a property

JSON_MODIFY returns another JSON expression with the specified value at the path. It does not change the source variable or column by itself; we must assign or store the result.

JSON_MODIFY(Documento, Ruta, ValorNuevo)

FOR JSON PATH: build JSON from rows

FOR JSON PATH transforms the final query rows into a JSON array. Column names or aliases determine the properties it produces.

SELECT Columna1, Columna2
FROM dbo.Tabla
FOR JSON PATH;

A path such as $.cliente.id starts at the root $, enters the cliente object, and then locates the id property.

Query values and fragments

DECLARE @Documento NVARCHAR(MAX) = N'
{
  "cliente": { "id": 101, "nombre": "Ana" },
  "productos": [
    { "id": 1, "cantidad": 2 },
    { "id": 5, "cantidad": 1 }
  ]
}';

SELECT
    JSON_VALUE(@Documento, '$.cliente.id') AS IdCliente,
    JSON_VALUE(@Documento, '$.cliente.nombre') AS Nombre,
    JSON_QUERY(@Documento, '$.productos') AS Productos;

JSON_VALUE returns a scalar value: a single text, number, or logical value. JSON_QUERY returns an object or array while preserving its JSON structure.

ISJSON(@Documento) checks whether the expression contains valid JSON. Syntactic validation does not confirm that every property exists or complies with our business rules.

Convert an array into rows

SELECT
    j.IdProducto,
    j.Cantidad
FROM OPENJSON(@Documento, '$.productos')
WITH
(
    IdProducto INT '$.id',
    Cantidad INT '$.cantidad'
) AS j;

The WITH clause defines the expected contract—the names and types accepted by the query—and converts each property to the indicated type. When the document shape varies, OPENJSON can also return generic key, value, and type columns.

Modify a property

SET @Documento = JSON_MODIFY(
    @Documento,
    '$.cliente.nombre',
    N'Ana Torres'
);

SELECT @Documento AS DocumentoActualizado;

JSON_MODIFY returns a new expression containing the change, so we assign its result. For several changes, we nest calls or apply them in stages.

Generate JSON from rows

SELECT
    p.IdProducto AS id,
    p.Nombre AS nombre,
    p.Precio AS precio
FROM dbo.Producto AS p
WHERE p.Activo = 1
ORDER BY p.IdProducto
FOR JSON PATH, ROOT('productos');

FOR JSON PATH uses aliases to build properties. ROOT wraps the array in a root object. The consumer must agree on names, types, nullability, and document shape.

Types and versions

JSON functions have been available since SQL Server 2016. In traditional versions, the document is usually stored in NVARCHAR; SQL Server 2025 also offers a native JSON type in preview, a capability still subject to change before general availability. The examples use text to remain compatible with a broad range of installations.

OPENJSON requires database compatibility level 130 or higher. Other new capabilities may depend on the version, so verify the target before publishing or deploying scripts.

What should be checked

We validate size, encoding, paths, types, and missing properties. lax mode tolerates a missing path and may return NULL; strict mode treats it as an error. We choose this behavior deliberately.

If we frequently filter by a property, a computed column—whose value comes from an expression—with an index can avoid parsing the complete document for every query. The index organizes those values to make access easier. Data that needs relationships, constraints, or frequent updates, however, usually belongs in regular columns and tables.