# Extract json array to insert into a database

**URL:** <https://community.n8n.io/t/extract-json-array-to-insert-into-a-database/7380>\
**Category:** Questions\
**Created:** [August 19, 2021, 8:35pm UTC](https://community.n8n.io/t/extract-json-array-to-insert-into-a-database/7380 "2021-08-19T20:35:10Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![mcc37](https://avatars.discourse-cdn.com/v4/letter/m/c37758/32.png) [@mcc37](https://community.n8n.io/u/mcc37)\
**Post date:** [August 19, 2021, 8:35pm UTC](https://community.n8n.io/t/extract-json-array-to-insert-into-a-database/7380/1 "2021-08-19T20:35:10Z")

</div>

Hi all,

I’m quite a newbie at using N8N which is really great.

I’m planning to use it a lot for home usage to manage all kind of stuff at home and do the link between domotic system and Homekit. That’s the context !

For now, I’m strugling about handling data tha I would like to insert into a SQL database, probably Timescaledb.

I request an API with HTTP Request, and I get this :

```auto
[
{
"estimated_actuals": [
{
"pv_estimate": 0,
"period_end": "2021-08-19T20:00:00.0000000Z",
"period": "PT30M"
},
{
"pv_estimate": 0.0046,
"period_end": "2021-08-19T19:30:00.0000000Z",
"period": "PT30M"
},
{
"pv_estimate": 0.0651,
"period_end": "2021-08-19T19:00:00.0000000Z",
"period": "PT30M"
},
{
"pv_estimate": 0.6825,
"period_end": "2021-08-19T18:30:00.0000000Z",
"period": "PT30M"
}
]
}
]

```

I’m strugling with the SET node to extract the data so it can be inserted row after row in a database.

Am I missing something simple or does it require to write a javascript function ?

Thanks a lot for your help !

---

<div class="post-metadata">

**Author:** ![RicardoE105](https://dub1.discourse-cdn.com/n8n/user_avatar/community.n8n.io/ricardoe105/32/550_2.png) [@RicardoE105](https://community.n8n.io/u/RicardoE105)\
**Post date:** [August 19, 2021, 11:42pm UTC](https://community.n8n.io/t/extract-json-array-to-insert-into-a-database/7380/2 "2021-08-19T23:42:22Z")

</div>

Welcome to the community @mcc37

Sadly you cannot extract the data with a set node. For that, a function node after the HTTP node is needed. The function node should look as shown below.

```auto
const estimated_actuals = items[0].json.estimated_actuals

return estimated_actuals.map(estimate => ({ json: estimate }))

```

> **Example workflow**
>
> ```
> {
> "nodes": [
> {
> "parameters": {},
> "name": "Start",
> "type": "n8n-nodes-base.start",
> "typeVersion": 1,
> "position": [
> 250,
> 300
> ]
> },
> {
> "parameters": {
> "functionCode": "return [\n {\n json: {\n \"estimated_actuals\": [\n {\n \"pv_estimate\": 0.0046,\n \"period_end\": \"2021-08-19T19:30:00.0000000Z\",\n \"period\": \"PT30M\"\n },\n {\n \"pv_estimate\": 0.0046,\n \"period_end\": \"2021-08-19T19:30:00.0000000Z\",\n \"period\": \"PT30M\"\n },\n]\n \n }\n } \n ]\n"
> },
> "name": "Function",
> "type": "n8n-nodes-base.function",
> "typeVersion": 1,
> "position": [
> 560,
> 300
> ],
> "notesInFlow": true,
> "notes": "HTTP Request Mockup"
> },
> {
> "parameters": {
> "functionCode": "const estimated_actuals = items[0].json.estimated_actuals\n\nreturn estimated_actuals.map(estimate => ({ json: estimate }))\n"
> },
> "name": "Function1",
> "type": "n8n-nodes-base.function",
> "typeVersion": 1,
> "position": [
> 800,
> 300
> ],
> "notesInFlow": true,
> "notes": "Mapping"
> }
> ],
> "connections": {
> "Start": {
> "main": [
> [
> {
> "node": "Function",
> "type": "main",
> "index": 0
> }
> ]
> ]
> },
> "Function": {
> "main": [
> [
> {
> "node": "Function1",
> "type": "main",
> "index": 0
> }
> ]
> ]
> }
> }
> }
> 
> ```

---

<div class="post-metadata">

**Author:** ![mcc37](https://avatars.discourse-cdn.com/v4/letter/m/c37758/32.png) [@mcc37](https://community.n8n.io/u/mcc37)\
**Post date:** [August 20, 2021, 5:26am UTC](https://community.n8n.io/t/extract-json-array-to-insert-into-a-database/7380/3 "2021-08-20T05:26:00Z")

</div>

Thanks @RicardoE105

I’m gonna try this.

That should be a nice evolve to be able to manipulate this kind of json without code.

Or maybe, I should build a connector for this API that would handle that point and return datas in the right format.

---

<div class="post-metadata">

**Author:** ![sirdavidoff](https://dub1.discourse-cdn.com/n8n/user_avatar/community.n8n.io/sirdavidoff/32/2423_2.png) [@sirdavidoff](https://community.n8n.io/u/sirdavidoff)\
**Post date:** [August 20, 2021, 9:12am UTC](https://community.n8n.io/t/extract-json-array-to-insert-into-a-database/7380/4 "2021-08-20T09:12:05Z")

</div>

FYI a node to do this without code is in the works.

---

<div class="post-metadata">

**Author:** ![mcc37](https://avatars.discourse-cdn.com/v4/letter/m/c37758/32.png) [@mcc37](https://community.n8n.io/u/mcc37)\
**Post date:** [August 20, 2021, 9:18am UTC](https://community.n8n.io/t/extract-json-array-to-insert-into-a-database/7380/5 "2021-08-20T09:18:27Z")

</div>

Great news @sirdavidoff ! I will be happy to test as available?

Just tested your function @RicardoE105, it works perfectly. Thanks a lot !

---

<div class="post-metadata">

**Author:** ![mcc37](https://avatars.discourse-cdn.com/v4/letter/m/c37758/32.png) [@mcc37](https://community.n8n.io/u/mcc37)\
**Post date:** [August 20, 2021, 10:04am UTC](https://community.n8n.io/t/extract-json-array-to-insert-into-a-database/7380/6 "2021-08-20T10:04:48Z")

</div>

Just to share, I did achieve to setup my N8N workflow to pull Estimated PV production at my home from the public [Solcast API](https://solcast.com/solar-data-api/api-toolkit/) and insert into a TimescaleDB instance.

I have also another one for Predicted PV production.

I’m gonna be able to compare with real production without code, just amazing 🙂

---

<div class="post-metadata">

**Author:** ![RicardoE105](https://dub1.discourse-cdn.com/n8n/user_avatar/community.n8n.io/ricardoe105/32/550_2.png) [@RicardoE105](https://community.n8n.io/u/RicardoE105)\
**Post date:** [August 20, 2021, 1:21pm UTC](https://community.n8n.io/t/extract-json-array-to-insert-into-a-database/7380/7 "2021-08-20T13:21:13Z")

</div>

Really cool. Thanks for sharing.