Querying JSON structures

Native JSON support in databases allows not only read and modify structured data, but also making queries. It is a very powerful feature - the queries use the same syntax and run in the same ACID transactional environment, in the same time offering NoSQL capabilities of a document store inside the relational database.

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:

# products with display size greater than 5
Product.select(lambda p: p.info['display']['size'] > 5)

In order to specify values you can use parameters:

x = 2048
# 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)
key = 'display'
Product.select(lambda p: p.info[key]['resolution'][index] > 1000)

Note

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

For JSON array you can calculate length:

# products with more than 2 tags
Product.select(lambda p: len(p.info['tags']) > 2)

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 True in the following cases:

• When the sub-element contains JSON null value

• When the sub-element does not exist

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

You can test JSON sub-element for truth value:

# products with multi-touch displays
select(p for p in Product if p.info['display']['multi-touch'])

In Python, the following values are treated as false for conditionals: None, 0, False, empty string, empty dict and empty list. Pony keeps this behavior for conditions applied for JSON structures. Also, if the JSON path is not found, it will be evaluated to false.

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’

Here is a query example:

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.