Django postgres Json field with numeric keys

Question

I have model with postgres json field.

class MyModel(models.Model):
    data = JSONField(null=True)

then, I do:

m1 = MyModel.objects.create(data={'10':'2017-12-1'})
m2 = MyModel.objects.create(data={'10':'2018-5-1'})

I want query all the MyModel whose key '10' starts with '2017', so I want to write:

MyModel.objects.filter(data__10__startswith='2017')

The problem is that the 10 is interpreted as integer, and therefore, in the generated query it is considered as list index and not key. Is there anyway to solve this? (except writing raw queries).

This is the generated query:

SELECT "systools_mymodel"."id", "systools_mymodel"."data" FROM "systools_mymodel" WHERE ("systools_mymodel"."data" ->> 10)::text LIKE '2017%' LIMIT 21;

And I want the 10 to be quoted (which would give me the right answer).

Thanks!

Amadaamadas

Answer 1

A very hackish solution (use on your own risk, tested under Django 2.0.5, voids warranty...):

# patch_jsonb.py
from django.contrib.postgres.fields.jsonb import KeyTransform


def as_sql(self, compiler, connection):
    key_transforms = [self.key_name]
    previous = self.lhs
    while isinstance(previous, KeyTransform):
        key_transforms.insert(0, previous.key_name)
        previous = previous.lhs
    lhs, params = compiler.compile(previous)
    if len(key_transforms) > 1:
        return "(%s %s %%s)" % (lhs, self.nested_operator), [
            key_transforms] + params
    try:
        int(self.key_name)
    except ValueError:
        if self.key_name.startswith("K") and self.key_name[1:].isnumeric():
            lookup = "'%s'" % self.key_name[1:]
        else:
            lookup = "'%s'" % self.key_name
    else:
        lookup = "%s" % self.key_name
    return "(%s %s %s)" % (lhs, self.operator, lookup), params


def patch():
    KeyTransform.as_sql = as_sql

Usage:

1. Add this to the bottom of your settings.py:

   import patch_jsonb
   patch_jsonb.patch()
   

2. Instead of __123__ lookups use __K123__ lookups - the uppercase K will be stripped by this patch:

   MyModel.objects.filter(data__K10__startswith='2017')
   

And consider avoiding using numbers as jsonb object keys...

Splenitis