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?