Problem on query with parameter and Postgres JSONB column

1

Hi,

when I do a query with that where clausule I got a error:

users.active AND ( users."services.idp.matricula" = {{user_key}} OR roles ? 'livechat-manager' )

image
image1504×675 80.3 KB

But the same without parameter works:
users.active AND ( users."services.idp.matricula" = '123' OR roles ? 'livechat-manager' )

Or using another comparison without JSONB operator works too
users.active AND ( users."services.idp.matricula" = {{user_key}} OR roles is not null )

roles is a JSONB column

{
  "browser-info": {
    "language": "pt-BR",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.81 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.14+9",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.14",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.14+9",
    "os.name": "Linux",
    "os.version": "5.10.16.3-microsoft-standard-WSL2",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "postgres",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "14.1 (Debian 14.1-1.pgdg110+1)"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.23"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-02-08",
      "tag": "v0.42.0",
      "branch": "release-x.42.x",
      "hash": "de1264e"
    },
    "settings": {
      "report-timezone": "America/Araguaina"
    }
  }
}
2

I've found the problem in logs:

users.active AND ( users.\"services.idp.matricula\" = ? OR roles ? 'livechat-manager' )"

The parameters are replaced by '?' and the '?' operator is treated as another parameter

3

After some random tries to escape the question mark it has worked (two question marks):

WHERE matricula = {{user_key}} AND roles ?? 'livechat-manager'