# 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** <a href="#json-source" id="json-source"></a>

As an example, consider the array of trades extracted from CoinAPI:

```json
[
    {
        "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"
    }
]

```

&#x20;                                                              *Trades array (<>trades\</>)*

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

### **Postgres** <a href="#postgres" id="postgres"></a>

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:

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

```

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

### **Oracle** <a href="#oracle" id="oracle"></a>

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:

```json
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.

&#x20;

### **MS SQL** <a href="#ms-sql" id="ms-sql"></a>

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:

```json
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.

{% hint style="warning" %}
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
{% endhint %}

Read more: [How to Escape a Single Quote in SQL Server](https://www.encodedna.com/sqlserver/tips/how-to-escape-a-single-quote-in-sql-server.htm)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.skyone.cloud/english/skyone-studio/how-to/insert-json-into-databases.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.