python - Querying jsonb fields in PostgreSQL using Django: IN vs. CONTAINS

Question

When Django's ORM translates a JSONB query using CONTAINS into SQL it uses the ->> operator which is working fine.

For example, something like:

"metadata__my_field_name__icontains": "1234"

runs as:

...WHERE UPPER(("table"."metadata" ->> my_field_name)::text) LIKE UPPER(%1234%)...

which works great.

However, when I try to use the IN operator and a list of values:

"metadata__my_field_name__in": my_list

the SQL generated uses the JSON, as opposed to JSONB operator like this:

..."table"."metadata" -> 'my_id') IN ('1234', '3456', ...

While it runs, it does not return the expected values (empty set). By contrast, if I manually run the same query using the JSONB operator, ->>, the expected values are returned. Note also, that the IDs are converted to strings. I'm running Django 2.2.

Is there a way to force Django to use the JSONB operator?

question from:https://stackoverflow.com/questions/65928462/querying-jsonb-fields-in-postgresql-using-django-in-vs-contains

Answer 1

OK, so this turned out to be doable using a custom Lookup. Like so:

from django.contrib.postgres.fields.jsonb import (
    KeyTransform, KeyTransformTextLookupMixin
)
from django.db.models.lookups import In

@KeyTransform.register_lookup
class KeyTransformIdIn(KeyTransformTextLookupMixin, In):
    lookup_name = 'id_in'
    prepare_rhs = False

    def as_sql(self, compiler, connection):
        lhs_sql, lhs_params = self.process_lhs(compiler, connection)
        rhs_sql, rhs_params = self.process_rhs(compiler, connection)
        sql = "%s::int IN %s" % (lhs_sql, rhs_sql)
        params = [*lhs_params, *rhs_params]
        return sql, params

From the KeyTransformTextLookupMixin source code comments:

"""
Mixin for combining with a lookup expecting a text lhs from a JSONField
key lookup. Make use of the ->> operator instead of casting key values to
text and performing the lookup on the resulting representation.
"""

The built in django.db.models.lookups.In class provides the proper right-hand-side processing to convert the list of integers into correct PostgreSQL.

...WHERE ("table"."metadata" ->> 'my_id')::int IN (1234, 3456...