Menu
For project I'm using Python 3.6.3, Django 2.0 and Postgre 9.4. In my class Ticket, a have JSONField passenger
passenger = JSONField(blank=True)
and my passenger JSON looks like this:
{
"email": null,
"mobile": "21312",
"passport": "2141241",
"sms_sent": false,
"full_name": "something"
},
{
"email": null,
"mobile": null,
"passport": "1231231",
"sms_sent": false,
"full_name": "Irfan"
},
{
"email": null,
"mobile": null,
"passport": "1231231",
"sms_sent": true,
"full_name": "Irfan"
}
Now I have django command where I want to filter tickets that have mobile that is not null or None, and sms_sent is False.
tickets = Ticket.objects.filter(
date=tomorrow, trip__bus_company=bus_company,
passenger__sms_sent=False
).not_cancelled()
Now passenger__sms_sent=False filter is working, and is giving my only Tickets with sms_sent=False. But passenger__mobile filter is not working. I tried everyone of this:
tickets = tickets.exclude(passenger__mobile=None)
tickets = tickets.exclude(passenger__mobile=None).exclude(passenger__mobile='')
tickets = tickets.exclude(passenger__mobile__isnull=True)
tickets = tickets.exclude(passenger__exact={'mobile': None})
tickets = tickets.exclude(passenger__mobile__isnull=True).exclude(passenger__mobile='')
tickets = tickets.exclude(passenger__mobile__isnull=False).exclude(passenger__mobile='')
tickets = tickets.exclude(Q(passenger__mobile__isnull=True) | Q(passenger__mobile=''))
and also puting passenger__mobile in first filter, but I can not filter out(exclude) tickets where passenger__mobile is null, I either get all tickets, or empty queryset.
Now I can do this:
for ticket in tickets:
if ticket.passenger['mobile'] is not None:
print(ticket.passenger['mobile'])
but that is not what I'm looking for. I want to use filter or exclude to get those tickets. What I'm I doing wrong? P.S. not_cancelled() is my manager and it does not have anything to do with passenger field.
579
Django Filter Queryset Introduction of Django Filter Queryset When the database connectivity is been established in Django, there is always a need of extracting the needed columns alone from the database and their corresponding rows, basically, the filter query set in django model filters is used for achieving this.
As per the Django JSONField docs, it explains that that the data structure matches python native format, with a slightly different approach when querying. If you know the structure of the JSON, you can also filter on keys as if they were related fields: Your contains example is almost correct, but your data is in a list and requires:
QuerySets are lazy – the act of creating a QuerySet doesn’t involve any database activity. You can stack filters together all day long, and Django won’t actually run the query until the QuerySet is evaluated. Take a look at this example:
The djangorestframework-word-filter developed as alternative to filters.SearchFilter which will search full word in text, or exact match. django-url-filter provides a safe way to filter data via human-friendly URLs.
According to this https://code.djangoproject.com/ticket/25718 (see the last, closing comment) the following should work model.objects.filter(field__key=None) (But obviously you should use the Django version with the fix).
The django docs https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/fields/#querying-jsonfield
warn that
Since any string could be a key in a JSON object, any lookup other than those listed below will be interpreted as a key lookup. No errors are raised. Be extra careful for typing mistakes, and always check your queries work as you intend.
and here they are https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/fields/#containment-and-key-operations
93
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
