Trabajar con JSON en SQL

JSON significa Notación de objetos Javascript . Se utiliza principalmente en el almacenamiento y transporte de datos. La mayoría de las bases de datos NoSQL como MongoDB, CouchDB, etc., utilizan datos en formato JSON. Siempre que los datos de un servidor deban transferirse a una página web, el formato JSON es el formato preferido, ya que las aplicaciones front-end como Android, iOS, React o Angular, etc., pueden analizar los contenidos JSON y mostrarlos según convenga. Incluso en SQL, podemos enviar datos JSON y almacenarlos fácilmente en filas. Veamos uno por uno.

Usemos Azure Data Studio para SQL Server. También vamos a crear la base de datos y tener una tabla en su lugar. Entonces puede proceder para JSON. Azure Data Studio funciona bien para entornos Windows 10, Mac y Linux. Se puede instalar desde aquí .

Creación de base de datos:

Comando para crear la base de datos. Aquí GEEKSFORGEEKS es el nombre de la base de datos.

CREATE DATABASE GEEKSFORGEEKS;

Para activar la base de datos, use el siguiente comando:

USE GEEKSFORGEEKS;

Creación de tablas con datos:

Ahora vamos a crear una tabla llamada » Autores » e insertemos algunos datos como se muestra a continuación:

CREATE TABLE Authors (
    ID INT IDENTITY NOT NULL PRIMARY KEY,
    AuthorName NVARCHAR(MAX),
    Age INT,
    Skillsets NVARCHAR(MAX),
    NumberOfPosts INT
);
INSERT INTO Authors (AuthorName,Age,Skillsets,NumberOfPosts) VALUES ('Geek',25,'Java,Python,.Net',5);
GO

INSERT INTO Authors (AuthorName,Age,Skillsets,NumberOfPosts) VALUES ('Geek2',22,'Android,Python,.Net',15);
GO
INSERT INTO Authors (AuthorName,Age,Skillsets,NumberOfPosts) VALUES ('Geek3',23,'IOS,GO,R',10);
GO
INSERT INTO Authors (AuthorName,Age,Skillsets,NumberOfPosts) VALUES ('Geek4',24,'Java,Python,GO',5);
GO

JSON es una hermosa opción para conectar NoSQL y los mundos relacionales. Por lo tanto, en caso de que haya exportado los datos de MongoDB y necesite importarlos en SQL Server, podemos seguir los siguientes enfoques

Los documentos JSON se pueden almacenar tal cual en columnas NVARCHAR, ya sea en formato de almacenamiento LOB o en formato de almacenamiento relacional. Los documentos JSON sin formato deben analizarse y pueden contener texto que no esté en inglés. Al usar el tipo de datos nvarchar(max), podemos almacenar documentos JSON con una capacidad máxima de 2 GB de tamaño. Si los datos JSON no son enormes, podemos optar por NVARCHAR(4000), o bien podemos optar por NVARCHAR(max) por motivos de rendimiento.

Compatibilidad con funciones cruzadas:

La razón principal para mantener el documento JSON en formato NVARCHAR es la compatibilidad entre funciones. NVARCHAR funciona con la función X, es decir, todos los componentes del servidor SQL, como Hekaton (OLTP), temporales o tablas de almacenamiento de columnas, etc. Como el comportamiento de JSON también es así, se representa como tipo de datos NVARCHAR.

Migración:

Antes de SQL Server 2016, JSON se almacenaba en la base de datos como texto. Por lo tanto, era necesario cambiar el esquema de la base de datos y la migración se produjo como tipo JSON en formato NVarchar.

Soporte del lado del cliente:

JSON simplemente se trata como un objeto en JavaScript y, por lo tanto, se denomina notación de objetos de Javascript. No hay un tipo de objeto JSON estandarizado específico en el lado del cliente disponible similar al objeto XmlDom.

Veamos las funcionalidades importantes disponibles en SQL Server que se pueden usar con datos JSON.

Ejemplo de datos JSON:

{
"Information": 
  {"SchoolDetails": 
     [{"Name": "VidhyaMandhir"}, {"Name": "Chettinad"}, {"Name":"PSSenior"}]
  }
}

1. ISJSON ( string JSON ):

Esta función se usa para verificar si la string json de entrada dada está en formato JSON o no . Si está en formato JSON, devuelve 1 como salida o 0, es decir, devuelve 1 o 0 en formato INT.

SELECT ISJSON(@JSONData) AS VALIDJSON

2. JSON_VALUE ( string JSON , ruta):

 La salida será un valor escalar de la string JSON dada. Se realiza el análisis de la string JSON y hay algunos formatos específicos para proporcionar la ruta. Por ejemplo

• ‘$’: hace referencia al objeto JSON completo
• ‘$.Example1’ – referencia Ejemplo1 en objeto JSON
• ‘$[4]’: hace referencia al cuarto elemento en la array JSON
• ‘$.Example1.Example2[2].Example3’: propiedad anidada de referencia en el objeto JSON

Ejemplo :

SELECT JSON_VALUE(@JSONData,'$.Information.SchoolDetails[0].Name') as SchoolName

