3

I have a model MyModel with a JSONField called data. This json field contains an array of objects like:

[
    {
        "date": "2021-01-01",
        "amount": 120
    },
    {
        "date": "2021-01-02",
        "amount": 150
    }
]

I would like to filter by the amount of the last element of the array. From the Django documentation, I understand that I can filter by the first element using:

MyModel.objects.filter(data__0__amount__gte=100)

How could I do this using the last element?

nbeuchat
  • 6,575
  • 5
  • 36
  • 50

2 Answers2

1

Django and Postgres' -> operator are quite happy to use negative indices with semantics familiar from Python. So, you can say things like:

>>> MyModel.objects.values_list('data__-1__amount', flat=True)[0]
>>> 150

This works since .values_list() is string based.

But, since .filter() expects kwargs, and data__-1__amount is not a valid Python kwarg key, you need this form:

MyModel.objects.filter(**{'data__-1__amount__gte': 100})
Shaheed Haque
  • 644
  • 5
  • 14
-3

Filter returns always a list of objects even there is only one such object in this case greater than 100 which is stored in data field "data" of the same instance:

last = MyModel.objects.filter(amount__gte=last.data[-1]["amount"])
Coconutcake
  • 156
  • 9