honeysql 2024-05-08 | Slack Archive

Hi there, how can I format an insert-into when the values have nested maps or vectors that would be stored as jsonb?

(let [data [{:jsonb-column {:a 1 :b 2}
               :jsonb-vector [1 2]
               :text "text"}]]
    (honeysql2/format {:insert-into :table
                       :values      data}))

Miguel10:05:30

In contrast if I use next.jdbc directly it works, but my use case requires special handling of on-conflict so I cant go that way.

(next.jdbc.sql/insert! tx :table {:jsonb-column {:a 1 :b 2}
                                        :jsonb-vector [1 2]
                                        :text         "text"})

p-himik10:05:42

:lift should work, assuming that whatever prepares the query knows how to convert from maps and vectors to SQL values:

(let [data [{:jsonb-column [:lift {:a 1 :b 2}]
             :jsonb-vector [:lift [1 2]]
             :text         "text"}]]
  (sql/format {:insert-into :table
               :values      data}))
=> ["INSERT INTO table (jsonb_column, jsonb_vector, text) VALUES (?, ?, ?)" {:a 1, :b 2} [1 2] "text"]

Miguel10:05:44

Of course, it's that easy. Well, I'm never getting these two hours back. Thank you so much 🙏

p-himik10:05:02

I usually recommend asking others when you're stuck for 15-30 minutes. :)

Miguel10:05:22

Lesson learned

seancorfield15:05:21

The next version of HoneySQL will give a better error message for this case, and the documentation will have several more mentions of :lift to guide people to using it.

🙌 1

Noah Bogart19:05:58

Is it possible to edit a :select statement? i want to do a "select-keys" on a select to only use certain ones by their column or alias name instead of having to retype the subset

seancorfield19:05:05

Well, it's "just data" so yeah... (update dsl :select #(filterv #{:columns :to :keep} %)) or something like that? Bear in mind that :select can also take arbitrary expressions so it would need to be smarter than that in general.

Noah Bogart19:05:46

yeah, the arbitrary expression part is what i'm struggling with lol. i was hoping someone had already figured that out for me but that's okay, i can hack something together

seancorfield19:05:15

And also you can have symbols or keywords -- so it's going to depend on your hygiene in building the selection.

seancorfield19:05:01

(defn col-or-alias [select-item] (if (keyword? select-item) select-item (second select-item))) -- that should get you the column or alias name in most cases...

seancorfield19:05:02

(update dsl :select #(filterv (comp #{:columns :to :keep} col-or-alias) %)) -- you still have to specify the set of columns/aliases you want to keep.

👍 1

Noah Bogart19:05:30

ah thanks, that looks close enough for my use case

2024-05-08

Channels