I've figured out with this:

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

[(keyword "") field [:raw "?"] fields]

field  ?  ...

JDBC expects ?? and turns it back to ? as part of parameter substitution.

(this has come up before)

The honey.sql.pg-ops ns defines ? as a symbol that you can use instead of :? if you want (that ns exists mostly for the ops that cannot be represented as keyword literals in Clojure).

So, the answer to your question is: yes, the better solution is just to use :? 🙂

Can you explain why ?? is not an option for you? Are you using HoneySQL to generate SQL that you are using somewhere other than JDBC?

yeah I remember that issue with ?? and ? which I rose by mistake. I'll take a look at the pg-ops, thank you! I'm building a complex JSONpath expression. It's an internal sublanguage in Postgres to query JSONb blobs. It has @@ , @? and ? operatos

But no, the ps-ops namespace didn't work. It still generates ??

Just for the reference: https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-PATH

> It still generates ?? Yes, of course, because JDBC requires ?? -- ? must be escaped.

Again, can you explain why -- for JDBC -- you think you need a single ? here and not the correctly-escaped ?? version? Are you getting an error from JDBC with ?? or are you trying to use the generated SQL in a non-JDBC context?

Because JSONpath is a special language that doesn't accept JDBC parameters. Let me find an example...

select id from my_table
where
   entity @@ '(
               $.attrs."foo-bar-3-test-name" == "Aaa"
            && !($.attrs."foo-aaa-1-hello-name" == "XXX")
            && exists($.attrs."asset-class"."some-class-code" ? ((@ == "17" || @ == "22")))
            && (
                (   $.attrs."foo-first-name" == "Hello"
                 && $.attrs."fo-last-name" == "Some Value")
                ||
                (   $.attrs."bar-first-name" == "Hello"
                 && $.attrs."bar-last-name" == "Some Values")))'
limit 10;

The expression after @@ is JSONpath. It's a string which is processed by Postgres internally when querying JSONb values. This expression doesn't capture JDBC parameters. It may accept a JSON map of its own parameters though. The thing is, my query is pretty simple but the JSONpath expression is quite complex, and I've got to build it out from a so-called "DSL" which was used for another datastore. I HoneySQL because it can build JSONpath pretty well. The only issue I have is this ? operator which, in JSONpath, means checking a certain path. But with the workaround that I shared above, it works.

Briefly, first I build a data structure for JSONpath, then bake it into a string, and then build the final query passing that path inline.

You're producing the JSONpath separately and then dropping it :inline into the SQL?

(I'm actually a bit shocked you're able to use HoneySQL to generate even close to that!)

If you can explain a bit more, with code, about how exactly you're doing this, I may be able to come up with a solution... maybe a public but undocumented dynamic var that you could use to turn off the ? escaping in the few places where it happens?

Well, there is nothing special here, really. I'm receiving a DSL like

[:and [:= :some.attr.name "test"] 
      [:wildcard :some.other.field "hello"]
      [:or [:gte :some.attr.age 42]
           [:in :attr.status ["created" "pending"]]]]

[:&& [:= [:. :some :attr :name] [:raw (json/encode "test")]]
      [:|| ...]
      ]

$.foo.bar like_regex "pattern" flag "iq"

Here is an example of generating

... exists($.foo.bar ? (@.id == 1 || @.id == 2, ...))

:predicate-in
      (let [{:keys [attr value]}
            content

            field
            (-> attr
                attr->path
                path->$attr)

            fields
            (into [:||]
                  (for [v value]
                    [:== (keyword "@") [:raw (util/to-json v)]]))]

        [:exists [(keyword "") field [:raw "?"] fields]])

And how do you combine this into the main SQL part, in HoneySQL?

It sounds like you call into HoneySQL once for the JSONpath part and then a second time to generate the SQL with the SQL from the JSONpath part as an inlined string? That's what I'm trying to understand.

By the way, there is a ? operator for Postgres as well, not only for JSON path. For example, to ensure that the lefthand JSON includes the righthand one:

select '{"foo": 123}'::jsonb ? 'foo';

Right, but PG JDBC accepts ?? there and still treats it as the ? operator.

ah you're right. It failed before with JSONpath because it was inside 'quotes'

but maybe I'm mixing something...

For SQL, ?? works in JDBC (for PG). For JSONpath, yes, that's a "string" so you need ? there instead of ?? I suspect (which is what triggered all of this).

Anyway, you have an (undocumented) option to suppress ?? escaping so you can experiment 🙂

ok, sorry for confusion