martes, 30 de mayo de 2017

JSON en SQL Server 2016

Seguro que ya estabas enterado, pero en mi caso ha sido recientemente cuando he descubierto que a partir de SQL Server 2016 se puede trabajar con JSON.

Aunque hay una excelente documentación al respecto en JSON Data (SQL Server), de una forma resumida y con ejemplos que pueda recordar fácilmente, me gustaría contarte que posibilidades tenemos para trabajar con JSON en SQL Server.

Lo primero es que no hay un tipo json, en realidad trabajaremos con nvarchar y todo la magia ocurrirá a través de nuevas clausulas y funciones.

Para organizar el post, voy a plantear un escenario donde, primero importaremos datos desde un fichero .json, a continuación formatearemos en JSON la salida de una consulta SQL, para después hacer consultas SQL sobre una columna que guarda JSON y acabar, finalmente, con un consejo sobre índices para mejorar el rendimiento.

El script SQL necesario para todas las pruebas es el siguiente:

CREATE TABLE [dbo].[OrderLines](
	[Id] [int] NOT NULL,
	[Units] [int] NOT NULL,
	[Price] [decimal](18, 2) NOT NULL,
	[ProductId] [int] NOT NULL,
	[OrderId] [int] NOT NULL
 CONSTRAINT [PK_dbo.OrderLines] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))
GO
CREATE TABLE [dbo].[Orders](
	[Id] [int] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[Comment] [nvarchar](250) NULL
 CONSTRAINT [PK_dbo.Orders] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))
GO
CREATE TABLE [dbo].[Products](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](250) NULL
 CONSTRAINT [PK_dbo.Products] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))
GO
ALTER TABLE [dbo].[OrderLines]  WITH CHECK ADD  CONSTRAINT [FK_dbo.OrderLines_dbo.Orders_OrderId] FOREIGN KEY([OrderId])
REFERENCES [dbo].[Orders] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[OrderLines] CHECK CONSTRAINT [FK_dbo.OrderLines_dbo.Orders_OrderId]
GO
ALTER TABLE [dbo].[OrderLines]  WITH CHECK ADD  CONSTRAINT [FK_dbo.OrderLines_dbo.Products_ProductId] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Products] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[OrderLines] CHECK CONSTRAINT [FK_dbo.OrderLines_dbo.Products_ProductId]

Para importar datos desde un fichero .json podemos usar la función OPENROWSET con el parámetro SINGLE_CLOB, que lee y devuelve su contenido como una única fila y columna BulkColumn de tipo nvarchar(max). Además, tendremos que usar la función OPENJSON que convierte JSON en filas y columnas.

Nuestro fichero .json será como sigue

[
  {
    "Id": 1,
    "CreatedDate": "2017-05-29T00:00:00.000",
    "OrderLines": [
      {
        "Id": 1,
        "Units": 1,
        "Price": 1.25,
        "Product": {
          "Id": 1,
          "Name": "Product 1"
        }
      },
      {
        "Id": 2,
        "Units": 2,
        "Price": 2.5,
        "Product": {
          "Id": 2,
          "Name": "Product 2"
        }
      }
    ]
  },
  {
    "Id": 2,
    "CreatedDate": "2017-05-29T00:00:00.000",
    "Comment": "A brief but useful comment",
    "OrderLines": [
      {
        "Id": 3,
        "Units": 3,
        "Price": 3.75,
        "Product": {
          "Id": 1,
          "Name": "Product 1"
        }
      }
    ]
  }
]

Ahora podemos usar OPENROWSET y OPENJSON

SELECT BulkColumn, [key], [value], [type]
FROM OPENROWSET (BULK 'C:\panicoenlaxbox\data.json', SINGLE_CLOB) AS T
CROSS APPLY OPENJSON(BulkColumn)

OPENROWSET devuelve la columna BulkColumn, OPENJSON devuelve las columnas key, value y type.

image

