i’m trying to write this query:

SELECT c.id, c.name,
       JSONB_AGG(JSONB_BUILD_OBJECT('id', ce.expertise_id,
                                    'years', ce.experience_years)) AS "expertises"
  FROM candidates AS C
       LEFT JOIN candidate_expertises AS ce ON c.id = ce.candidate_id
 GROUP BY c.id

.. (:require    [honey.sql :as sql2]
                     [honey.sql.helpers :as sqlh2])..

(-> (sqlh2/select
        :c.id
        :c.name
        [[:jsonb_agg [:jsonb_build_object
                      "id" :ce.expertise_id
                      "experience_years" :ce.experience_years]] "expertises"]
        )
      (sqlh2/from [:candidates :c])
      (sqlh2/left-join [:candidate-expertises :ce] [:= :c.id :ce.candidate-id])
      (sqlh2/group-by :c.id))

["SELECT c.id, c.name, 
JSONB_AGG(JSONB_BUILD_OBJECT(?, ce.expertise_id, ?, ce.experience_years)) AS \"expertises\" 
FROM candidates AS c 
LEFT JOIN candidate_expertises AS ce ON c.id = ce.candidate_id 
GROUP BY c.id"
 "id"
 "experience_years"]

Assert failed: Alias should have two parts[:jsonb_build_object "id"
   :ce.expertise_id "experience_years" :ce.experience_years] (= 2 (count x))

solved it: i used our helper for execute, which called format from honey v1 🙃

but i still want to figure out if it’s possible to solve this with honey v1.0.461 :

.. (:require    [honeysql.core :as sql]   [honeysql.helpers :as sqlh]) .. 

(-> (sqlh/select
          :c.id
          :c.name
          [[:jsonb_agg [:jsonb_build_object
                        "id" :ce.expertise_id
                        "experience_years" :ce.experience_years]] "expertises"]
          )
        (sqlh/from [:candidates :c])
        (sqlh/left-join [:candidate-expertises :ce] [:= :c.id :ce.candidate-id])
        (sqlh/group :c.id)
        (sql/format))

In v1, you almost certainly need sql/call to identify the function calls in the select.

thanks a lot, Sean! it seems to be working with:

..
[(sql/call
            "jsonb_agg"
            (sql/call "jsonb_build_object"
                      "id" :ce.expertise_id
                      "years" :ce.experience_years)) "expertises"] ..