I have the model Item with JSON field
metadata = JSONField(null=True)
sample content:
[{"value": 50, name: "other"}, {"value": 60, name: "other2"}]
How can I query all rows where exists a "value" larger than 50?
I tried
Item.objects.filter(metadata__contains__value__gt=50)
I've managed to query an exact value with
Item.objects.filter(metadata__contains=[{"value": 50}])
Django, as of version 4.1, doesn't have this built-in. Fortunately, in PostgreSQL you can achieve this (refer to https://www.postgresql.org/docs/12/functions-json.html), and Django allows you to write custom SQL and Lookups.
In PostgreSQL, you can use the following in the WERE clause (both lines are equivalent):
jsonb_path_exists(metadata, '$[*] ? (@.value > 50)')
jsonb_path_exists(metadata, '$[*] ? (@.value > $val)', '{"val": 50}')
One way to achieve this in Django is to use RawSQL, however passing parametres other than numbers is tricky, as they will be surrounded in quotes and break it. Also, we can make it reusable by implementing a lookup.
Here is a simplified version of how you could achieve this with a custom lookup. Explanation for some design choices is in the form of comments.
class JSONBPath(RawSQL):
"""Wrapper for a JSONB path."""
def __init__(self, sql, params: dict | list | None = None):
# `params` must be a JSON serializable instance that will be
# passed as the third parameter of `jsonb_path_exists`
# We will not use `params` in this class, more reasons below
if params is not None:
params = Value(params, output_field=JSONField())
super().__init__(sql, params)
def as_sql(self, compiler, connection):
# We are quoting the placeholder and there could be a risk of
# SQL injection if passing the parameters here. Furthermore
# if passing strings, they will be wrapped in single quotes and
# you will get a syntax error. Hence we make the params an empty
# tuple and let `JSONBPathExists` handle the params.
return "('%s')" % self.sql, tuple()
class JSONBPathExists(Lookup):
lookup_name = "path_exists"
def __init__(self, lhs, rhs):
if isinstance(rhs, JSONBPath):
# Trick to handle the json parameters, as we cannot pass
# more parameters to the lookup, and to avoid the issues
# mentioned above
self.jsonb_vars: Value | None = rhs.params
else:
self.jsonb_vars = None
super().__init__(lhs, rhs)
def process_jsonb_vars(self, compiler, connection):
"""
Converts and cleans json_vars to SQL and params,
so that it is safe.
"""
if self.jsonb_vars is None:
return "", ()
return self.jsonb_vars.as_sql(compiler, connection)
def as_sql(self, compiler, connection):
lhs, lhs_params = self.process_lhs(compiler, connection)
rhs, rhs_params = self.process_rhs(compiler, connection)
jsonb_vars, jsonb_vars_params = self.process_jsonb_vars(compiler, connection)
if jsonb_vars:
params = tuple(lhs_params) + tuple(rhs_params) + tuple(jsonb_vars_params)
return "jsonb_path_exists(%s, %s, %s)" % (lhs, rhs, jsonb_vars), params
else:
params = tuple(lhs_params) + tuple(rhs_params)
return "jsonb_path_exists(%s, %s)" % (lhs, rhs), params
Note that we are passing any extra parameters provided by the user as a JSON value, to prevent the issues with strings being wrapped in single quotes and SQL injection vulnerability.
You can register the lookup for JSONField and then perform your query as follows:
Item.objects.filter(metadata__path_exists=JSONBPath("$[*] ? (@.value > $val)", {"val": 50}))
Or without registering the lookup:
Item.objects.filter(JSONBPathExists(F("metadata"), JSONBPath("$[*] ? (@.value > $val)", {"val": 50})))
To query based on a given dictionary key, simply use that key as the lookup name. For example:
Item.objects.filter(metadata__value__gte=50)
