python / expert
Snippet
Extending ORM Query Capabilities with Custom Postgres JSONB Array Lookups
Subclassing Django ORM Lookup empowers developers to register raw database operators like PostgreSQL ?| into field filter syntax across ORM querysets.
snippet.py
python
1
2
3
4
5
6
7
8
9
10
11
12
from django.db.models import Lookupfrom django.db.models.fields import Fieldclass JSONHasAnyKey(Lookup):lookup_name = 'has_any_key'def as_sql(self, compiler, connection):lhs_sql, lhs_params = compiler.compile(self.lhs)rhs_sql, rhs_params = compiler.compile(self.rhs)return f'{lhs_sql} ?| {rhs_sql}', lhs_params + rhs_paramsField.register_lookup(JSONHasAnyKey)
django
Breakdown
1
class JSONHasAnyKey(Lookup):
Subclasses Django internal Lookup object to express SQL transformations.
2
lookup_name = 'has_any_key'
Defines field lookup suffix name used in ORM filter calls (e.g. field__has_any_key).
3
def as_sql(self, compiler, connection):
Compiles left-hand side field and right-hand side parameters into PostgreSQL ?| operator SQL expression.
4
Field.register_lookup(JSONHasAnyKey)
Registers custom lookup globally on base Field class for queryset accessibility.