Disabled Prepared Statements for Query JSON with SQL

I'm trying to write a generic query for a table that allows people to use the Query JSON with SQL to dynamically filter a table. However, when I try to use a Text Input or Query Builder to create the SQL expression, it prepares the statements with quotes and doesn't work. Is there a way to disable prepared statements for the query JSON with SQL resource? Or is there another way to filter the results of a JSON transformer using an arbitrary SQL-like statement?

Hi @ianb-pomelo! Would you mind sharing a screenshot of your current query? Are you looking to do something like this?

Yep attached. Basically I want to dynamically create a query builder (or allow the user to use a text input if the query builder doesn't work) to create the WHERE filter on the expression. I can get the fields that are filterable from the data using a JS expression so the query builder works but when I try to use it in it quotes the where expression. I get wanting to escape to be safe but given the query is on data that has already been loaded it's safe to do

Ah got it! I've been playing around with this and haven't been able to get it to work with the Query JSON with SQL type :frowning:

However, I have been able to kind of get it to work with a JS transformer. Basically, using a filter clause instead of WHERE.

Would something like this work for you?

Here's the code I used:

const dataArr = {{formatDataAsArray(query3.data)}}
const field = {{querybuilder1.value.rules[0].field}}
const value = {{querybuilder1.value.rules[0].value}}
const operator = {{querybuilder1.value.rules[0].operator}}
if (operator == 'like') return dataArr.filter(obj => _.includes(obj[field], value))
if (operator == '>') return dataArr.filter(obj => obj[field] > value)
// etc.

Thanks for the reply! I think this would work but I think the maintenance burden of using this in multiple places may be too much to make it worth adding but really appreciate the feedback!

sad thumbs up cat Meme Template - Piñata Farms - The best meme generator  and meme maker for video & image memes

Of course! Sorry we didn't make this easy on you. Please write back in anytime and hopefully we can find a better solution for you next time :slight_smile: