Extending HoneySQL with Array Intersection

Table of Contents

Turns out that Postgres has beautiful support for “array” field types. In one database of mine, I use this to keep track of the recipients of emails:

DROP TABLE IF EXISTS "email_log" CASCADE;
CREATE TABLE "email_log" (
  id SERIAL PRIMARY KEY,
  sender TEXT NOT NULL,
  recipients TEXT[] NOT NULL,
  message TEXT,
  sent_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  info JSONB);
-- Note the recipients TEXT[] definition: that's an array of texts. 

Now for the use-case: a user might have multiple email addresses, and the email_log tracks recipient ADDRESSES not user-ids. My task is to retrieve all emails whose recipients are any of my user’s email addresses. Postgres supports array-intersection logic, and HoneySQL is easy to extend to build me the type of query I need:

(defn pg-intersect-arrays
  "Determine whether a Postgres array field interesects a provided collection"
  [db-array-field coll]
  (let [stringy-coll (map #(str "'" % "'") coll) 
;; Strings need to be single-quoted in this postgres syntax,
;; and comma-separated within brackets (next line)
        array-format (str "["
                          (clojure.string/join "," stringy-coll)
                          "]")]
    (sql/raw (str (name db-array-field) " && ARRAY" array-format))))
;; Note the && operator, Postgres for "array intersection"

Then I use it (also using a json operator from another HoneySQL/Postgres blog post of mine) and obtain messages from my email log that include ANY of the user’s email addresses among the recipients array in the database:

(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)
                 [:= (db/pg->> :info :application-id) (str application-id)]
                 [:= (db/pg->> :info :type) (MESSAGE-TYPES :request)]]}
        sql/format
        db/dbr)))
Tory Anderson avatar
Tory Anderson
Full-time Web App Engineer, Digital Humanist, Researcher, Computer Psychologist