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.
Continue reading →