Insertar JSON en bases de datos

Desafío JSON es el principal formato de datos utilizado en Skyone Studio y, por lo general, puedes querer escribir varios registros de datos de un arreglo JSON en una base de datos relacional.

Siempre puedes procesar cada elemento del arreglo individualmente y usar un INSERT regular para almacenar el registro en la base de datos. Sin embargo, la mayoría de las bases de datos cuentan con herramientas para recibir un arreglo JSON como argumento de una función que mapea los elementos del arreglo a las columnas de la base de datos. Estas funciones nos permiten enviar grandes arreglos JSON en una sola instrucción INSERT, ahorrando tiempo y procesamiento desde los flujos de Skyone Studio.

Fuente JSON Como ejemplo, considera el arreglo de trades extraído de CoinAPI:

---

[
    {
        "symbol_id": "BITSTAMP_SPOT_BTC_USD",
        "time_exchange": "2023-01-23T00:16:24.1850000Z",
        "time_coinapi": "2023-01-23T00:16:24.2096192Z",
        "uuid": "f4b6a7d3-3a6b-4870-9073-383c62f68d6b",
        "price": 22688,
        "size": 0.0268,
        "taker_side": "BUY"
    },
    {
        "symbol_id": "BITSTAMP_SPOT_BTC_USD",
        "time_exchange": "2023-01-23T00:16:26.6100000Z",
        "time_coinapi": "2023-01-23T00:16:26.6324401Z",
        "uuid": "b473a149-e174-4c98-a7e6-561b0ad470be",
        "price": 22691,
        "size": 0.00419,
        "taker_side": "BUY"
    }

Normalmente queremos hacer un INSERT de todo el arreglo en una sola operación de base de datos.

Postgres

Supongamos que ya tenemos la base de datos de trades creada con los nombres de columna idénticos a los nombres de propiedad del arreglo de operaciones.

En el campo de query de la operación de base de datos, puedes configurar:

INSERT INTO trades 
SELECT * FROM 
json_populate_recordset (NULL::trades, 
'<>trades</>'
);

Donde <>trades</> es un parámetro que contiene el arreglo como se especificó arriba.

---

Oracle

Supongamos que ya tenemos la base de datos de trades creada con los nombres de columna idénticos a los nombres de propiedad del arreglo de operaciones.

En el campo de query de la operación de base de datos, puedes configurar:

DECLARE
my_array CLOB := '<>trades</>';
BEGIN
INSERT INTO trades (SYMBOL_ID, TIME_EXCHANGE, TIME_COINAPI, UUID, PRICE, "SIZE", TAKER_SIDE)
SELECT * FROM json_table(my_array FORMAT JSON,'$[*]' 
COLUMNS(
    symbol_id VARCHAR PATH '$[*].symbol_id',    
    time_exchange TIMESTAMP PATH '$[*].time_exchange',
    time_coinapi TIMESTAMP PATH '$[*].time_coinapi',
    uuid VARCHAR PATH '$[*].uuid',
    price NUMBER PATH '$[*].price',
    "SIZE" NUMBER PATH '$[*].size',
    taker_side VARCHAR PATH '$[*].taker_side'    
));
END;

Donde <>trades</> es un parámetro que contiene el arreglo como se especificó arriba.

Observa que tenemos que usar la variable my_array para almacenar el arreglo JSON original. Esto es necesario para manejar arreglos mayores a 4000 caracteres.

---

MS SQL

Supongamos que ya tenemos la base de datos de trades creada con los nombres de columna idénticos a los nombres de propiedad del arreglo de operaciones.

En el campo de query de la operación de base de datos, puedes configurar:

DECLARE @json NVARCHAR(max)  = N'<>trades</>' 
INSERT INTO trades
SELECT * FROM OPENJSON(@json)  
WITH   
(
    symbol_id VARCHAR(200) '$.symbol_id',    
    time_exchange DATETIME '$.time_exchange',
    time_coinapi DATETIME '$.time_coinapi',
    uuid VARCHAR(200) '$.uuid',
    price NUMERIC '$.price',
    size NUMERIC '$.size',
    taker_side VARCHAR(200) '$.take_side' 
) 

Donde <>trades</> es un parámetro que contiene el arreglo como se especificó arriba.

Si tu JSON tiene cadenas con comillas simples como parte del texto, puede que necesites escaparlas duplicando las comillas simples. MS SQL almacenará una sola comilla en su lugar.

Lee también: How to Escape a Single Quote in SQL Server