Extending HoneySQL with JSON operators

HoneySQL is a wonderful Clojure library that lets use the full power of Clojure collections to generate your SQL statements. Postgres supports json operators and data types, which is one of the main features for which I use it; however, since this is a non-SQL-standard feature, HoneySQL doesn’t support it directly, and I didn’t see that support in HoneySQL-Postgres yet. Fortunately, HoneySQL makes it easy to extend this way.

(ns myapp.db.core
  (:require
   [honeysql.core :as sql]))

(defn pg->
  "Postgres json -> operator"
  [parent fieldkey]
  (sql/raw (str (name parent) "->'" (name fieldkey) "'")))

(defn pg->>
  "Postgres json ->> operator"
  [parent fieldkey]
  (sql/raw (str (name parent) "->>'" (name fieldkey) "'")))

;; other ns
(defn mail-notifications-for-user 
  "retrieve all mail approval requests notifications for `user` and application `application-id` "
  [user-id application-id]
  (let [email-addresses (map :email (email-addresses-for-user user-id))]
    (-> {:select [:*]
     :from [:email-log]
     :where [:and
             (db/pg-intersect-arrays :recipients email-addresses) ;; see my other blog post on this extension
             [:= (db/pg->> :info :application-id) (str application-id)]
             [:= (db/pg->> :info :type) (MESSAGE-TYPES :request)]]}
    sql/format
    db/dbr)))

;; produces a query that is filtering off of the info-field (jsonb) :application-id and :type object-fields

The last thing to note is that, as far as I can tell, the only options in Postgres when handling JSON is to use the -> operator to get back another json object, or the ->> operator to get back text; that’s why my application-id in the code above needs to be made from an integer into a string, else the PG errors that it can’t compare integers (my value) to strings (what it turns out the -» returns) or to json (what I get if I use -> instead of ->>).