I'm trying to make a filter of a JSONField that has the following data:
[
{
"date": "2017-12-02T11:39:07.447677",
"staff": "payment_in_process"
},
{
"date": "2017-12-02T11:39:12.700590",
"state": "ASSIGNING",
"staff": null
},
{
"date": "2017-12-02T11:40:38.974475",
"state": "ASSIGNED",
"staff": "Marco"
},
{
"date": "2017-12-02T12:05:54.834831",
"state": "BUYING",
"staff": "Marco Alejandro"
},
{
"date": "2017-12-02T13:30:13.426935",
"state": "DELIVERING",
"staff": "Marco Alejandro"
},
{
"date": "2017-12-02T14:11:44.572710",
"state": "DELIVERED",
"staff": "Marco Alejandro"
},
{
"date": "2017-12-02T14:11:45.895028",
"state": "DELIVERED",
"staff": "Marco Alejandro"
},
{
"date": "2017-12-02T14:11:46.781141",
"state": "DELIVERED",
"staff": "Marco Alejandro"
},
{
"date": "2017-12-04T18:01:22.575038",
"state": "DELIVERED_RATED",
"staff": "Marco Alejandro"
}
]
This JSON is a JSONField that stores all the states of an order together with the date in which that state changes and is adding more elements as the order changes states. What I'm trying to do is get a report comparing the date of the last JSONField object with a date data.
Doing it with the first element of the JSONField is very easy, I do it in the following way
date1 = request.data.get("date1", None)
date1_parsed = datetime.strptime(date1, "%d/%m/%Y").strftime('%Y-%m-%d %H:%M:%S')
orders = Order.objects.filter(history__0__date__range=(date1_parsed, date2_parsed))
What I can not do is like always point to the last element, I can not burn a value since each order will have a different size from the JSONField.