2 formas de leer convertir un Json a tabla de sql server 2022

Importar datos un archivo JSON en una tabla de SQL Server, te permitira trabajar con group by , order by incluso con join puesto que debido a lo ligero del archivo texto plano por demas decir podras extaer la informacion capturada y procesarla de forma agil en Sql server 2016.

En este articulo explicaremos que hace la funcion openjson ademas como podras extraer la misma informacion con una funcion personalizadas para version sql server 2014 o sql server 2012.

Índice

Json sql server como funciona ?

Con la funcion openjson y for json tendremos la posibilidad de nos solo leer un json desde sql server sino que podremos dar formatos a la información extraidas desde el archivo. OPENJSON es una herramienta muy útil para trabajar con datos JSON en SQL Server y es una función muy versátil que te permite realizar una amplia gama de tareas relacionadas con JSON.

Cuando debo usar OpenJson o for json ?

Hay varios escenarios en los que puedes usar la función OPENJSON de SQL Server:

Importar datos JSON en una tabla: Puedes usar OPENJSON para importar datos JSON en una tabla de SQL Server. Con OPENJSON, puedes especificar el formato de los datos JSON y especificar cómo se deben mapear los datos a las columnas de la tabla.
Analizar datos JSON: Puedes usar OPENJSON para analizar datos JSON en una columna y obtener información específica. Por ejemplo, puedes extraer información específica de una columna JSON y colocarla en una tabla temporal.

Modificar datos JSON: Puedes usar OPENJSON para modificar datos JSON en una columna. Por ejemplo, puedes agregar o eliminar valores de un objeto JSON.
Combinar datos: Puedes usar OPENJSON para combinar datos JSON con datos de otras tablas o fuentes de datos.

Formas de leer un json en Sql Server

En las versiones superiores a SQL Server 2016 existe dos formas de extaer la informacion de un json , la primera es con uso de variables, y la segunda con el uso de nuestro ya conocido openrowset funcion que nos permite leer archivos csv o de cualquier otro tipo de datos.

Ejemplo para leer json desde variable

Puedes convertir JSON a una tabla en SQL Server utilizando la función OPENJSON.

Por ejemplo:

DECLARE @json NVARCHAR(MAX) = '
[    
{        "id": 1,        
         "name": "John Doe",        
         "HBD": 3/5/1984  
         "salary": 365 },   
{        "id": 2,        
         "name": "Jane Doe",        
         "HBD": 3/2/1999  
         "salary": 359 }
]'

SELECT *
INTO #temp
FROM OPENJSON (@json)
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    age INT '$.age'
)

SELECT *
FROM #temp

Esto creará una tabla temporal #temp con los datos del JSON y podrás seguir trabajando con ella como con cualquier otra tabla en SQL Server. En este caso si quisieramos podemos aplicar formato a nuestro campo de salario para que se vean como dolar o a nuestro cambo de fecha nacimiento indicar el formato mas adecuado a nuestra region. Para ambos caso no estamos modificando el json son consultando.

Ejemplo convertir json a tabla con openrowset

Convertir un archivo JSON en una tabla en SQL Server utilizando OPENROWSET. Por ejemplo, si tienes un archivo llamado data.json en el servidor, puedes hacer lo siguiente:

SELECT *
INTO #temp
FROM OPENROWSET (
    BULK 'C:\data.json',
    SINGLE_CLOB
) AS j
CROSS APPLY OPENJSON (BulkColumn)
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    age INT '$.age'
)

SELECT *
FROM #temp

Igual que en el ejemplo anterior, esto creará una tabla temporal #temp con los datos del archivo JSON y podrás trabajar con ella como con cualquier otra tabla en SQL Server.

Como convertir una tabla a json con sql server?

Existen 2 dos forma en laas Puedes convertir una tabla en formato JSON en SQL Server utilizando la función FOR JSON. te recomiendo que ustilices convert o cast para los fines de dar los formatos de moneda o para convertir a los formatos de fechas requeridos.

La sintaxis básica es la siguiente:

SELECT col1, col2, col3
FROM table
FOR JSON AUTO

