Double brackets not working as expected when querying jsonb column in postgres database

[austinxyz]

Having trouble with this dynamic psql query in Retool. I've tried a lot of variations of this. Here is the query without templating, which runs as expected:

SELECT * FROM ability WHERE "action" -> 'linkUrl' @> '"https://poolsuite.net/"'::jsonb;

But as soon as I put any sort of handlebar template in place of that url, it fails with invalid input syntax for type json

Here is what is written in retool in the query:

SELECT * FROM ability WHERE "action" -> 'linkUrl' @> '{{AbilitiesTable.selectedRow.data.action.linkUrl}}'::jsonb;

As you can see in this screenshot, the brackets/handlebars should resolve to the same string as in the hard coded example above:

It seems that Retool is using a prepared statement to make the handlebars work, so I tested the query with a prepared statement in a database management tool (TablePlus), and it worked the same as the above query, as expected.

PREPARE ability_query (text) AS SELECT * FROM ability WHERE "action" -> 'linkUrl' @> $1::jsonb; EXECUTE ability_query ('"https://poolsuite.net/"');

What am I doing wrong?

[ScottR]

Not sure but try ticking the following box in Core Resource

[Kabirdas]

Hey @austinxyz!

It looks like the string you're passing doesn't include double quotes within the string itself and those are just being displayed in the Retool preview to indicate that it is a string. Can you try using {{'"' + AbilitiesTable.selectedRow.data.action.linkUrl + '"'}} instead of '{{AbilitiesTable.selectedRow.data.action.linkUrl}}'?

(Note the removal of the ' outside of the curlies as well)

[austinxyz]

@Kabirdas

{{'"' + AbilitiesTable.selectedRow.data.action.linkUrl + '"'}}

This worked. Thank you @Kabirdas!