CODEWITHSUNDEEP
pythondjangopostgresql
Irfan Dzankovic
Irfan Dzankovic

Reputation: 81

Django JSONField filtering Queryset

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.

Upvotes: 8

Views: 11958

Answers (1)

Bob
Bob

Reputation: 6173

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

Upvotes: 9

Related Questions