N8N Uptodate (Docker) - How to import in postgresql a json table

[Phildefer]

I don’t how to import a json table with a prostgresql node

Please share your workflow

(Select the nodes on your canvas and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow.)

Share the output returned by the last node

Information on your n8n setup

• n8n version: uptodate
• Database (default: SQLite): postgresql
• n8n EXECUTIONS_PROCESS setting (default: own, main): default
• Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
• Operating system: Linux

Hi all, I’m trying to import a json file in which there is a table.
I can see the value in postgresql node when I put that : {{ $json.data[0].year }}
My problem is the index. If I let [0] all the database will be populate with a single record. So I’d like to know how to put a index [i] which can be incremented during the process.
Thx.

[ria]

Hi @Phildefer

Could you kindly share your workflow / node configuration and also some example of the json data you are trying to import?

You can share your workflow here by pasting them as JSON in between two block quotes (```).

Thanks!

[Phildefer]

Hi @ria ,

Here is my workflow

So For the moment I just keep the table into the json (keep Data Only node) but I’d like to avoid this step and directly to read the Data into the complete Json.

Here is an exemple of the raw file (before the Keep Data Only node)

[
{
“json”: {
“status”: 200,
“success”: true,
“last_update”: 113,
“count”: 26089,
“messages”: ,
“data”: [
{
“event_id_cnty”: “BFO12566”,
“event_date”: “2024-09-13”,
“year”: “2024”,
“time_precision”: “1”,
“disorder_type”: “Political violence”,
“event_type”: “Battles”,
“sub_event_type”: “Armed clash”,
“actor1”: “JNIM: Group for Support of Islam and Muslims”,
“assoc_actor_1”: “”,
“inter1”: “2”,
“actor2”: “Police Forces of Burkina Faso (2022-)”,
“assoc_actor_2”: “”,
“inter2”: “1”,
“interaction”: “12”,
“civilian_targeting”: “”,
“iso”: “854”,
“region”: “Western Africa”,
“country”: “Burkina Faso”,
“admin1”: “Boucle du Mouhoun”,
“admin2”: “Nayala”,
“admin3”: “Yaba”,
“location”: “Lah”,
“latitude”: “12.8000”,
“longitude”: “-2.9833”,
“geo_precision”: “1”,
“source”: “Undisclosed Source”,
“source_scale”: “Local partner-Other”,
“notes”: “On 13 September 2024, JNIM militants attacked a patrol of special police unit (GUMI) in the village of Lah (Yaba, Nayala). Casualties unknown.”,
“fatalities”: “0”,
“tags”: “”,
“timestamp”: “1726526020”
},
{
“event_id_cnty”: “BFO12571”,
“event_date”: “2024-09-13”,
“year”: “2024”,
“time_precision”: “1”,
“disorder_type”: “Political violence”,
“event_type”: “Battles”,
“sub_event_type”: “Armed clash”,
“actor1”: “JNIM: Group for Support of Islam and Muslims”,
“assoc_actor_1”: “”,
“inter1”: “2”,
“actor2”: “VDP: Volunteer for Defense of Homeland”,
“assoc_actor_2”: “”,
“inter2”: “4”,
“interaction”: “24”,
“civilian_targeting”: “”,
“iso”: “854”,
“region”: “Western Africa”,
“country”: “Burkina Faso”,
“admin1”: “Nord”,
“admin2”: “Loroum”,
“admin3”: “Solle”,
“location”: “Solle”,
“latitude”: “14.1219”,
“longitude”: “-2.1144”,
“geo_precision”: “2”,
“source”: “Al Zallaqa”,
“source_scale”: “New media”,
“notes”: “On 13 September 2024, JNIM claimed to have ambushed volunteer fighters (VDP) between Solle and Tibou (Solle, Loroum). The ambush resulted in human and material losses, the statement claimed. Unknown fatalities coded to 3.”,
“fatalities”: “3”,
“tags”: “”,
“timestamp”: “1726526020”
},
],
“filename”: “-Western_Africa”
},
“pairedItem”: {
“item”: 0
}
}
]

Thanks

[ria]

Hi @Phildefer,

I’m sorry, I fail to see what’s wrong with your workflow.
Could you perhaps share the error you’re getting?

Also, did you know you can pin data in your workflow?
Perhaps if you could run it with the (dummy) data that you have and then pin the data in either the HTTP node or Split out Node, that would help to understand the data flow in your workflow.

Thanks!

Data pinning | n8n Docs

[Phildefer]

Hi @ria

Thx for your answer. I don’t have an error message because my problem is not a malfunction. My problem is that in my json file I have a data table. The first solution I used was to extract the table from the file (node Keep data Only) but I thought that it could useful to keep the whole file to save a node and instead use this : {{ $json.data[i].year }} etc for each field. My problem is the index (i). If I use {{ $json.data[0].year }} all the row will have the same values (of the first record). I have to increment the index (i) of the table to get all data but I don’t know how to do that.
Thx.

[ria]

Hi @Phildefer

Thanks for clarifying! I understand what you’re trying to do now.

So essentially, this loop that you are trying to create in the HTTP request node, is the entire logic of the Split Out node.

You cannot achieve the same thing because our expressions will always evaluate into one single item (array). This output is what you need to loop over and that’s exactly what the Split Out node is doing.

I hope this makes sense? I think your workflow is very ok

[Phildefer]

Thx @ria !
I keep my node to split the data.