Insert JSON into databases

Challenge

JSON is the primary data format used in Skyone Studio, and it's common to want to write multiple data records from a JSON array into a relational database.

You can always process each element of the array individually and use a standard INSERT to store the record in the database. However, most databases offer tools that accept a JSON array as an argument to a function that maps array elements to database columns. These functions allow you to pass large JSON arrays in a single INSERT, saving time and processing effort within Skyone Studio flows.

JSON Source

As an example, consider the array of trades extracted from 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"
    }
]

Trades array (<>trades</>)

We usually want to INSERT the whole array in a single database operation.

Postgres

Let's assume we have the database trades already created with the column names identical as the property names of the trades array.

In the query field onf the database operation you can configure:

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

Where <>trades</> is a parameter that contain the array as specified above.

Oracle

Let's assume we have the database trades already created with the column names identical as the property names of the trades array.

In the query field onf the database operation you can configure:

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;

Where <>trades</> is a parameter that contain the array as specified above.

Observe we have to use the variable my_array to store the original JSON array. This is required to handle with arrays larger then 4000 charactes.

MS SQL

Let's assume we have the database trades already created with the column names identical as the property names of the trades array.

In the query field onf the database operation you can configure:

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' 
) 

Where <>trades</> is a parameter that contain the array as specified above.

If your JSON has strings with single quotes as part of the text, you may need to escape them by duplicating the single quotes. The MS SQL will store a single quote instead

Read more: How to Escape a Single Quote in SQL Server