Django annotate double nested key in JSONField

Question

My JSONField contains data like this:

class Car(models.Model):
   data = JSONField()

Car.objects.first().data

{
    u'colors': [
        {u'color_id': u'1', u'source': u'Manufacturer 3'},
        {u'color_id': u'2', u'source': u'Manufacturer 2'},
        {u'color_id': u'3', u'source': u'Manufacturer 1'},
    ]
}

I know that I can filter results with:

Car.objects.filter(data__colors__contains=[{'color_id':'3'}])

Is there any way I can annotate queryset so that 'color_id' are contained in a list? Something like:

Car.objects.all().annotate(color_ids=...)

That would enable me to do:

Car.objects.first().color_ids
['3', '2', '1']

Or something along those lines, just to filter with color_id value. Using Django 1.11 and Postgres 13.

Answer 1

I think you can use KeyTransform for getting the json value and ArrayAgg() for getting the list:

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

Car.objects.annotate(colors=KeyTransform("data", "colors")).annotate(color_id=KeyTransform("colors", "color_id")).annotate(colors_ids=ArrayAgg('color_id'))