Aplicación de formato JSON a los resultados de consulta con FOR JSON

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (solo grupo de SQL sin servidor)

Aplique formato JSON a los resultados de las consultas o exporte datos de SQL Server como JSON mediante la adición de la cláusula FOR JSON a una instrucción SELECT. Use la cláusula FOR JSON para simplificar las aplicaciones cliente mediante la delegación del formato de la salida JSON desde la aplicación a SQL Server.

Nota:

Azure Data Studio es el editor de consultas recomendado para las consultas JSON porque da formato automáticamente a los resultados JSON, como se muestra en este artículo. SQL Server Management Studio muestra una cadena sin formato.

Aplicar formato a resultados de consultas

Al usar la cláusula FOR JSON, puede especificar la estructura de la salida JSON de forma explícita, o bien dejar que la estructura de la instrucción SELECT determine la salida.

• Para mantener el control total sobre el formato de la salida JSON, use FOR JSON PATH. Puede crear objetos contenedores y anidar propiedades complejas.

• Para aplicar formato a la salida JSON de forma automática según la estructura de la instrucción SELECT, use FOR JSON AUTO.

Este es un ejemplo de una instrucción SELECT con la cláusula FOR JSON y su salida.

FOR JSON PATH

Control de la salida con FOR JSON PATH

En el modo PATH, puede usar la sintaxis de puntos (por ejemplo, Item.Price) para dar formato a la salida anidada.

Esta es una consulta de ejemplo en la que se usa el modo PATH con la cláusula FOR JSON. En el ejemplo siguiente también se usa la opción ROOT para especificar un elemento raíz con nombre.

Más información sobre FOR JSON PATH

Para más información detallada y ejemplos, vea Formato de salida JSON anidada con el modo PATH (SQL Server).

Para ver la sintaxis y el uso, consulte Cláusula FOR (Transact-SQL).

FOR JSON AUTO

Control de la salida con FOR JSON AUTO mediante la instrucción SELECT

En el modo AUTO, la estructura de la instrucción SELECT determina el formato de la salida JSON.

Los valores NULL no se incluyen en la salida de forma predeterminada. Puede usar INCLUDE_NULL_VALUES para cambiar este comportamiento.

Esta es una consulta de ejemplo en la que se usa el modo AUTO con la cláusula FOR JSON.

SELECT name, surname
FROM emp
FOR JSON AUTO;

Y este es el JSON devuelto.

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

Ejemplo con JOIN y NULL

En el siguiente ejemplo de SELECT...FOR JSON AUTO se incluye una visualización del aspecto que tendrían los resultados JSON cuando hay una relación uno a varios entre los datos de las tablas donde se ha aplicado.

También se ilustra la ausencia del valor NULL en el código JSON devuelto. Sin embargo, este comportamiento predeterminado se puede invalidar si se usa la palabra clave INCLUDE_NULL_VALUES en la cláusula FOR.

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

CREATE TABLE #tabClass (
    ClassGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    ClassName NVARCHAR(32) NOT NULL
);

CREATE TABLE #tabStudent (
    StudentGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    StudentName NVARCHAR(32) NOT NULL,
    ClassGuid UNIQUEIDENTIFIER NULL -- Foreign key.
);
GO

INSERT INTO #tabClass (ClassGuid, ClassName)
VALUES
    ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
    ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
    ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting');

INSERT INTO #tabStudent (StudentName, ClassGuid)
VALUES
    ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
    ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', '98509D36-A2C8-4A65-A310-E744F5621C83'),
    ('Carla Cap', null);
GO

SELECT c.ClassName,
    s.StudentName
FROM #tabClass AS c
RIGHT JOIN #tabStudent AS s ON s.ClassGuid = c.ClassGuid
ORDER BY c.ClassName,
    s.StudentName
FOR JSON AUTO
    -- To include NULL values in the output, uncomment the following line:
    --, INCLUDE_NULL_VALUES
    ;
GO

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

