I am working with Snowflake and ACF21 and trying to run a

queryExecute

with a JDBC connection. In my case, Snowflake has a flattened JSON table column which you can query individual keys like this (assuming the column name is

RECORD_CONTENT

)

return queryExecute(
	"SELECT TOP 50
	RECORD_CONTENT:booking_id AS id
	FROM mytable
	WHERE RECORD_CONTENT:status = :status",
	{
		status = {
			value = arguments.status,
			cfsqltype = "cf_sql_varchar"
		}
	}
);

However this creates issues when you add query params in the WHERE clause because they are separated by a column themselves. CF thinks that the selects are params, and ends up changing the query to this:

... SELECT RECORD_CONTENT?:booking_id ...

(notice the "?"), and the query fails complaining about not having enough query params. I found this stackoverflow answer, which suggests escaping the columns, but it doesn't work for me: https://stackoverflow.com/questions/53308446/how-to-properly-escape-colon-in-queryexecute-sql-statement Is there any other solution than the suggested second solution in that answer, which is change all the SELECT columns into queryparams? That does seem like a lot of work.

result = QueryExecute("
  SELECT :selectBookingid AS ...
  ",
  { selectBookingid : "RECORD_CONTENT:booking_id"}
  );