Fork me on GitHub
#honeysql
<
2024-04-03
>
igrishaev13:04:25

How can I build a where clause that involves the @? operator for jsonb path? What I'm doing is:

(sql/register-op! (keyword "@?"))

(sql/format [(keyword "@?") 
               :entity 
               "$.foo.bar[*].id ? (@ == 42)"]
              {:numbered true})

["entity @?? $1" "$.foo.bar[*].id ? (@ == 42)"]
But I'm getting @?? for some reason. Is it because of extra quoting of question marks or something?

kolstae13:04:46

Could be down to what you pass into sql/format - that's not a valid honeysql query I think. Try using it in a valid query

igrishaev13:04:39

It still doesn't work for me:

(sql/format {:select 42 :where [:and [honey.sql.pg-ops/at? "aa" "bb"]]})
["SELECT ? WHERE (? @?? ?)" 42 "aa" "bb"]

igrishaev13:04:07

the honey.sql.pg-ops/at? variable is just (keyword "@?") as I did before

💯 1
igrishaev13:04:40

Ah! The same issue is in the tests:

kolstae13:04:33

Yeah sorry I couldn't make it work either I just assumed 🙈

valerauko13:04:49

:@? (or for that matter any keyword that starts with @ ) isn't really valid it just happens to work sometimes 😞 i've never used jsonb through honeysql but if you can use string operators that should be way safer

igrishaev13:04:40

sorry @UAEH11THP what do you mean by "string operators"?

igrishaev13:04:17

Yeah I'm trying to build a raw expression. But I don't want to put the jsonpath inline. It might lead to injections.

kolstae13:04:24

Seems :raw still supports params though:

(sql/format {:select [:a [[:raw ["@var := " ["foo"]]]]]})
;;=> ["SELECT a, @var := (?)" "foo"]

igrishaev13:04:24

option one with params

(sql/format {:select [:a [[:raw ["entity @? " [:param :path]]]]]}
              {:numbered true
               :params {:path "$.foo.bar"}})
option two with inline
(sql/format [:raw "entity @?" \space [:inline "$.foo.bar[*].id ? (@ == 42)"]])
["entity @? '$.foo.bar[*].id ? (@ == 42)'"]

p-himik14:04:55

FWIW I myself would probably use jsonb_path_exists with silent set to TRUE till the issue with @? (and, seemingly, any ops containing ?) is fixed.

igrishaev14:04:32

well, the jsonb_path_* functions do not work with my index which is USING GIN (entity jsonb_path_ops)

👍 1
seancorfield17:04:07

I was under the impression that all SQL dialects always treated ?? as an escaped ? and therefore @?? was correct (since I don't use PostgreSQL). Thanks for the issue -- will get it addressed shortly.