Workflow retool database block jsonb error "invalid input syntax for type json" when trying to populate jsonb column

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 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 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({{}});

INSERT INTO ssa_inventory (data) SELECT jsonb_array_elements({{}}::jsonb);"

which both gave the same initial error, and both with 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...

Yes definitely!

Screenshot 2024-04-08 at 12.03.26 PM

Did you try adding ticks around the array?

INSERT INTO ssa_inventory (data) SELECT jsonb_array_elements(' {{}} ')

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

How about
INSERT INTO ssa_inventory (data) VALUES (' {{[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 ({{[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 ({{[0]}})
works but not
INSERT INTO ssa_inventory (data) VALUES ({{}})?

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({{}}::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 {{}} 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 {{[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:

  ssa_inventory (data)

And so you need switch it up to a SELECT query (as it seems you successfully did).


That makes sense! Thanks so much for your help.