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?

dirkgroten · Accepted Answer · 2017-12-01 13:46:27Z

3

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

edited Dec 1, 2017 at 13:46

answered Dec 1, 2017 at 10:51

dirkgroten

20.8k2 gold badges32 silver badges46 bronze badges

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

4 Comments

Sayantan Over a year ago

This sounds a good one. KeyTransform doesn't seem to have much official documentation. Either ways, can you confirm KeyTransform works on django 1.10.X level (x=5). And what is KeyTransform actually? Sorry to ask a stupid question in comments. If its bad, let me google and understand.

dirkgroten Over a year ago

No I can't confirm this works on Django 1.10.x, I'm using 1.11. But KeyTransform is supported on 1.10.x so I'm pretty sure it works. Just be aware that nested KeyTransforms (which you don't need in this case) are only supported in Django 1.11.x and up.

dirkgroten Over a year ago

KeyTransform, KeyTextTransforms etc... work by combining two keys of your json document and adding the corresponding Postgresql operator to traverse the json document. So contrib=KeyTransform('key', 'json_data') basically does a select json_data::json -> 'key'.

Sayantan Over a year ago

Thank you @dirkgroten... This seems a complete solution

Vanojx1 · Accepted Answer · 2017-12-01 10:20:10Z

1

This should work DOC

SalesModel.objects.filter(json_data__id=1).values('id', 'json_data__contribution')

answered Dec 1, 2017 at 10:20

Vanojx1

5,5842 gold badges25 silver badges37 bronze badges

5 Comments

Sayantan Over a year ago

Are you sure we can use 'values' with 'postgresql' specific 'jsonfield'. Let me try. But as far as I know, that would case an error.

Vanojx1 Over a year ago

Based on the genal framework query logic.. it should

dirkgroten Over a year ago

django.core.exceptions.FieldError: Cannot resolve keyword 'contribution' into field. Join on 'json_data' not permitted

Sayantan Over a year ago

@dirkgroten Exactly, that's what I thought the error would be, because Django doesn't permit it and I don't know of any Raw SQL that Postgres allow

Vanojx1 Over a year ago

Thought that should work... Take a look at this seems exactly the same problem. if it work ill mark this as duplicate

pissall · Accepted Answer · 2017-12-01 10:16:24Z

0

Yes, I guess. If I have understood it right, you will have an id to be matched or a list of ID's. So if your ID is 2:

my_id = 2
dict1 = [{"id":1, "contribution":10},{"id":2, "contribution":20},{"id":3, "contribution":30}] 
for i in dict1:
    if i["id"] == my_id:
        print(i["contribution"])

answered Dec 1, 2017 at 10:16

pissall

7,4442 gold badges29 silver badges47 bronze badges

1 Comment

Sayantan Over a year ago

Well, I will have somewhere around 100K data at max, and around 20K data on average. I would not like a for-loop to run. While looping is an option, is there anything using ORM/Raw SQL we can work with? Thank you @pissall for your time. You would see that we wound have 3N loops. Not good if it reaches the 100K mark more frequently.

Collectives™ on Stack Overflow

Django Postgresql JsonField query related dictionary keys

3 Answers 3

4 Comments

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related