I'm experiencing an issue while using PostgreSQL with Lucee on the backend. One of the tables has column with jsonb datatype. I'm using ? operator to check existence of the value. While this works when I run it in DBeaver, I'm experiencing an issue while processing the file with lucee. Please see the code below.
public query function getItems( uid ) {
var sqlText = "
SELECT ar.role_group, ar.role_indv
FROM app.app_roles ar, app.app_user au
WHERE au.user_roles ? ar.role_id::text AND ar.role_is_active = 'Y' AND au.user_id = :uid
";
return queryExecute( sqlText, {uid: { value: arguments.uid, cfsqltype: "integer" }}, {datasource : "my_dev"});
}
writeDump(getItems(24));
SQL code is working and seems correct. The issue is on this line: WHERE au.user_roles ? ar.role_id::text
I couldn't figure out why Lucee is having issues with this code. Do any of you know a workaround for this issue? Please see error below.
Message: string there are more question marks in the SQL than params defined
Thank you.