Order_by last element in list in JSONFiled

Using Django
1

Hi! Have JSONField with content like:
“statuses”: [
{
“code”: “CREATED”,
“date_time”: “2020-06-15T15:02:57+0700”,
“city”: “Офис СДЭК”
},
{
“code”: “CREATED”,
“date_time”: “2020-06-15T15:05:32+0700”,
“city”: “Офис СДЭК”
},
{
“code”: “RECEIVED_AT_SHIPMENT_WAREHOUSE”,
“date_time”: “2020-06-15T16:42:24+0700”,
“city”: “Офис СДЭК”
}

I try make query with ordering by code. But i need get last status.
so, string like:
SDOrders.objects.filter(some_filter).order_by('ext_data__statuses__-1__name’) - don’t work
How i can set, that i need last element of List?

2

Eesh, this might be a little difficult. As far as I know PostgreSQL does not support filtering by a string version of a timestamp so you’d end up having to cast that value from the JSON object into a timestamp, then perform the ordering on it. Writing raw sql, that’s probably doable. Getting that to work in Django feels like it’s going to be difficult. I’d recommend writing that SQL by hand first, then try to figure out how to get the django ORM to do that. You may need to use .annotate() and an ExpressionWrapper to avoid using the .raw() method altogether.

Hopefully that helps you get started.

1 Like