¿Cómo importar datos JSON a SQL Server?

JSON se refiere a la notación de objetos Javascript. Es un formato de datos de texto popular que se utiliza para intercambiar datos en aplicaciones web y móviles modernas. Se basa en un subconjunto del lenguaje de programación Javascript. Se utiliza para almacenar datos aleatorios en archivos de registro o NoSQL.

También nos permite integrar NoSQL y conceptos relacionados en la misma base de datos. 

Ventaja de los datos JSON:

• Formato ligero de intercambio de datos.
• Fácil de leer y escribir para los humanos.
• Fácil de analizar y generar para las máquinas.

 Datos JSON usando funciones y operadores integrados:

• Podemos analizar el texto JSON y leer o modificar los valores.
• Transforma objetos JSON en formato de tabla.
• Podemos ejecutar cualquier consulta Transact-SQL en los objetos JSON convertidos.
• Transforme los resultados de las consultas de Transact-SQL de nuevo al formato JSON.

Función	Descripción
ISJSON	Comprueba si una string contiene datos JSON o no
JSON_VALUE	Extrae un valor escalar de una string JSON.
JSON_QUERY	Extrae un objeto o una array de una string JSON.
JSON_MODIFY	Cambia un valor en una string JSON.

 

Ahora, para importar datos JSON en el servidor SQL, usaremos OPENROWSET (BULK). 

Paso 1: uso de OPENROWSET (BULK) 

Es una función con valores de tabla que puede leer datos de cualquier archivo. Devuelve una tabla con una sola columna que contiene todo el contenido del archivo. Simplemente puede cargar todo el contenido de un archivo como un valor de texto. (Este único valor grande se conoce como objeto grande de un solo carácter o SINGLE_CLOB).

Sintaxis: 

SELECT * FROM OPENROWSET (BULK 'file_path', SINGLE_CLOB) as correlation_name;

Lee el contenido del archivo y lo devuelve en BulkColumn. Se debe especificar el nombre de la correlación. Tenemos el archivo JSON llamado «file1.json». Contenido del archivo JSON:

Consulta:

[{"Firstname": "ROMY", "Lastname": "KUMARI", "Gender": "female", "AGE" : 22 },
{"Firstname": "PUSHKAR", "Lastname": "JHA", "Gender": "male", "AGE" : 22 },
{"Firstname": "SHALINI", "Lastname": "JHA", "Gender": "female", "AGE" : 21 },
{"Firstname": "SAMBHAVI", "Lastname": "JHA", "Gender": "female", "AGE" : 18 } ]

Paso 2: Importe file1.json en el servidor SQL

Consulta:

SELECT * FROM OPENROWSET (BULK 'E:\file1.json', Single_CLOB) AS import;

Producción:

Todo el contenido se devuelve como una sola columna.

Paso 3: convertir datos JSON

• Declarar una variable.
• Almacene datos de Bulkcolumn en una variable.
• Use la función OPENJSON() para convertir la salida JSON de una variable a un formato tabular.
• Cláusula WITH junto con la definición de la columna (la clave debe usarse como nombre de la columna).

Consulta:

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'E:\file1.json', SINGLE_CLOB) import
SELECT * FROM OPENJSON (@JSON)
WITH  (
   [Firstname] varchar(20),  
   [Lastname] varchar(20),  
   [Gender] varchar(20),  
   [AGE] int );

Producción: