Ecto Fragments and the PostgreSQL JSON `?` operator

Questions & Help Questions
, ,
2

This does not answer the question I raised above, but I have found two workarounds for this using PostgreSQL. Both of these depend on an undocumented function (only the ? operator is documented) and a bit of digging to produce something similar as needed.

  1. Use the underlying function of ? directly. fragment("jsonb_exists(?, ?)", u.data, "honorific"). The functions for ?& and ?| are jsonb_exists_all and jsonb_exists_any, so those can be used as well (but note that the types are text[], so you would need to do fragment("jsonb_exists_any(?, ?)", u.data, ["honorific"])).

  2. Create a new operator analogous to ?, ?&, and ?| in your schema.

CREATE OPERATOR =~ (
  PROCEDURE=pg_catalog.jsonb_exists, LEFTARG=jsonb, RIGHTARG=text
);
CREATE OPERATOR =~& (
  PROCEDURE=pg_catalog.jsonb_exists_all, LEFTARG=jsonb, RIGHTARG=text[]
);
CREATE OPERATOR =~| (
  PROCEDURE=pg_catalog.jsonb_exists_any,LEFTARG=jsonb, RIGHTARG=text[]
);

Then you can construct these…

fragment("? =~ ?", u.data, "honorific")
fragment("? =~& ?", u.data, ["honorific"])
fragment("? =~| ?", u.data, ["honorific"])

Unfortunately, no one in the PostgreSQL community will understand these operators against json/jsonb types because you’ve created them, and you’re no longer writing standard PostgreSQL JSON queries. Unless you know these operators have been created, asking questions about these would cause confusion.