How do I use the PostgreSQL JSON ?
operator in Ecto? Assume that I have a table users
that has a JSONB field data
, and I want to test (in PostgreSQL) whether it has a key honorific
.
The SQL for this is:
SELECT *
FROM users
WHERE data ? 'honorific';
Neither of these work:
from u in :users, where: fragment("? \? ?", u.data, "honorific")
from u in :users, where: fragment("? ? ?", u.data, "?", "honorific")
The former fails with a compile error (not enough parameters to match the number of question marks) and the latter fails in the query because it produces SELECT u0.* FROM users WHERE u.data '?' 'honorific'
.
I think that this is a bug, and Iβd be interested to know how we would need to use such operators, because PostgreSQL has several:
ββββββββ¬ββββββββββ¬ββββββββββββ¬ββββββββββββββ¬βββββββββββββββββββββββββββββ
β Name β Left argβ Right arg β Result type β Description β
ββββββββΌββββββββββΌββββββββββββΌββββββββββββββΌβββββββββββββββββββββββββββββ€
β <?> β abstime β tinterval β boolean β is contained by β
β ? β jsonb β text β boolean β key exists β
β ?# β box β box β boolean β deprecated, use && instead β
β ?# β line β box β boolean β intersect β
β ?# β line β line β boolean β intersect β
β ?# β lseg β box β boolean β intersect β
β ?# β lseg β line β boolean β intersect β
β ?# β lseg β lseg β boolean β intersect β
β ?# β path β path β boolean β intersect β
β ?& β jsonb β text[] β boolean β all keys exist β
β ?- β point β point β boolean β horizontally aligned β
β ?- β β line β boolean β horizontal β
β ?- β β lseg β boolean β horizontal β
β ?-| β line β line β boolean β perpendicular β
β ?-| β lseg β lseg β boolean β perpendicular β
β ?| β jsonb β text[] β boolean β any key exists β
β ?| β point β point β boolean β vertically aligned β
β ?| β β line β boolean β vertical β
β ?| β β lseg β boolean β vertical β
β ?|| β line β line β boolean β parallel β
β ?|| β lseg β lseg β boolean β parallel β
ββββββββ΄ββββββββββ΄ββββββββββββ΄ββββββββββββββ΄βββββββββββββββββββββββββββββ