Clojurians Log v2

Not much activity here but I’ll give it a shot anyway. I’m trying to write a PostgreSQL ORDER BY clause that orders by some data in a JSON column:

order by selections #>> array[?,'user-data','text']

I tried this but it doesn’t work at all:

:order-by [[:#>> :selections (honeysql.types/array [selectable-id "user-data" "text"])] :asc]

There's quite a bit of activity here -- but the free plan we're on limits visibility to 10,000 messages and there's usually well over 15,000 a week so you only ever see a few days of messages in any channel.

@jkrasnay Are you using the PostgreSQL-specific extensions library for HoneySQL, or just bare HoneySQL?

I suspect you'll need to use #sql/raw (`sql` = honeysql.types).

Thanks for the quick response! Just using HoneySQL. I did get the following working:

:order-by [[(sql/raw ["selections #>> array[" (sql/param selectable-id) ",'user-data','text']"])]]

Sadly, selectable-id is a string so I have to supply it separately to format instead of it being carried around in the SQL map. Is there some way to lift that parameter out of the raw in the same way, say, an integer would be?

Not yet. It's an open issue.

I'm not very happy with sql/raw but I haven't yet figured out a better solution that deals with the corner cases, since I took over the library.

OK, thanks for your time

@jkrasnay Does this work for you?

=> (defmethod sqlf/fn-handler "#>>"
     [_ obj path]
     (format "%s#>>%s" (sqlf/to-sql obj) (sqlf/to-sql (sqlt/array path))))

=> (sql/format {:order-by [[(sql/call :#>> :payload ["a" "b"]) :asc]]})
["ORDER BY payload#>>ARRAY[?, ?] ASC" "a" "b"]

sqlf = honeysql.format, sql = honeysql.core, sqlt = honeysql.types