Compartir a través de

Introducción a las características JSON

Se aplica a:Azure SQL DatabaseAzure SQL Managed InstanceBase de datos SQL en Fabric

Azure SQL Database, SQL Database en Microsoft Fabric e Instancia administrada de Azure SQL permiten analizar y consultar los datos representados en formato de notación de objetos JavaScript (JSON) y exportar los datos relacionales como texto JSON. Los escenarios JSON siguientes están disponibles:

Formato de datos relacionales en formato JSON

Si tiene un servicio web que toma datos del nivel de base de datos y proporciona una respuesta en formato JSON o marcos JavaScript del lado cliente o bibliotecas que aceptan datos con formato JSON, puede dar formato JSON al contenido de la base de datos directamente en una consulta SQL. Ya no tiene que escribir código de aplicación que dé formato a los resultados como JSON o incluya alguna biblioteca de serialización JSON para convertir los resultados de la consulta tabular y, a continuación, serializar objetos en formato JSON. En su lugar, puede usar la cláusula para dar formato a los resultados de la FOR JSON consulta SQL como JSON y usarlo directamente en la aplicación.

En el ejemplo siguiente, se aplica el formato JSON a las filas de la tabla Sales.Customer mediante la cláusula FOR de JSON:

select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH

La FOR JSON PATH cláusula da formato a los resultados de la consulta como texto JSON. Los nombres de columna se utilizan como claves, mientras que los valores de celda se generan como valores JSON:

[
{"CustomerName":"Eric Torres","PhoneNumber":"(307) 555-0100","FaxNumber":"(307) 555-0101"},
{"CustomerName":"Cosmina Vlad","PhoneNumber":"(505) 555-0100","FaxNumber":"(505) 555-0101"},
{"CustomerName":"Bala Dixit","PhoneNumber":"(209) 555-0100","FaxNumber":"(209) 555-0101"}
]

Se da al conjunto de resultados formato de matriz JSON, donde cada fila tiene formato de objeto JSON independiente.

PATH indica que puede personalizar el formato de salida del resultado JSON mediante la notación de puntos en alias de columna. La consulta siguiente cambia el nombre de la CustomerName clave en el formato JSON de salida y coloca los números de teléfono y fax en el Contact subobjeto:

select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

La salida de esta consulta tiene el siguiente aspecto:

{
    "Name":"Nada Jovanovic",
    "Contact":{
           "Phone":"(215) 555-0100",
           "Fax":"(215) 555-0101"
    }
}

En este ejemplo, se devuelve un único objeto JSON en lugar de una matriz al especificarse la opción WITHOUT_ARRAY_WRAPPER. Puede usar esta opción si sabe que va a devolver un solo objeto como resultado de la consulta.

El valor principal de la FOR JSON cláusula es que permite devolver datos jerárquicos complejos de la base de datos con formato como objetos JSON anidados o matrices. En el ejemplo siguiente se muestra cómo incluir las filas de la tabla Orders que pertenecen a Customer como matriz anidada de Orders:

select CustomerName as Name, PhoneNumber as Phone, FaxNumber as Fax,
        Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
    join Sales.Orders Orders
        on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER

En lugar de enviar consultas independientes para obtener Customer datos y, a continuación, capturar una lista de relacionados Orders, puede obtener todos los datos necesarios con una sola consulta, como se muestra en la siguiente salida de ejemplo:

{
  "Name":"Nada Jovanovic",
  "Phone":"(215) 555-0100",
  "Fax":"(215) 555-0101",
  "Orders":[
    {"OrderID":382,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":395,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":1657,"OrderDate":"2013-01-31","ExpectedDeliveryDate":"2013-02-01"}
  ]
}

Trabajar con datos JSON

Si no tiene datos con una estructura estricta, si tiene subobjetos complejos, matrices o datos jerárquicos, o si las estructuras de datos evolucionan con el tiempo, el formato JSON puede ayudar a representar cualquier estructura de datos compleja.

JSON es un formato de texto que se puede usar como cualquier otro tipo de cadena. Puede enviar o almacenar datos JSON como nvarchar estándar:

