Insert JSON into databases

JSON is the main data format used in Skyone Studio and you may often want to write several data records from a JSON array to a relational database.

You can always process each element of the array individually and use a regular INSERT to store the record in the database. However, most databases have tools for receiving a JSON array as an argument to a function that maps the array elements to the database columns. These functions allow us to pass large JSON arrays to a single INSERT, saving time and processing from platform 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