Y este sería el JSON que se obtendría con la instrucción SELECT anterior.

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[
   {"s":[{"StudentName":"Carla Cap"}]},
   {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
   {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
   {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

Más información sobre FOR JSON AUTO

Para más información detallada y ejemplos, vea Aplicar formato a la salida JSON automáticamente con el modo AUTO (SQL Server).

Para ver la sintaxis y el uso, consulte Cláusula FOR (Transact-SQL).

Controlar otras opciones de salida JSON

Controla la salida de la cláusula FOR JSON mediante las siguientes opciones adicionales.

• ROOT

  Para agregar un solo elemento de nivel superior a la salida JSON, especifique la opción ROOT. Si no especifica esta opción, la salida JSON no tiene ningún elemento raíz. Para obtener más información, consulte Agregar un nodo raíz a la salida JSON con la opción ROOT (SQL Server).

• INCLUDE_NULL_VALUES

  Para incluir valores NULL en la salida JSON, especifique la opción INCLUDE_NULL_VALUES. Si no especifica esta opción, la salida no incluye las propiedades JSON para los valores NULL en los resultados de la consulta. Para obtener más información, consulte Inclusión de valores Null en JSON - Opción INCLUDE_NULL_VALUES.

• WITHOUT_ARRAY_WRAPPER

  Para quitar los corchetes que rodean la salida JSON de la cláusula FOR JSON de manera predeterminada, especifique la opción WITHOUT_ARRAY_WRAPPER. Use esta opción para generar un objeto JSON único como salida de un resultado de fila única. Si no especifica esta opción, a la salida JSON se le aplica un formato como una matriz, es decir, está delimitada por corchetes. Para obtener más información, consulte Quitar corchetes de la salida JSON - Opción WITHOUT_ARRAY_WRAPPER.

Salida de la cláusula FOR JSON

La salida de la cláusula FOR JSON tiene las siguientes características:

1. El conjunto de resultados contiene una sola columna.

   • Un conjunto de resultados pequeño puede contener una sola fila.
   • Un conjunto de resultados grande divide la cadena JSON larga en varias filas.

     • De forma predeterminada, SQL Server Management Studio (SSMS) concatena los resultados en una sola fila cuando el valor de salida es Resultados a cuadrícula. La barra de estado de SSMS muestra el recuento de filas real.

     • Otras aplicaciones cliente pueden requerir código para recombinar los resultados extensos en una sola cadena JSON válida mediante la concatenación del contenido de varias filas. Para obtener un ejemplo de este código en una aplicación de C#, vea Uso de salidas FOR JSON en una aplicación cliente de C#.

       

2. Los resultados reciben el formato de una matriz de objetos JSON.

   • El número de elementos de la matriz JSON es igual al número de filas en los resultados de la instrucción SELECT (antes de aplicar la cláusula FOR JSON).

   • Cada fila de los resultados de la instrucción SELECT (antes de aplicar la cláusula FOR JSON) se convierte en un objeto JSON independiente en la matriz.

   • Cada columna de los resultados de la instrucción SELECT (antes de aplicar la cláusula FOR JSON) se convierte en una propiedad del objeto JSON.

3. Tanto los nombres de las columnas como sus valores van acompañados de un carácter de escape, como reza la sintaxis de JSON. Para obtener más información, consulte Cómo FOR JSON inserta caracteres de escape en los caracteres especiales y caracteres de control (SQL Server).

Ejemplo

Este es un ejemplo en el que se muestra cómo la cláusula FOR JSON da formato a la salida JSON.

Resultados de la consulta

A	B	C	D
10	11	12	X
20	21	22	Y
30	31	32	Z

Salida de JSON

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}]

Contenido relacionado

• Conversión por parte de FOR JSON de tipos de datos de SQL Server en tipos de datos JSON (SQL Server)
• Cómo FOR JSON inserta caracteres de escape en los caracteres especiales y caracteres de control (SQL Server)
• JSON as a bridge between NoSQL and relational worlds (JSON como puente entre los universos NoSQL y relacional)
• SELECT: cláusula FOR (Transact-SQL)
• Uso de salidas FOR JSON en SQL Server y en aplicaciones cliente (SQL Server)