I need help for JSON to Postgres SQL

[Dimitar_Terziev]

Hello everyone,

I am currently working on a project where I am trying to insert data from the following JSON file into a PostgreSQL database:

[
{
"Part Number": "7036866",
"RID": "200",
"Status": "New",
"Type": "Manufacturer_code"
},
{
"Part Number": "R134A",
"RID": "200",
"Status": "New",
"Type": "other_code"
},
{
"Part Number": "326527",
"RID": "200",
"Status": "New",
"Type": "visible_code"
}
]

I am using Retool.com as my platform for building the application, but I am not sure how to proceed. I am trying to build a query but with not success. I would really appreciate it if anyone who has experience with Retool and PostgreSQL could give me some guidance on how to insert this JSON data into the database. Any suggestions or examples would be greatly appreciated.

Thank you in advance for your help!

[ScottR]

@Dimitar_Terziev Welcome to the forumn!
There are many ways to implement this but the basic way is the following:
Use a javascript query to iterate over your result set (I used a transformer but in your case I would imagine the above array is the result of a query so below you would replace what I labeled as "parts" to yourquery.data:

1. Create a JS query and add:

for (i=0; i<parts.value.length;i++) {
  query53.trigger({
  additionalScope: {
    part_number: parts.value[i]['Part Number'],
    rid: parts.value[i].RID,
    status: parts.value[i].Status,
    type: parts.value[i].Type
  },
});
}

query53 looks like:
insert into parts(part_number,rid,status,type) VALUES ({{part_number}},{{rid}},{{status}},{{type}})
(Note that my table "parts" contains the following columns part_number,rid,status,type

When you run the js query it calls the sql to iterate over the array and insert each array value...

Hope this helps.... (BTW you can also use Promise) Check and search the docs here

[Dimitar_Terziev]

Thank you Scott for your help, you were extremely helpful in resolving my issue. I really appreciate the time and effort you took to assist me. Thanks again!