Si en fichero .json tuviera un sólo objeto en vez de un array, la salida nos ayudaría a entender mejor como funciona OPENJSON

{
  "Id": 1,
  "CreatedDate": "2017-05-29T00:00:00.000",
  "OrderLines": [
    {
      "Id": 1,
      "Units": 1,
      "Price": 1.25,
      "Product": {
        "Id": 1,
        "Name": "Product 1"
      }
    },
    {
      "Id": 2,
      "Units": 2,
      "Price": 2.5,
      "Product": {
        "Id": 2,
        "Name": "Product 2"
      }
    }
  ]
}

image

OPENJSON tiene la clausula WITH con la que podemos, de forma explícita, establecer la estructura del resultado devuelto. Por ejemplo, para conseguir un conjunto de filas y columnas sobre la que poder trabajar directamente, ejecutaríamos la siguiente consulta

SELECT
	Orders.Id AS OrderId
   ,Orders.CreatedDate
   ,Orders.Comment
   ,OrderLines.Id AS OrderLineId
   ,OrderLines.Units
   ,OrderLines.Price
   ,Product.Id AS ProductId
   ,Product.[Name] AS ProductName
INTO #Table1
FROM OPENROWSET(BULK 'C:\panicoenlaxbox\data.json', SINGLE_CLOB) AS j
CROSS APPLY OPENJSON(BulkColumn)
WITH (
	Id INT,
	CreatedDate DATETIME '$.CreatedDate',
	Comment NVARCHAR(MAX),
	OrderLines NVARCHAR(MAX) AS JSON
) AS Orders
CROSS APPLY OPENJSON(Orders.OrderLines)
WITH (
	Id INT,
	Units INT,
	Price DECIMAL(18, 2),
	Product NVARCHAR(MAX) AS JSON
) AS OrderLines
CROSS APPLY OPENJSON(OrderLines.Product)
WITH (
	Id INT,
	[Name] NVARCHAR(MAX)
) AS Product    

image

Ahora ya sí podemos insertar estos datos leídos del fichero .json en nuestras tablas

INSERT INTO Products SELECT DISTINCT ProductId, ProductName FROM #table1;
INSERT INTO Orders SELECT DISTINCT OrderId, CreatedDate, Comment FROM #table1;
INSERT INTO OrderLines SELECT DISTINCT OrderLineId, Units, Price, ProductId, OrderId FROM #table1;    

Si hablamos ahora de formatear consultas, tendremos que usar la clausula FOR JSON

SELECT * FROM Orders O
INNER JOIN OrderLines OL ON OL.OrderId = O.Id
INNER JOIN Products P ON OL.ProductId = P.Id
FOR JSON AUTO

Que devuelve

    [{
    "Id": 1,
    "CreatedDate": "2017-05-29T00:00:00",
    "OL": [{
        "Id": 1,
        "Units": 1,
        "Price": 1.25,
        "ProductId": 1,
        "OrderId": 1,
        "P": [{
            "Id": 1,
            "Name": "Product 1"
        }]
    }, {
        "Id": 2,
        "Units": 2,
        "Price": 2.50,
        "ProductId": 2,
        "OrderId": 1,
        "P": [{
            "Id": 2,
            "Name": "Product 2"
        }]
    }]
}, {
    "Id": 2,
    "CreatedDate": "2017-05-29T00:00:00",
    "Comment": "A brief but useful comment",
    "OL": [{
        "Id": 3,
        "Units": 3,
        "Price": 3.75,
        "ProductId": 1,
        "OrderId": 2,
        "P": [{
            "Id": 1,
            "Name": "Product 1"
        }]
    }]
}]

Como probablemente este resultado no nos satisfaga, tendremos que tomar el control con FOR JSON PATH. Por ejemplo, la siguiente consulta devuelve exactamente lo mismo que tiene el fichero .json que usamos al comienzo para importar los datos

