Formatear los resultados de consulta como JSON con FOR JSON

Aplica a: SQL Server 2016 (13.x) y versiones posteriores y Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (solo para SQL pool sin servidor)punto final de SQL Analytics en Microsoft FabricAlmacén en Microsoft FabricBase de datos SQL en Microsoft Fabric

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.

Note

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.

En Fabric Data Warehouse, FOR JSON debe ser el último operador de la consulta y, por tanto, no se permite dentro de subconsultas.

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 contenedor 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 obtener más información y ejemplos, vea Formato de salida JSON anidada con el modo PATH.

Para obtener información sobre la sintaxis y el uso, vea SELECT - FOR Clause (Transact-SQL).

FOR JSON AUTO

Salida de controles de instrucción SELECT con FOR JSON AUTO

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

El siguiente ejemplo de SELECT...FOR JSON AUTO incluye una visualización del aspecto de los resultados JSON cuando existe una relación 1:many entre los datos de las tablas unidas.

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 obtener información y ejemplos más detallados, consulte Formato de salida JSON automáticamente con el modo AUTO.

Para obtener información sobre la sintaxis y el uso, vea SELECT - FOR Clause (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.

• 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 más información, consulte Quitar corchetes de la opción JSON: 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. Los nombres de las columnas y sus valores se escapan según la sintaxis JSON. Para obtener más información, consulta Cómo FOR JSON inserta caracteres de escape en los caracteres especiales y caracteres de control.

Example

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

• Cómo FOR JSON convierte los tipos de datos de SQL Server en tipos de datos JSON
• Cómo FOR JSON inserta caracteres de escape en los caracteres especiales y caracteres de control
• Vídeo: JSON como puente entre NoSQL y mundos relacionales
• SELECT: cláusula FOR (Transact-SQL)
• Uso de la salida FOR JSON en el motor de base de datos de SQL y en aplicaciones cliente