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.
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"
}
}
]
}
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
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'
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'
Un saludo!
No hay comentarios:
Publicar un comentario