Ecto Fragments and the PostgreSQL JSON `?` operator

Questions / Help
, ,
1

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

3 Likes
3

Third post on this for those following along, how I figured all of this out. I’ve been playing in pg_catalog recently because I’ve been building some tooling to automate the construction of monthly table partitioning in PostgreSQL 9.6, so I’m as apt to dig into this now as ever.

If you need to figure out how to do this yourself, psql -E <database> is your friend, because when you run something like \doS (describe operators in all schemas), it will output a query like the one below (the one below is one I copied and modified to find all operators with a question mark in them for the table in the first post).

SELECT n.nspname as "Schema", o.oprname AS "Name",
       CASE WHEN o.oprkind = 'l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS "Left arg type",
       CASE WHEN o.oprkind = 'r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS "Right arg type",
       pg_catalog.format_type(o.oprresult, NULL) AS "Result type",
       coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'), pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS "Description"
  FROM pg_catalog.pg_operator o
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace
 WHERE o.oprname OPERATOR(pg_catalog.~) '^([?])$'
   AND pg_catalog.pg_operator_is_visible(o.oid)
 ORDER BY 1, 2, 3, 4;

This will tell you the argument types for your aliased operator. In our case, left is jsonb and right is text. It still doesn’t tell you which function to use. For that, you need to look at pg_operator directly:

SELECT oprname, oprcode FROM pg_operator WHERE oprname = '?';

This gives us the answer that the ? operator uses jsonb_exists. If the function/procedure you’re using does not exist in the same schema as the operator you’re defining (it probably doesn’t in this case), you must fully qualify it to pg_catalog.jsonb_exists.

This tells us that our CREATE OPERATOR statement needs to look like:

CREATE OPERATOR =~ (
  PROCEDURE=pg_catalog.jsonb_exists, LEFTARG=jsonb, RIGHTARG=text
);

Be careful about overloading, as overloads require different types. You can qualify an operator by using the OPERATOR() function in a query (seen above in the introspection query):

fragment("? OPERATOR(pg_catalog.=~) ?", u.name, "Michal")

If you wish to drop an operator, you must know the arg types as well.

DROP OPERATOR =~(jsonb, text);
1 Like
4

In my experience, I just dropped any attempts to use ECTO syntax and gone straight to Postgres JSON/B “normal queries”.

What are your thoughts regarding this direct approach?

PS: I use extensively Postgres advanced functions for JSON/B.

5

The need to query into the JSONB is relatively rare for my applications, and I don’t recall what made me start looking at this particular issue. Most of my needs are met by straight queries, and on the rare occasion I need to query into JSONB it’s better to just use fragment/1, which is why I was curious.

1 Like
6

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")
16 Likes
7

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

5 Likes
8

I should have tried that. This might be worth putting in the Ecto documentation.

2 Likes