Importar JSON en SQL Server

El formato de archivo JSON es cada vez más popular para intercambiar datos. Este formato no son archivos de texto, por lo que se pueden utilizar en prácticamente cualquier sistema, además de ser fáciles de interpretar tanto por los ordenadores como por las personas. Por lo que es probable que en algún momento necesitemos importar este formato de archivo en una base de datos. A partir de SQL Server 2017 esta tarea ya es posible sin ninguna herramienta adicional. Lo que hace realmente sencillo importar JSON en SQL Server.

A continuación, se importará un archivo JSON obtenido de Mockaroo con datos generados aleatoriamente.

Leer un archivo JSON en SQL Server

La función de SQL Server con la que se pueden importar directamente archivos de texto es OPENROWSET. Al ejecutar esta con la opción BULK se obtiene un campo de cadena de texto con el nombre BulkColumn. Lo que se puede asignar a una variable como se muestra a continuación.

DECLARE @JSON VARCHAR(MAX)

SELECT @JSON = BulkColumn FROM
	OPENROWSET(BULK 'C:\Users\daniel\Downloads\MOCK_DATA.json', SINGLE_BLOB) JSON;

Como se ve en el ejemplo es necesario indicar a la función OPENROWSET el tipo de data que se desea importar. Ahora en @JSON tenemos el contenido del archivo JSON. Para validar que este sea un archivo JSON válido se puede usar la función ISJSON que devolverá 1 en caso afirmativo. Así se puede hacer una consulta para ver el contenido si este es válido.

En Analytics Lane

Nuevo test de normalidad interactivo en el laboratorio de Analytics Lane

IF (ISJSON(@JSON) = 1)
	SELECT @JSON

[{"id":1,"first_name":"Margy","last_name":"Bonni…

En donde se observa el contendió del archivo.

Procesar el archivo JSON

Ahora que se dispone del contenido del archivo en una variable es posible procesar este con la función OPENJSON. Lo que dará una tabla con los valores

SELECT * FROM OPENJSON (@JSON) 

En donde se tiene la clave, el valor y el tipo de dato. Los posibles valores para el tipo de datos son:

Cargar los datos en una tabla con WITH

En este punto ya tenemos los datos, pero lo que necesitamos es asignar cada uno de los pares clave-valor a una tabla. Para lo que se puede usar la cláusula WITH. Al combinar OPENROWSET con WITH permite especificar el tipo de dato y los nombres de las columnas. Así para los datos se puede procesar de la siguiente manera.

SELECT * FROM OPENJSON(@JSON)
	WITH (
		id int,
		first_name varchar(20),
		last_name varchar(20),
		email varchar(50),
		gender varchar(6),
		ip_address varchar(14))

Los cuales ya se puede importar en una tabla de la base de datos.

Acceder a datos internos del JSON

Por defecto solamente se analiza el nivel raíz del JSON, lo que puede ser útil en archivos como el que hemos importado. Sin embargo, es habitual encontrar archivos JSON en los que los datos se encuentran anidados. Por ejemplo, un archivo que devuelva información adicional además de los datos.

{
  "ok": true,
  "date": "2019-11-11",
  "data": [
  	// Los datos se encuentran aquí
  ]
}

En estas ocasiones la función OPENROWSET admite un segundo parámetro opcional con el que se puede indicar dónde se encuentran los datos que se desean importar. Al utilizar esta opción se obtendrán solamente los datos en esa ruta. Para el JSON del ejemplo anterior se tendría que indicar que los datos se encuentran en data.

SELECT * FROM OPENJSON(@JSON, '$.data')
	WITH (
		id int,
		first_name varchar(20),
		last_name varchar(20),
		email varchar(50),
		gender varchar(6),
		ip_address varchar(14))

Conclusiones

Ya hemos visto en otras ocasiones como trabajar con archivos JSON desde Python o Matlab. En esta entrada se ha visto cómo importar archivos JSON en SQL Server. Dada la popularidad de este formato, posiblemente esto sea algo que tengamos que hacer de ahora en adelante de forma habitual.

Imágenes: Pixabay (John_Ioannidis)