(defn join-account-id [base-query account-id]
  (if account-id
    (merge base-query
           {:join [[:account :a] [:and
                                  [:= :pl.location_id [:cast [:raw "a.con->>'location_id'"] :bigint]]
                                  [:= :pg.group_id [:cast [:raw "a.con->>'group_id'"] :bigint]]]]})
    base-query))

(defn raw-query-gen [i-key db-function-to-use]
  (sql/format {:raw (str db-function-to-use "('" i-key "', now() - interval '24 hours')")}))

(defn fetch-roster-query [i-key account-id db-function-to-use]
  (-> {:select [:p.*]
       :from [[:person :p]]
       :where [:= :p.instance i-key]
       :left-join [[:person_location :pl] [:= :p.location_id :pl.location_id]
                   [:person_department :pd] [:= :p.department_id :pd.department_id]
                   [:person_group :pg] [:= :p.group_id :pg.group_id]
                   [[:raw (raw-query-gen i-key db-function-to-use)]  :person_result]
                   [:= :p.person_id :person_result.person_id]
                   [:result :r] [:and [:= :person_result.result_id :r.result_id]
                                 [:= :r.is_excluded [:raw "'FALSE'"]]]]
       :group-by [:p.person_id :pl.name :pd.name :pg.name]}
      (join-account-id account-id)
      (sql/format :quoting :ansi)))

(defn fetch-roster [i-key account-id db-function-to-use]
  (jdbc/execute-one! @db/data-source
                     (fetch-roster-query i-key account-id db-function-to-use)
                     {:builder-fn result-set/as-unqualified-maps
                      :return-keys true}))

(comment

 (fetch-roster "TEST" true "results_for_instance_v2")
  ;; => Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2510).
  ;;    ERROR: missing FROM-clause entry for table "pl"
  ;;      Position: 57


  (fetch-roster "TEST" false "results_for_instance_v2")
  ;; => {:identity {:first-name "first1", :last-name "last1", :employee-id 1}, :instance "TEST", :location_id 1, :person_id 1, :group_id 1, :custom_data {:surname "Wow", :test "Name"}, :last_daily_screening_reminder nil, :old_id nil, :department_id 1, :created_at #time/instant "2022-02-16T14:40:25.314411Z"}



 (fetch-roster-query "TEST" true "results_for_instance_v2")
 ;; => ["SELECT p.* FROM person AS p INNER JOIN account AS a ON (pl.location_id = CAST(a.con->>'location_id' AS bigint)) AND (pg.group_id = CAST(a.con->>'group_id' AS bigint)) LEFT JOIN person_location AS pl ON p.location_id = pl.location_id LEFT JOIN person_department AS pd ON p.department_id = pd.department_id LEFT JOIN person_group AS pg ON p.group_id = pg.group_id LEFT JOIN results_for_instance_v2('TEST', now() - interval '24 hours') AS person_result ON p.person_id = person_result.person_id LEFT JOIN result AS r ON (person_result.result_id = r.result_id) AND (r.is_excluded = 'FALSE') WHERE p.instance = ? GROUP BY p.person_id, pl.name, pd.name, pg.name" "TEST"]

)

whenever i execute the query with account_id = true, it fails with below error

;; => Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2510).
  ;;    ERROR: missing FROM-clause entry for table "pl"
  ;;      Position: 57

See https://cljdoc.org/d/com.github.seancorfield/honeysql/2.2.868/doc/getting-started/sql-clause-reference#join-by

(as always, I highly recommend reading the docs from cover-to-cover -- nearly every question asked about HoneySQL these days is answered in the docs)

sure, checking it again.

thank you @U04V70XH6 i did missed reading those. Have to make some more changes for it to work on my query.

(defn join-account-id [base-query account-id]
  (if account-id
    (update-in base-query [:join-by] conj
           [:join [[:account :a] [:and
                                  [:= :pl.location_id [:cast [:raw "a.con->>'location_id'"] :bigint]]
                                  [:= :pg.group_id [:cast [:raw "a.con->>'group_id'"] :bigint]]]]])
    base-query))

(defn raw-query-gen [i-key db-function-to-use]
  (sql/format {:raw (str db-function-to-use "('" i-key "', now() - interval '24 hours')")}))

(defn fetch-roster-query [i-key account-id db-function-to-use]
  (-> {:select [:p.*]
       :from [[:person :p]]
       :where [:= :p.instance i-key]
       :join-by [:left [[:person_location :pl] [:= :p.location_id :pl.location_id]
                          [:person_department :pd] [:= :p.department_id :pd.department_id]
                          [:person_group :pg] [:= :p.group_id :pg.group_id]
                          [[:raw (raw-query-gen i-key db-function-to-use)]  :person_result]
                          [:= :p.person_id :person_result.person_id]
                          [:result :r] [:and [:= :person_result.result_id :r.result_id]
                                        [:= :r.is_excluded [:raw "'FALSE'"]]]]]
       :group-by [:p.person_id :pl.name :pd.name :pg.name]}
      (join-account-id account-id)
      (sql/format :quoting :ansi)))

I added a link to the https://www.postgresqltutorial.com/postgresql-json/ in the issue

FWIW recent Postgres versions also have functions to substitute most of these operators.

@U7PBP4UVA Do you know what’s the equivalent function of -> ?

I would say jsonb_extract_path ?

I've used extensively the jsonpath support recently.