3. JSON_QUERY ( string JSON , ruta)

Se utiliza para extraer una array de datos u objetos de la string JSON.

SELECT JSON_QUERY(@JSONData,'$.Information.SchoolDetails')
AS LISTOFSCHOOLS

4. JSON_MODIFY:

Hay una opción llamada «JSON_MODIFY» en la función (Transact-SQL) disponible para actualizar el valor de una propiedad en una string JSON y devolver la string JSON actualizada. Siempre que haya un requisito para cambiar el texto JSON, podemos hacerlo

SET @JSONData= JSON_MODIFY(@JSONData, '$.Information.SchoolDetails[2].Name', 'Adhyapana');
SELECT modifiedJson = @JSONData;

5. PARA JSON: 

Esta función se utiliza para exportar datos de SQL Server en formato JSON. Esta es una función útil para exportar datos SQL a formato JSON. Hay dos opciones disponibles con FOR JSON

• AUTO: como está anidado, el subconjunto JSON se crea en función de la jerarquía de la tabla.
• PATH:  Al usar esto podemos definir la estructura de JSON de forma personalizada.

SELECT * FROM Authors FOR JSON AUTO;

SELECT * FROM Authors FOR JSON AUTO, ROOT ('AuthorInfo')

6. OPENJSON:

Esta función se usa para importar JSON como datos de string. Podemos importar JSON como un archivo de texto usando la función OPENROWSET y en eso la opción BULK debe estar habilitada. Devuelve un campo de string única con BulkColumn como nombre de columna.

Ejemplo :

DECLARE @JSON VARCHAR(MAX)
--Syntax to get json data using OPENROWSET  
SELECT @JSON = BulkColumn FROM OPENROWSET  
(BULK '<pathname\jsonfilename with .json extension>', SINGLE_CLOB)  AS j
--To check  json valid or not, we are using this ISJSON
SELECT ISJSON(@JSON)
--If ISJSON is true, then display the json data
If (ISJSON(@JSON)=1)
SELECT @JSON AS 'JSON Text'

Nota: Incluso se pueden colocar datos grandes. Como muestra, mostramos solo una fila.

SINGLE_BLOB, que lee un archivo como varbinary(max). SINGLE_NCLOB, que lee un archivo como nvarchar(max): si el contenido está en texto que no está en inglés, como datos en japonés o chino, etc., debemos seguir este patrón. Usamos SINGLE_CLOB, que lee un archivo como varchar(max).

Generará una tabla relacional con su contenido a partir de la string JSON. Se crea cada fila que se puede obtener iterando a través de los elementos del objeto JSON, OPENJSON se puede usar para analizar el JSON como texto. Pongamos un JSON en un archivo externo y su contenido sea

Select * FROM OPENJSON (@JSON)

SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK '<location of json file>', SINGLE_CLOB) 
AS j
--If the retrieved JSON is a valid one
If (ISJSON(@JSON)=1)
Select * FROM OPENJSON (@JSON)

Podemos ver que para la clave «Strings» como » authorname » y «skills» se obtuvo el tipo 1 y la clave «int» como «id» y «age» se obtuvo el tipo 2. De manera similar, para boolean, el tipo es 3. Para arrays, es 4 y para objeto, es 5. OPENJSON analiza solo el nivel raíz de JSON.

En caso de que el JSON esté anidado, necesitamos usar variables de ruta

Select * FROM OPENJSON (@JSON,  '$.skills')

Incluso podemos hacer los conjuntos de habilidades como columnas de datos como

SELECT * FROM OPENJSON (@JSON, '$.skills')  
WITH ( skill1 VARCHAR(25), skill2 VARCHAR(25), skill3 VARCHAR(25) )

Guardar el conjunto de filas en la tabla: aquí el número de columnas debe coincidir con el recuento que está presente en el interior con:

SELECT <col1>,<col2>,.... INTO <tablename>  FROM OPENJSON (@JSON, '$.skills')  
WITH (skill1 VARCHAR(25),
skill2 VARCHAR(25),
skill3 VARCHAR(25)
)

Cambio de valores JSON:

Hay una opción llamada «JSON_MODIFY» en la función (Transact-SQL) disponible para actualizar el valor de una propiedad en una string JSON y devolver la string JSON actualizada. Siempre que haya un requisito para cambiar el texto JSON, podemos hacerlo

DECLARE @json NVARCHAR(MAX);
SET @json = '{"Information": {"SchoolDetails": [{"Name": "VidhyaMandhir"}, {"Name": "Chettinad"}, {"Name":"PSSenior"}]}}';
SET @json = JSON_MODIFY(@json, '$.Information.SchoolDetails[2].Name', 'Adhyapana');
SELECT modifiedJson = @json;

Conclusión :

Los datos JSON son muy necesarios hoy en día y se requieren mucho para almacenar y transportar datos a través de muchos servidores y todo el software los utiliza para muchos propósitos útiles. Todas las llamadas a la API REST proporcionan JSON como medio de salida y en SQL Server, hemos visto cómo usarlos.