Issue: Extending JSON queries / building expression with JSON keys
Author: tuukkamustonen
State: closed
Created: 2017-12-05 14:07:08

I'm trying to query by single JSON field value with pattern matching (e.g. `ILIKE "foo*"`), something like:

```python
class BaseModel(peewee.Model):
    class Meta(object):
        database = db

class Data(BaseModel):
    notes = BinaryJsonField()

# Create tables and indexes...

Data.create(notes={'org_path': '1/1/1'})
Data.create(notes={'org_path': '1/2/1'})
Data.create(notes={'org_path': '1/2/2'})
```

Trying to query them:

```python
Data.select().where(SQL("Data.notes->>'org_path' LIKE '%s'", '1/2/*'))
```

...because peewee maps `Data` table name as `t1` in query. If I manually write `t1` in there, it works. Is it possible to grab `t1` somehow into `SQL(...)` or how should I tackle this? Use `alias()` or something?

Also tried:

```python
Data.select().where(SQL("%s.notes->>'org_path' LIKE '%s'", '1/2/*', Data))

# fails into:
ProgrammingError: can't adapt type 'BaseModel'
```

So maybe:

```python
Data.select().where(SQL("%s->>'org_path' LIKE '%s'", '1/2/*', Data.notes))

# fails into:
ProgrammingError: can't adapt type 'BinaryJSONField'
```

Also, to make these queries more "formal" I would like to build `Expression`s instead. For example, `BinaryJsonField#contains()` returns:

```python
Expression(self, OP.JSONB_EXISTS, Passthrough(other))
```

It would be easy to just change the operator, but I need to make `lhs`(`self`) map into `Data.notes->>'org_path'` and I'm not sure how/where to do that. Can I use `Data.notes['org_path']` somehow...?

You *should* just be able to write:

```python
Data.select().where(Data.notes['org_path'] % '1/2/*')
# ('SELECT "t1"."id", "t1"."notes" FROM "data" AS "t1" WHERE ("t1"."notes"->>%s LIKE %s)',
# ['org_chart', '1/2/*'])
```

Uh, I'll try that. What how about _if_ I actually needed to build something more complex, where I would really face the problems in the original message?

Can you give an example of a more complex query?

Maybe using some more exotic json operators (or combination) from https://www.postgresql.org/docs/current/static/functions-json.html. I would assume not everything is supported out-of-box?

*Most* of that stuff actually is supported, although there are probably some exceptions.

To your question, though... The `SQL()` constructor doesn't know about converting models or fields into quoted names. It treats everything after the SQL string itself as a parameter of the query.

If you wanted to dynamically construct a query, you might do something like:

```python
# 2.x
exp = Clause(Data.notes, SQL('->>"foobar" LIKE %s', '1/2/*'), glue='')

# 3.x
exp = NodeList([
  Data.notes, 
  SQL('->>"foobar" LIKE %s', ('1/2/*',))
], glue='')

# either
Data.select().where(exp)
```

Yeah, I see them now in `PostgresqlExtCompiler`. Most of the stuff that is not implemented is probably not needed after all (concat `||` and deletions `-`/`#-`).

None of the [JSON Processing Functions](https://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE) are supported, though? Some of those could be useful sometimes (though I'm not sure when/how).

They are, just use `fn.json_function_name(...)`.

Of course. Thanks again. `peewee` is awesome.