luckyframework / luckyframework/avram

Can't use special JSON operators with raw SQL

Open
#939 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
Crystal
Stars
183
Forks
67
PR merge metrics
No merged PRs in 30d

Description

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

```sql
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 `?`

```sql
'{"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.

```crystal
# 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...

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start at src/avram/where.cr#L265 and trace how raw where queries count bind operators. Compare the examples using PostgreSQL JSON operators with the example that supplies bind arguments, then verify that operator syntax no longer causes an incorrect bind-variable error while actual bind arguments still behave correctly.

Written by the indexing model from the issue text.

Assessment

Tech stack
crystal, postgresql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.