My colleague @holyjak just discovered (for us) unnest (https://blog.jakubholy.net/2023/pg-select-where-match-on-list-of-tuples/) Is there a way to express this in honey?

👋 How do I write JOIN UNNEST(?, ?) AS as x(ws,t) with honey? Currently I cheat with [[:unnest "arg1" "arg2"] [[:raw "as x(ws,t)"]]] . I see that also [... [[:x :ws :t]]] works, with the disadvantage that it ends up as AS X(ws, t) , i.e. a capital X. This will also answer Slipset ☝️

unnest and alias syntax look like function call, and honeysql is happy to generate it. Although I don't know if this case is officially supported

(let [id+ver-pairs [['a' 1] ['b' 20] ['c' 1]]
      ids (map first id+ver-pairs)
      versions (map last id+ver-pairs)]
    (->> {:select :*
          :from [[:thing :t]]
          :join [[[:unnest [:array ids] [:array versions]] [[:x :id :version]]]
                 [:and [:= :x.id :w.id] [:= :x.name :w.name]]]}
         (next.jdbc/execute! conn)))

Yes, that is what I am mentioning above. The only disadvantage is that it ends up capitalizing the alias, so as X(ws,t) in my case. But since it is unquoted then its case does not matter, and in honey-generated quoted result "x"."t" will work.

Cool approach. I've been using I've been using json_to_recordset for that instead (so you just pass json array of objects (e.g., join json_to_recordset(?::json) as b(id int, state int) on a.id = b.id and ...) ). But I assume your way is more efficient.

You can use :'x to keep x as lowercase. Example in 🧵 (and maybe in future, can folks use threads for follow-ups/replies please)