Using django query set values() to index into JSONField

Question

I am using django with postgres, and have a bunch of JSON fields (some of them quite large and detailed) within my model. I'm in the process of switching from char based ones to jsonb fields, which allows me to filter on a key within the field, and I'm wondering if there is any way to get the equivalent benefit out of a call to the query set values method.

Example:

What I would like to do, given a Car model with options JSONField, is something like

qset = Car.objects.filter(options__interior__color='red')
vals = qset.values('options__interior__material')

Please excuse the lame toy problem, but hopefully it gets the idea across. Here the filter call does exactly what I want, but the call to values does not seem to be aware of the special nature of the JSON field. I get an error because values can't find the field called "interior" to join on. Is there some other syntax or option that I am missing that will make this work?

Seems like a pretty obvious extension to the existing functionality, but I have so far failed to find any reference to something similar in the docs or through stack overflow or google searches.

Edit - a workaround:

After playing around, looks like this could be fudged by inserting the following in between the two lines of code above:

qset=qset.annotate(options__interior__material=RawSQL("SELECT options->'interior'->'material'",()))

I say "fudged" because it seems like an abuse of notation and would require special treatment for integer indices.

Still hoping for a better answer.

andilabs · Accepted Answer · 2017-12-19 18:46:22Z

3

I can suggest a bit cleaner way with using:

django's Func https://docs.djangoproject.com/en/2.0/ref/models/expressions/#func-expressions
and postgres function jsonb_extract_path_text https://www.postgresql.org/docs/9.5/static/functions-json.html

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'
    ), 
)

answered Dec 19, 2017 at 18:46

andilabs

23.5k14 gold badges126 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RishiG Over a year ago

Thanks! This is indeed a little cleaner (avoiding the call to RawSQL), although it still uses my abuse of notation (annotating with double underscores) and functionally it's almost the same (would be equivalent if I had used #> operator instead of -> in the raw sql), right? I wonder if the multiple index thing is more efficient?

simon · Accepted Answer · 2018-03-19 04:38:11Z

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.

answered Mar 19, 2018 at 4:38

simon

16.5k5 gold badges47 silver badges68 bronze badges

1 Comment

RishiG Over a year ago

Wow, been a little while. I probably should have been a little more explicit in my question: I was looking for a general way of mapping string to operation so that I can lump this operation in along with a whole set of other values calls. Still, I did not see KeyTextTransform which seems like the right way to handle the lookup. Thanks for pointing that out!

Mani · Accepted Answer · 2018-09-25 11:18:44Z

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

answered Sep 25, 2018 at 11:18

Mani

9476 silver badges15 bronze badges

Collectives™ on Stack Overflow

Using django query set values() to index into JSONField

Example:

Edit - a workaround:

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Example:

Edit - a workaround:

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related