SqliteException(1): while preparing a statement, unrecognized token: "@" about drift HOT 3 CLOSED

Shouldn't this be valid syntax since SQLite 3.38.0

I'm not sure why you'd think that, that operator has never been in sqlite3. sqlite 3.38.0 has added some json operators but all the @ things are very much postgres-specific. The [] array syntax is not valid SQL either.

If you want to check whether a column is in a list of values, use equipment IN (?, ?) and bind the variables to the values you care about. Or use isIn in drift.

from drift.

Sorry, my bad. Thank you for pointing that out.

I ended up with this query

"SELECT * FROM exercises WHERE EXISTS ( SELECT * FROM json_each(equipment) WHERE value IN ($list))"

isIn doesn't seem to work it returns zero results.
Probably because it can't handle Array with Strings in it?

from drift.

Yes the array syntax is not supported by sqlite3. If you put that into a drift file or into the DriftDatabase annotation under the queries parameter, drift will generate a method for you that desugars the IN :variable.

from drift.