Clojurians Log v2
honeysql
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)"]@?? for some reason. Is it because of extra quoting of question marks or something?Dear @seancorfield I’m asking your forgiveness. You were right about double ??. The thing is, I was experimenting with honeysql in repl and then copied queries to psql. That’s why it didn’t like ??. But in JDBC , an extra ? is required. Can you please rollback the last PR? My shame.
No problem. The changes for #528 have been reverted.
Thank you! Again, sorry for the false alert.
Have you seen https://cljdoc.org/d/com.github.seancorfield/honeysql/2.6.1126/api/honey.sql.pg-ops#at??
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
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"]the honey.sql.pg-ops/at? variable is just (keyword "@?") as I did before
Ah! The same issue is in the tests:
Yeah sorry I couldn't make it work either I just assumed 🙈
:@? (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
sorry @vale what do you mean by "string operators"?
There's always :raw if you need it https://cljdoc.org/d/com.github.seancorfield/honeysql/2.6.1126/doc/getting-started/sql-special-syntax-?q=%3Araw#raw
Yeah I'm trying to build a raw expression. But I don't want to put the jsonpath inline. It might lead to injections.
Seems :raw still supports params though:
(sql/format {:select [:a [[:raw ["@var := " ["foo"]]]]]})
;;=> ["SELECT a, @var := (?)" "foo"]option one with params
(sql/format {:select [:a [[:raw ["entity @? " [:param :path]]]]]}
{:numbered true
:params {:path "$.foo.bar"}})(sql/format [:raw "entity @?" \space [:inline "$.foo.bar[*].id ? (@ == 42)"]])
["entity @? '$.foo.bar[*].id ? (@ == 42)'"]
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.
well, the jsonb_path_* functions do not work with my index which is USING GIN (entity jsonb_path_ops)
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.
