Disabled Prepared Statements for Query JSON with SQL

[ianb-pomelo]

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?

[victoria]

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

[ianb-pomelo]

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

[victoria]

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

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.

[ianb-pomelo]

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!

[victoria]

[victoria]

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