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-01T13:46:27.273

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 01 '17 at 13:46

answered Dec 01 '17 at 10:51

dirkgroten

20,112
2
29
42

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. – Sayantan Dec 01 '17 at 11:58
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 `KeyTransform`s (which you don't need in this case) are only supported in Django 1.11.x and up. – dirkgroten Dec 01 '17 at 13:29
1

`KeyTransform`, `KeyTextTransforms` etc... work by combining two keys of your json document and adding the corresponding [Postgresql operator to traverse the json document](https://www.postgresql.org/docs/9.6/static/functions-json.html). So `contrib=KeyTransform('key', 'json_data')` basically does a `select json_data::json -> 'key'`. – dirkgroten Dec 01 '17 at 13:40
Thank you @dirkgroten... This seems a complete solution – Sayantan Dec 01 '17 at 13:56

score 1 · Answer 2 · answered Dec 01 '17 at 10:20

1

This should work DOC

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

answered Dec 01 '17 at 10:20

Vanojx1

5,574
2
23
37

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. – Sayantan Dec 01 '17 at 10:23
Based on the genal framework query logic.. it should – Vanojx1 Dec 01 '17 at 10:28
django.core.exceptions.FieldError: Cannot resolve keyword 'contribution' into field. Join on 'json_data' not permitted – dirkgroten Dec 01 '17 at 10:30
@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 – Sayantan Dec 01 '17 at 10:34
1

Thought that should work... Take a look at [this](https://stackoverflow.com/questions/42675803/postgres-values-query-on-json-key-with-django) seems exactly the same problem. if it work ill mark this as duplicate – Vanojx1 Dec 01 '17 at 10:38

score 0 · Answer 3 · answered Dec 01 '17 at 10:16

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 01 '17 at 10:16

pissall

7,109
2
25
45

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. – Sayantan Dec 01 '17 at 10:21

Django Postgresql JsonField query related dictionary keys

3 Answers3