Clojurians Log v2

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.

Exactly, this is a simplified code fragment:

(let [json-path-map
      (build-from-dsl ...)
      
      [jsonpath]
      (honey.sql/format json-path-map)

      top-map
      {:select ...
       :from ...
       :where [pg/atat :entity [:inline jsonpath]]}]

  (honey.sql/format top-map))

OK, so either an option or a dyn var to disable the escaping would work for you? Can you create an issue on GH and I'll look at that for the next release (which will be fairly soon, I expect, since I just made a new release tonight)

Sure, here it is: https://github.com/seancorfield/honeysql/issues/533 Thank you for that amazing lib, it's helped me more times than I can remember!

Shouldn't that be considered abusing HoneySQL? I mean, jsonpath is not SQL. Nor is xpath or regex or anything else that PostgreSQL supports and that has to be provided as a plain string.

Yeah I agree it’s some sort of abusing. That’s why I’m not forcing this.

Right, it's more of a question for @seancorfield. :) Even though he's the maintainer, I'm still a bit worried about scope creep and all the negatives it entails.

user=> (require '[honey.sql :as sql] 'honey.sql.pg-ops)
nil
user=> (sql/format {:select [[[:? 13 42]]]})
["SELECT ? ?? ?" 13 42]
user=> (sql/format {:select [[[:? 13 42]]]} {:inline true})
["SELECT 13 ?? 42"]
user=> (binding [sql/*escape-?* false] (sql/format {:select [[[:? 13 42]]]} {:inline true}))
["SELECT 13 ? 42"]
user=> 

It'll be a while before I cut a new release, so you'll have to use a git dep for now (or the SNAPSHOT that should be deployed to Clojars in a few minutes).

Thank you a lot!

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