Pony allows selecting objects by filtering them by JSON sub-elements. To access JSON sub-element Pony constructs JSON path expression which then will be used inside a SQL query:

In order to specify values you can use parameters:

# products with width resolution greater or equal to x
Product.select(lambda p: p.info['display']['resolution'][0] >= x)

In MySQL, PostgreSQL, CockroachDB and SQLite it is also possible to use parameters inside JSON path expression:

index = 0
Product.select(lambda p: p.info['display']['resolution'][index] < 2000)

Note

Oracle does not support parameters inside JSON paths. With Oracle you can use constant keys only.

Another query example is checking if a string key is a part of a JSON dict or array:

# products which have the resolution specified
Product.select(lambda p: 'resolution' in p.info['display'])
# products of black color
Product.select(lambda p: 'Black' in p.info['colors'])

When you compare JSON sub-element with None, it will be evaluated to in the following cases:

Product.select(lambda p: p.info['SD card slot'] is None)

You can test JSON sub-element for truth value:

In previous examples we used JSON structures in query conditions. But it is also possible to retrieve JSON structures or extract its parts as the query result:

select(p.info['display'] for p in Product)

When retrieving JSON structures this way, they will not be linked to entity instances. This means that modification of such JSON structures will not be saved to the database. Pony tracks JSON changes only when you select an object and modify its attributes.

MySQL and Oracle allows using wildcards in JSON path. Pony support wildcards by using special syntax:

• […] means ‘any dictionary element’

• [:] means ‘any list item’

select(p.info['display'][...] for p in Product)

The result of such query will be an array of JSON sub-elements. With the current situation of JSON support in databases, the wildcards can be used only in the expression part of the generator expression.