select value from jsonfield in queryset

Question

I have the following situation

class MyModel(models.Model):
    my_field = JSONField(null=True, blank=True)

my_field is a JSON field with the following structure:

{
    "name": "some name"
    "id": "some hash id"
}

I want to perform something like: MyModel.objects.values(my_field__name)
which will be equivalent to the postgres query: select my_field->>'name' from my_app_my_model
but i can't seem to get django to generate the wanted query.
The error im getting is:

FieldError: Cannot resolve keyword 'name' into field. Join on 'my_field' not permitted.

The main goal here is to provide a list of strings called fields and run the following command: MyModel.objects.values(*fields) so i think that raw sql solutions won't fit here. Custom Lookups are for the WHERE clause and i don't know of any equivalent for the SELECT clause.
Any thought on how this can be done?
Thanks!

Possible duplicate of [SELECT on JSONField with Django](https://stackoverflow.com/questions/42379475/select-on-jsonfield-with-django) — Endre Both, Mar 08 '19 at 10:09

score 5 · Answer 1 · answered Jul 30 '17 at 15:15

5

Currently values doesn't support json lookups. You can try to do it with extra.

MyModel.objects.extra(select={'name':"my_field->>'name'"}).values('name')

answered Jul 30 '17 at 15:15

Dima Kudosh

7,126
4
36
46

score 4 · Answer 2 · edited May 31 '18 at 21:11

4

Don't use extra it might be deprecate for next versions as warning from django

Use RawSQL instead.

MyModel.objects.annotate(name=RawSQL("myfield->>'name'")).values('name').filter(<put your conditions here>)

edited May 31 '18 at 21:11

cnnr

1,267
4
18
23

answered May 31 '18 at 18:59

Abrar Khan

376
3
12

select value from jsonfield in queryset

2 Answers2