Does anyone know what the problem here is exactly? I'm very new to retool but tried a few different ways to accomplish this and consulted past issues here to no avail.
When you run code1, what does the output look like? I've occasionally had to use code1.data.data or something like that depending on how the data comes back.
1 Like
Hey thanks for your reply. code1 correctly outputs the json array, and each json object element in the json section just like query1. I tried data.data and it says it ran successfully but only produces an empty row in the table. I also tried
INSERT INTO ssa_inventory (data) SELECT jsonb_array_elements({{code1.data}});
INSERT INTO ssa_inventory (data) SELECT jsonb_array_elements({{code1.data}}::jsonb);"
which both gave the same initial error, and both with data.data as well which don't give an error but don't update the table at all.
Would you mind sharing a screenshot of what it looks like when you run the code1 block? That might help troubleshoot, even though it sounds like everything looks fine from your end...
Did you try adding ticks around the array?
INSERT INTO ssa_inventory (data) SELECT jsonb_array_elements(' {{code1.data}} ')
Yes I just tried INSERT INTO ssa_inventory (data) SELECT jsonb_array_elements(' {{code1.data}} ')
and
INSERT INTO ssa_inventory (data) VALUES (' {{code1.data}} ') and got the same error.
How about
INSERT INTO ssa_inventory (data) VALUES (' {{code1.data[0]}} ')
(with or without the ticks)
Hey sorry i just saw this. With the ticks returned the same error but INSERT INTO ssa_inventory (data) VALUES ({{code1.data[0]}}) without the ticks worked and updated the table with the first element in the json array!
Knew we’d get there eventually!
Yes thank you!!! Now I need to figure out how to update the table with all the JSON objects in the array instead of one. Would you have any idea why
INSERT INTO ssa_inventory (data) VALUES ({{code1.data[0]}})
works but not
INSERT INTO ssa_inventory (data) VALUES ({{code1.data}})?
I have no idea why this works but the only thing that works so far for all the elements in the array is this:
INSERT INTO ssa_inventory (data) SELECT json_data FROM unnest({{code1.data}}::json[]) AS t(json_data);
It creates a row in the Retool database table for every element in the json array of json objects.
But I'm still very interested why the more straightforward method doesn't work but this does, even though the version you suggested for a single element in the array works, and the input should be a valid json array since it passed stringify?
The SQL for a multi-value insert like what you are doing is
INSERT INTO xyz (col1, col2)
VALUES ('a','b'),('c','d');
The {{code1.data}} is a JSON object, not a bunch of JSON objects surrounded by ( ). The simple INSERT of a single value works because you are putting the first object {{code1.data[0}} inside ( ) hich is the right format.
Since you probably have a variable number of results (and it would be annoying), you don't want to write something like:
INSERT INTO
ssa_inventory (data)
VALUES
({{code1.data[0]}}),
({{code1.data[1]}}),
etc...
And so you need switch it up to a SELECT query (as it seems you successfully did).
1 Like
That makes sense! Thanks so much for your help.