CREATE TABLE Products (
  Id int identity primary key,
  Title nvarchar(200),
  Data nvarchar(max)
)
go
CREATE PROCEDURE InsertProduct(@title nvarchar(200), @json nvarchar(max))
AS BEGIN
    insert into Products(Title, Data)
    values(@title, @json)
END

Los datos JSON usados en este ejemplo se representan mediante el tipo nvarchar(MAX). Se puede insertar JSON en esta tabla o proporcionarlo como argumento del procedimiento almacenado mediante sintaxis estándar de Transact-SQL, tal como se muestra en el ejemplo siguiente:

EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'

Cualquier lenguaje del lado cliente o biblioteca que funcione con datos de cadena también funciona con datos JSON. JSON se puede almacenar en cualquier tabla que admita el tipo nvarchar, como una tabla optimizada para memoria o una tabla con versiones del sistema. JSON no introduce ninguna restricción en el código del lado cliente ni en el nivel de base de datos.

Consulta de datos JSON

Si tiene datos con formato JSON almacenados en tablas, las funciones JSON le permiten usar estos datos en cualquier consulta SQL.

Funciones JSON permiten tratar los datos con formato JSON como cualquier otro tipo de datos SQL. Puede extraer fácilmente valores del texto JSON y usar datos JSON en cualquier consulta:

select Id, Title, JSON_VALUE(Data, '$.Color'), JSON_QUERY(Data, '$.tags')
from Products
where JSON_VALUE(Data, '$.Color') = 'White'

update Products
set Data = JSON_MODIFY(Data, '$.Price', 60)
where Id = 1

La JSON_VALUE función extrae un valor del texto JSON almacenado en la columna Datos. Esta función usa una ruta de acceso de estilo JavaScript para hacer referencia a un valor en el texto JSON para extraerlo. El valor extraído se puede usar en cualquier parte de la consulta SQL.

La JSON_QUERY función es similar a JSON_VALUE. A diferencia de JSON_VALUE, esta función extrae subobjetos complejos, como matrices u objetos colocados en el texto JSON.

La JSON_MODIFY función le permite especificar la ruta de acceso del valor en el texto JSON que se debe actualizar, así como un nuevo valor que sobrescribirá el anterior. De esta forma, puede actualizar fácilmente el texto JSON sin volver a analizar la estructura completa.

Dado que JSON se almacena en texto estándar, no existe ninguna garantía de que los valores almacenados en las columnas de texto tengan el formato correcto. Puede comprobar que el texto almacenado en la columna JSON tenga un formato correcto con las restricciones estándar y la función ISJSON:

ALTER TABLE Products
    ADD CONSTRAINT [Data should be formatted as JSON]
        CHECK (ISJSON(Data) > 0)

Si el texto de entrada tiene el formato JSON correcto, la ISJSON función devuelve el valor 1. Con cada inserción o actualización de la columna JSON, esta restricción comprobará que el nuevo valor de texto no tenga un formato JSON incorrecto.

Transformación de JSON en formato tabular

Puede transformar colecciones JSON en formato tabular y cargar o consultar datos JSON.

OPENJSON es una función T-SQL de valor de tabla que analiza el texto JSON, busca una matriz de objetos JSON, recorre en iteración los elementos de la matriz y devuelve una fila en el resultado de salida para cada elemento de la matriz.

Capturas de pantalla y fragmentos de código de datos tabulares JSON de ejemplo.

En el ejemplo, podemos especificar dónde buscar la matriz JSON que se debe abrir (en la $.Orders ruta de acceso), qué columnas se deben devolver como resultado y dónde buscar los valores JSON que se devolverán como celdas.

Se puede transformar una matriz JSON de la variable @orders en un conjunto de filas, analizar este conjunto de resultados o insertar filas en una tabla estándar:

CREATE PROCEDURE InsertOrders(@orders nvarchar(max))
AS BEGIN

    insert into Orders(Number, Date, Customer, Quantity)
    select Number, Date, Customer, Quantity
    FROM OPENJSON (@orders)
     WITH (
            Number varchar(200),
            Date datetime,
            Customer varchar(200),
            Quantity int
     )
END

La colección de pedidos con formato de matriz JSON que se proporciona como parámetro del procedimiento almacenado se puede analizar e insertar en la tabla Orders.

Contenido relacionado