Can't use special JSON operators with raw SQL

[jwoertink]

If you want to use some special JSON operators https://www.postgresql.org/docs/14/functions-json.html like

metadata->'value' ?| array[1, 2]

The code will assume the ? is a bind operator and fail. In this case, we can't even ignore ?| because another operator is just ?

'{"a":1, "b":2}'::jsonb ? 'b' → t

https://github.com/luckyframework/avram/blob/376379a54ca115395e1b7eaf2afeeef86720d469/src/avram/where.cr#L265

I think as a temporary "hack" we could say that if you don't pass any bind args to a raw where query, then don't try to count the bind args. The raw where is an escape hatch anyway, so it'll be up to you to handle.

# this is ok because there's no args
where("metadata->'value' ?| array[1, 2]")

# this throws exception wrong number of bind variables
where("metadata->'value' ?| ?", [1, 2])

It's not great, and I don't love it, but aside from changing what the bind args are, I'm not sure how else to fix this...

[jwoertink]

One suggestion @watzon brought up was to add in named keys. I believe this is what Rails does.

The idea would be that you would say "oh, my SQL needs to use ? so I better pass in named keys", so you would write the query like this:

where("metadata->'value' ?| :data", data: [1, 2])

[jwoertink]

Related: https://github.com/crystal-lang/crystal-db/pull/207

[bcardiff]

Two alternatives that are not subject to heuristics would be:

1. To prepare the statement and check the parameter types.

prepare temp_1 as select * from table where name like $1;

select parameter_types from pg_prepared_statements where name = 'temp_1'

that will tell you if the prepared statement needs arguments (and their types or not). It requires a server running thou.

2. See if https://github.com/pganalyze/pg_query is able to provide enough information by parsing the query only. I believe there are no bindings yet for crystal to that C library.

[jwoertink]

Oh cool. Thanks for the suggestions :raised_hands: