Value formatting in keyvaluemap components II

App Building
, , , ,
1

I am inserting to a postgres database based on selected rows

Thanks @markbenepass for his helpful post

my_script (a js query) returns the following (it uses JSON.stringify)

"[{"lane_uuid":"5ad0fb16-2733-4ec6-b8ee-dec56dbfd19d","date_added":"09/27/2022, 16:53:58","added_by":"lee.walter@abc_company.com","block_type":"FULL"},{"lane_uuid":"8ff3fbea-220b-4314-827f-fd9f767b09c1","date_added":"09/27/2022, 16:53:58","added_by":"lee.walter@abc_company.com","block_type":"FULL"},{"lane_uuid":"8ff3fbea-220b-4314-827f-fd9f767b09c1","date_added":"09/27/2022, 16:53:58","added_by":"lee.walter@abc_company.com","block_type":"FULL"}]"

A separate sql query tries to instert this into a postgres database
INSERT INTO u_quotes.block_list2 VALUES {{my_script.data}})

and received a syntax error. I tried also
INSERT INTO u_quotes.block_list2 VALUES ({{my_script.data}}))

Any idea why this doesnt work? Could this be because JSON stringify inserts square brackets which sql cannot handle?

2

Update:

I tried moving the json.stringify from my_script into the sql query itself

INSERT INTO u_quotes.block_list2
VALUES ({{JSON.stringify(make_selection_list.data)}})

Here I did not get an error but rather than parse the string into 3 records and 4 columns, the entire string was inserted as one record and added to the first column

the output of the json stringify still had those unwanted square brackets

Perhaps if I remove the square brackets, using the solutions(s) here, it will work ??

3

Hi @lee_walter!

I'd recommend using the GUI mode when inserting/updating data in SQL databases. For this case specifically, configuring the Postgres query as below got a hard-coded version of your JSON data to upload to the DB with no issue. You'll just want to change id -> uuid since that was the primary key in mine.

image
image1014×880 46.2 KB

-Justin

1 Like
4

thanks so much @jmann . something very close to that worked for me

1 Like