This seemingly straightforward task made me do some digging. Imagine we have a table with JSONB Field and we want to search for specific field values using wildcard character to allow partial matches.
Ecto doesn’t have a JSONB support but this particular task is so common that it’s even mentioned in the documentation:
fragment("?->>? ILIKE ?", p.map, "key_name", ^some_value)
What we need is just add the wildcard characters around the value. We can’t do that directly (“?->>? ILIKE %?%”) though since it would trigger Ecto’s syntax error and the query wouldn’t compile.
We also can’t use quotes (“?->>? ILIKE ‘%?%'”) unless we search for the literal – such query would compile just fine but will trigger a DB level error. Depending on the number of interpolated parameters it might be the “bind message supplies 0 parameters, but prepared statement requires 1” or “could not determine data type of parameter” but they all mean, in a not so explicit way, that single quotes do not allow placeholders.
The solution is to “compile” the value with wildcard characters beforehand and just pass it to the above query, still pretty straightforward but only after it started working:
v = "%#{some_value}%"
from(q in Schema, where: fragment("?->>? ILIKE ?", p.map, ^key, ^v))