SELECT
	O.Id
   ,O.CreatedDate
   ,O.Comment
   ,(SELECT
			OL.Id
		   ,OL.Units
		   ,OL.Price
		   ,OL.ProductId AS 'Product.Id'
		   ,P.[Name] AS 'Product.Name'
		FROM OrderLines OL
		INNER JOIN Products P
			ON OL.ProductId = P.Id
		WHERE OL.OrderId = O.Id
		FOR JSON PATH)
	AS OrderLines
FROM Orders O
FOR JSON PATH

En cuanto a que podemos hacer para consultar datos JSON almacenados en una columna, encontramos varias funciones:

  • ISJSON
  • JSON_VALUE
  • JSON_QUERY
  • JSON_MODIFY

ISJSON valida que el texto es JSON válido

JSON_VALUE extrae un valor desde JSON

JSON_QUERY extrae como texto, un objeto u array desde JSON

JSON_MODIFY permite modificar JSON y devuelve el resultado

Lo más sencillo será agregar una nueva columna para poder jugar con ella

ALTER TABLE Orders
ADD SecurityContext NVARCHAR(MAX);
GO
UPDATE Orders SET SecurityContext = '{"Enabled":true,"Roles":["Salesman","Customer"],"Worflows":[{"Name":"Approval","Priority":1},{"Name":"Rejection","Priority":2}]}'
WHERE Id = 1;
UPDATE Orders SET SecurityContext = '{"Enabled":false}'
WHERE Id = 2;    

Ahora podemos ejecutar las siguientes consultas

--ISJSON valida si es JSON
SELECT ISJSON(SecurityContext) FROM Orders;

--JSON_VALUE extrae un valor desde JSON
--'$.Roles[1]' y 'lax $.Roles[1]' son lo mismo, por defecto es lax
SELECT JSON_VALUE(SecurityContext, '$.Roles[1]') FROM Orders;

--Con strict tendremos una excepción porque el segundo registro no tiene valor
--Property cannot be found on the specified JSON path.
--SELECT JSON_VALUE(SecurityContext, 'strict $.Roles[1]') FROM Orders;

--JSON_QUERY devuelve un objeto o un array
SELECT JSON_QUERY(SecurityContext, '$.Worflows') FROM Orders;
--[{"Name":"Approval","Priority":1},{"Name":"Rejection","Priority":2}]

DECLARE @json NVARCHAR(MAX)
SELECT @json = SecurityContext FROM Orders WHERE Id = 1;
--Modificar una propiedad
SELECT JSON_VALUE(JSON_MODIFY(@json, '$.Enabled', 'false'), '$.Enabled');
--Modificar un elemento de un array
SELECT JSON_QUERY(JSON_MODIFY(@json, '$.Roles[1]', 'Administrador'), '$.Roles');
--["Salesman","Administrador"]

--Agregar un elemento a un array
SELECT JSON_QUERY(JSON_MODIFY(@json, 'append $.Roles', 'Agent'), '$.Roles');
--["Salesman","Customer","Agent"]

Por último, sólo mencionar como podemos crear un índice para que JSON_VALUE lo use. Primero veremos el plan de ejecución de una consulta sin el índice y después como cambia cuando lo incluimos

SELECT JSON_VALUE(SecurityContext, '$.Enabled') FROM Orders 
WHERE JSON_VALUE(SecurityContext, '$.Enabled') = 'true'

image

Ahora agregamos el índice (bueno, en realidad agregamos una columna virtual y después el índice) y ejecutamos de nuevo la consulta para confirmar que lo está usando

ALTER TABLE Orders
ADD SecurityContextEnabled AS JSON_VALUE(SecurityContext,'$.Enabled')
GO
CREATE INDEX IX_Orders_Enabled
ON Orders(SecurityContextEnabled)  
GO
SELECT JSON_VALUE(SecurityContext, '$.Enabled') FROM Orders 
WHERE JSON_VALUE(SecurityContext, '$.Enabled') = 'true'

image

Un saludo!