Django JsonField Array data query

Question

I have a jsonfield in Postgres db and data like below:

income_info = [
  {
    "id": "1",
    "name": "A",
    "min_income": 22000
  },
  {
    "id": "2",
    "name": "B",
    "min_income": 40000
  },
  {
    "id": "3",
    "name": "C",
    "min_income": 22000
  }
]

Now want to use gte and lte over the django orm queryset. Already tried

Employee.objects.filter(income_info__min_income__lte = 4000000)

but did not work at all.

models.py:

class Employee(models.Model):
    institute = models.ForeignKey(Institute, on_delete=models.DO_NOTHING)
    income_info = JSONField(default=list)
    others = models.TextField(null=True)

can you please share your models?

ruddra
– ruddra

2019-07-31 13:01:47 +00:00
Commented Jul 31, 2019 at 13:01 — ruddra
– ruddra, Commented Jul 31, 2019 at 13:01
please check updated question

Russell
– Russell

2019-07-31 13:09:26 +00:00
Commented Jul 31, 2019 at 13:09 — Russell
– Russell, Commented Jul 31, 2019 at 13:09

cagrias · Accepted Answer · 2019-07-31 13:07:46Z

3

In django's documentation for querying JsonFields:

If the key is an integer, it will be interpreted as an index lookup in an array

As your json data is list of json datas, you need a query like this:

Employee.objects.filter(income_info__0__min_income__lte=4000000)

answered Jul 31, 2019 at 13:07

cagrias

1,8573 gold badges14 silver badges24 bronze badges

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

3 Comments

Russell Over a year ago

Many Many Thanks

Yandiro Over a year ago

Is there a way to do that check only for the object with id 3? Considering I don't know its position in the array?

cagrias Over a year ago

Im not sure if you can do that. Maybe you can add an identifier field in your json schema and have a separate filter for that in your query?

Collectives™ on Stack Overflow

Django JsonField Array data query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related