[Django]-Using django query set values() to index into JSONField

by

1👍

✅

Perhaps a better solution (for Django >= 1.11) is to use something like this:

from django.contrib.postgres.fields.jsonb import KeyTextTransform

Car.objects.filter(options__interior__color='red').annotate(
    interior_material=KeyTextTransform('material', KeyTextTransform('interior', 'options'))
).values('interior_material')

Note that you can nest KeyTextTransform expressions to pull out the value(s) you need.

👤simon

3👍

I can suggest a bit cleaner way with using:

from django.db.models import F, Func, CharField, Value
Car.objects.all().annotate(options__interior__material =
    Func(
        F('options'),
        Value('interior'),
        Value('material'),
        function='jsonb_extract_path_text'
    ), 
)
👤andilabs

0👍

Car.objects.extra(select={'interior_material': "options#>'{interior, material}'"})
.filter(options__interior__color='red')
.values('interior_material')

You can utilize .extra() and add postgres jsonb operators

Postgres jsonb operators: https://www.postgresql.org/docs/9.5/static/functions-json.html#FUNCTIONS-JSON-OP-TABLE

👤Mani

Leave a comment