En este ejemplo, se seleccionan las columnas col1, col2, col3 de la tabla table, y se convierten en formato JSON. La cláusula FOR JSON AUTO indica que se deben generar los datos en formato JSON de manera automática.

FOR JSON PATH sintaxis

También puedes especificar el modo en que se deben generar los datos en formato JSON utilizando la cláusula FOR JSON PATH, por ejemplo:

SELECT col1, col2, col3
FROM table
FOR JSON PATH

En este caso, los datos se generarán en formato JSON utilizando la sintaxis de JSON PATH. Esto te permite controlar la estructura y formato final del JSON generado.

Hay muchas otras opciones y configuraciones que puedes utilizar con la función FOR JSON, dependiendo de tus necesidades y requerimientos específicos. Te recomiendo revisar la documentación oficial de Microsoft para obtener más información sobre cómo utilizar esta función.

Json sql server 2014

OPENJSON es compatible con SQL Server desde la versión 2016 (incluido). Esta función permite parsear y convertir datos en formato JSON a tablas relacionales en SQL Server, lo cual te permite realizar consultas y manipulaciones de datos de forma similar a como lo harías con cualquier otra tabla.

Para los casos de sql server 2014 o sql server 2012 podemos usar nuestra propia funcion

Ejemplo función personalizada en SQL Server 2014

Podemos crear una función personalizada en SQL Server 2014 para parsear y convertir datos en formato JSON a una tabla relacional.

Aquí hay un ejemplo de código que puedes utilizar como punto de partida:

CREATE FUNCTION ParseJSON (@json NVARCHAR(MAX))
RETURNS @temp TABLE (
    id INT,
    name NVARCHAR(50),
    age INT
)
AS
BEGIN
    DECLARE @start INT = 0, @end INT = 0, @length INT = LEN(@json)
    WHILE @start &lt; @length
    BEGIN
        SET @start = CHARINDEX('{', @json, @start) + 1
        SET @end = CHARINDEX('}', @json, @start) - 1

        DECLARE @data NVARCHAR(MAX) = SUBSTRING(@json, @start, @end - @start + 1)

        DECLARE @id INT = NULL, @name NVARCHAR(50) = NULL, @age INT = NULL

        SET @start = CHARINDEX('"id":', @data, 1) + 5
        SET @end = CHARINDEX(',', @data, @start) - 1
        IF @start > 0 AND @end > 0
            SET @id = SUBSTRING(@data, @start, @end - @start + 1)

        SET @start = CHARINDEX('"name":', @data, 1) + 8
        SET @end = CHARINDEX(',', @data, @start) - 1
        IF @start > 0 AND @end > 0
            SET @name = SUBSTRING(@data, @start, @end - @start + 1)

        SET @start = CHARINDEX('"age":', @data, 1) + 6
        SET @end = CHARINDEX('}', @data, @start) - 1
        IF @start > 0 AND @end > 0
            SET @age = SUBSTRING(@data, @start, @end - @start + 1)

        INSERT INTO @temp (id, name, age)
        VALUES (@id, @name, @age)
    END

    RETURN
END

Esta función parseará los datos de entrada en formato JSON y los insertará en una tabla temporal. Luego, puedes utilizar la tabla temporal para realizar consultas y manipulaciones de datos. la misma es compatible con sql server 2012. Podemos ver el uso de substring de sql y ciclos con con while necesarios para poder leer el json.

Es importante saber usar las variables y la clausula if de sql par fines de poder personalizar a tu medida tu funcion.

Tenga en cuenta que este es solo un ejemplo básico y que probablemente necesites modificar el código para que se adapte a tus necesidades específicas. Además, esta solución puede ser menos eficiente que utilizar la función OPENJSON, ya que es una implementación personalizada.

Conclusion

Las funcion de OPENJSON es compatible con SQL Server desde la versión 2016 para convertir un json a tabla, sin embargo pudimos ver como con los comando que ya deberias concer de manipular cadenas de caracteres es posible generar los mismo desde versiones de sql server 2014 o 2012. Es importante aclarar que el uso de esta funcion para analisis de archivos es fundamenta.

Ahora comentanos como usas esta funcion en tus base de datos

Hasta la proxima gracias.