JSONB Search Error

Hey guys,

I have an SQL query I am running where I am trying to search a JSONB object, which holds arrays, holds an element within a specific array.

Let's imagine this is the structure:

history = {
"orders": ["order 1", "order 2", "order 3"]

I am able to successfully query my database outside of retool by using this clause in my SQL query:

SELECT * FROM "History" WHERE history->'order' @> '["order 1"]'

What I am trying to do is create a text box for the user to punch in a string to then search the array within that JSONB object. However, when I try to feed it the input from the text box, the query fails. I've tried the following two examples:

SELECT * FROM "History" WHERE history->'order' @> '["{{textInput1.value}}"]'
SELECT * FROM "History" WHERE history->'order' @> '[{{textInput1.value}}]'

However, if I am Retool dev mode and deleted the text input value part, and type in the string I am trying to query -- "order 1" -- it works as intended. I believe this is probably an issue with how retool is handling the substitution of the string into the query.

Can anyone help?

Any ideas on how to solve this issue?

@kbn @victoria @Kabirdas @joeBumbaca @Tess

Please help, this is really breaking a big portion of functionality my app needs. What is the solution here?

Hi there!

I tested this solution & it seems to work well for jsonb :blush:

Thanks so much. This has solved my problem.

1 Like