Django Postgresql JsonField query related dictionary keys

Question

A part of the model that I have, which uses Django Model field, is like the following:

class SalesModel(models.Model):
    some_data = models.PositiveIntegerField(db_index=True)
    some_other_data = models.CharField(max_length=50)
    json_data = JSONField(blank=True, null=True)

Now following is the format of the JsonData field:

[{"id": val, "contribution": "some_val", }, {"id": some_val, "contribution": "some_other_val",}, {"id": other_val, "contribution": "some_another_val"}]

i.e., the format is:

 [{'id':XX, 'contribution':XX},{'id':YY, 'contribution':YY},{'id':ZZ, 'contribution':ZZ}]

Currently I can filter the Django table with the val of ID. I would now, like to know the contribution of that particular ID.

For eg, if val = 1, I would like to filter the model SalesModel which has JsonField with id = 1, and I want to show the related contribution. So, that would mean, out of the 3 possible dictionaries (as per the field construction), I would only show one dictionary (filtered by the 'ID' key of that dictionary). That would mean, if the 2nd dictionary has a matching ID, show only the 2nd contribution, if the 1st ID is matching show only the 1st contribution, and similarly for the 3rd dictionary.

Is there a way that can be done?

Answer 1

You could restructure your JSONField differently, by giving it a dict where the key, value pairs are id: contribution directly. This way you could use the has_key filter and KeyTransform will work, as I'm not sure it works on an array of dicts. So assuming your json_data looks like this:

{1: 'xx', 3: 'yy', 9: 'zz'}

you could query this way, based on @vanojx1 contribution:

SalesModel.filter(json_data__has_key=id)\
    .annotate(contrib=KeyTransform(id, 'json_data')\
    .values('contrib')

Alternatively, using raw jsonb in postgresql:

SalesModel.filter(json_data__has_key=id)\
    .extra(select={'contrib': "json_data->{0}".format(id)})\
    .values('contrib')