Add PostgreSQL JSON operators by default?

[seancorfield]

->, ->>, and various others are a bit of a pain to use without registering them all or using :raw.

[introom]

The docs here at postgrestutorial explains the -> and ->> operator in a really concise way:
https://www.postgresqltutorial.com/postgresql-json/

[seancorfield]

https://www.postgresql.org/docs/current/functions-json.html lists a lot of operators, including several that cannot be written directly as Clojure keywords (or symbols) so I'll need a convenient way for PG users to get at these using symbolic names.

I think I might add a new honey.sql.pg-json namespace that not only registers the myriad operators but also provides nice, symbolic names for several of them as vars.

[seancorfield]

@introom If you work with develop version and require honey.sql.pg-json you should find all the PG JSON operators defined automatically. I need to document all this new stuff.

[introom]

Hi @seancorfield , thanks for the update! Minor question, why honey.sql.pg-json instead of honey.sq.pg ? pg-json sounds too fine-grained.

[seancorfield]

Because most PostgreSQL stuff is already supported directly. This new namespace is specifically because there are JSONB operators that have "unwritable" names -- and I don't want those four names in honey.sql since they are a PG-specific workaround for JSON operators, i.e., not general PostgreSQL stuff.

[dekelpilli]

Thanks for this, this will be very convenient and help reduce some boilerplate code for PG JSON(B) operations.

Just a heads up, :-> should probably be variadic (to support [:-> :column_name "top-level-key" "nested-key" "etc-key"])

[seancorfield]

Ah, good point @dekelpilli - thank you! I'll update that shortly. Should any of the others be variadic? (I can't really tell from the docs)

[seancorfield]

Looks like || should probably be variadic as well? Not many operators seem to return json(b) where I'd expect the same operator to be repeated?

[seancorfield]

I'm coming around to having vars in honey.sql.pg-json for all of the ops, even though only four of them really need it. I'll have to think carefully about docstrings and overall documentation enhancements.

[seancorfield]

It turns out that #>, #>>, and #- all need a name without the # so for now I'm using hash for #.

[holyjak]

This is unrelated to json but what about the regexp operators ~* and ~? Currently we do

(def regex "Keyword corresponding to regex matching operation"  (keyword "~"))
(def iregex "Keyword corresponding to case-insensitive regex matching operation"  (keyword "~*"))
(honey/register-op! regex)
(honey/register-op! iregex)

[seancorfield]

I'm adding the four regex operators (!~ and !~* as well as the two @holyjak mentioned).

Accordingly, I'm renaming the namespace to honey.sql.pg-ops since it is a general PostgreSQL operators utility now.