SQL Server 2016 introduce soporte incorporado para almacenar, administrar y procesar datos JSON. En este artículo, exploraremos cómo trabajar con datos JSON en SQL Server utilizando la función OPENJSON y otras funciones recientemente introducidas.

Importar datos JSON como datos tabulares

SQL Server 2016 incluye la función OPENJSON de conjunto de filas con valores de tabla, que le permite escanear datos JSON y convertirlos a un formato tabular. Esto resulta útil cuando desea almacenar datos JSON en un formato relacional, realizar informes sobre datos tabulares o pasar datos tabulares (convertidos de JSON) a aplicaciones que esperan datos tabulares.

Hay dos formas de llamar a la función OPENJSON:

• Llamar a OPENJSON con esquema predeterminado: esto devuelve una tabla con tres columnas: Key, Value y Type. La columna Key representa el nombre de la propiedad o el índice del elemento, la columna Value representa el valor de la propiedad o el elemento, y la columna Type representa el tipo de datos JSON del valor.
• Llamar a OPENJSON con un esquema explícito: esto le permite especificar las columnas de salida, sus tipos de datos y las rutas de las propiedades de origen para cada columna que se devuelve.

Aquí hay un ejemplo de llamar a OPENJSON con esquema predeterminado:

DECLARE @JSONText NVARCHAR(MAX)
SET @JSONText = N'{ "Order":{ "OrderID":43663, "Status":5, "PONumber":"PO18009186470" }, "Product":{ "ShipDate":"2011-06-07T00:00:00", "ProductID":760 } }'

SELECT * FROM OPENJSON(@JSONText);
SELECT * FROM OPENJSON(@JSONText, '$.Order');
SELECT * FROM OPENJSON(@JSONText, '$.Product');

Y aquí hay un ejemplo de llamar a OPENJSON con un esquema explícito:

DECLARE @JSONText NVARCHAR(MAX)
SET @JSONText = N'{ "Order":{ "SalesOrderID":43663, "Status":5, "PurchaseOrderNumber":"PO18009186470" }, "Product":{ "ShipDate":"2011-06-07T00:00:00", "ProductID":760 } }'

SELECT * FROM OPENJSON(@JSONText, '$') WITH (
  OrderID INT '$.Order.SalesOrderID',
  ShipmentStatus INT '$.Order.Status',
  PONumber NVARCHAR(25) '$.Order.PurchaseOrderNumber',
  ShipmentDate DATE '$.Product.ShipDate',
  ProductID INT '$.Product.ProductID'
);

Otras funciones JSON disponibles en SQL Server 2016

Además de la función OPENJSON, SQL Server 2016 incluye otras tres funciones para trabajar con datos JSON:

• ISJSON: esta función verifica si los datos JSON de entrada son válidos y devuelve 1 si lo son, o 0 si no lo son.
• JSON_VALUE: esta función extrae un valor escalar de los datos JSON según la ruta especificada.
• JSON_QUERY: esta función devuelve un objeto o una matriz de los datos JSON según la ruta especificada.

Aquí hay un ejemplo de uso de la función JSON_VALUE:

DECLARE @JSONText NVARCHAR(MAX)
SET @JSONText = N'{ "Order":{ "OrderID":43663, "Status":5, "PONumber":"PO18009186470" }, "Product":{ "ShipDate":"2011-06-07T00:00:00", "ProductID":760 } }'

SELECT JSON_VALUE(@JSONText, '$.Order.PONumber');

Y aquí hay un ejemplo de uso de la función JSON_QUERY:

DECLARE @JSONText NVARCHAR(MAX)
SET @JSONText = N'{ "Order":{ "OrderID":43663, "Status":5, "PONumber":"PO18009186470" }, "Product":{ "ShipDate":"2011-06-07T00:00:00", "ProductID":760 } }'

SELECT JSON_QUERY(@JSONText, '$.Product');

Índices en datos JSON

A diferencia de XML, SQL Server 2016 no tiene índices JSON personalizados. Sin embargo, puede crear índices estándar en datos JSON para optimizar el rendimiento. Por ejemplo, puede crear una columna calculada no persistente basada en valores de una propiedad en el documento JSON y luego crear los índices requeridos en ella.

Cuando trabaje con las funciones mencionadas anteriormente, puede especificar la ruta en el documento JSON en dos modos: laxo y estricto. El modo laxo, que es el predeterminado, devuelve NULL si la ruta especificada no existe. El modo estricto genera un error si la ruta no está disponible.

Estas características y funciones discutidas en este artículo se basan en SQL Server 2016 CTP 3.2 y pueden cambiar en futuras versiones.

En conclusión, SQL Server 2016 proporciona soporte nativo para almacenar, administrar y analizar datos JSON. La función OPENJSON y otras funciones JSON le permiten trabajar con datos JSON en un formato tabular, extraer valores escalares y devolver objetos o matrices. Al aprovechar los índices, puede optimizar el rendimiento de las consultas de datos JSON.

Recursos:

• Datos JSON en SQL Server 2016
• Función OPENJSON
• Función ISJSON
• Función JSON_VALUE
• Función JSON_QUERY
• Indexación de datos JSON
• Métodos de tipo de datos JSON