halostatue

Ecto Fragments and the PostgreSQL JSON `?` operator

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                   │
└──────┴─────────┴───────────┴─────────────┴────────────────────────────┘

7 comments

#ecto #jsonb #postgresql

3 6555 7

2018-08-13 04:18:04 UTC

Marked As Solved

michalmuskala

You can escape the question mark with \, but you need it doubled - once for string and once for fragment. This should work fine:

from u in :users, where: fragment("? \\? ?", u.data, "honorific")

Post #5

Also Liked

NobbZ

Instead of "? \\? ?" one can also use ~S"? \? ?", which removes one layer of escaping.

Post #6

halostatue

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.

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"])).
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.

Post #1