添加链接
link管理
链接快照平台
  • 输入网页链接,自动生成快照
  • 标签化管理网页链接

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.

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.

Yes I just tried INSERT INTO ssa_inventory (data) SELECT jsonb_array_elements(' {{code1.data}} ')
INSERT INTO ssa_inventory (data) VALUES (' {{code1.data}} ') and got the same error.

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!

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).