Django 2.0中JSONField多乘客数据的QuerySet过滤问题求助

Hey there! Since you're working with Django 2.0, Python 3.6.3, and PostgreSQL 9.4, you can take advantage of PostgreSQL's native JSONB operators (Django maps JSONField to PostgreSQL's jsonb type by default) to filter your passenger array field effectively. Below are practical solutions for common filtering scenarios based on your sample data:

1. Filter Tickets with any passenger matching a specific condition #

This is the most common use case—finding tickets where at least one passenger meets your criteria.

Example 1: Find tickets with at least one passenger who received an SMS (sms_sent: true) #

You can use either Django's built-in lookup or raw SQL for more flexibility:

# Option 1: Use Django's built-in __contains (matches exact sub-structure)
Ticket.objects.filter(passenger__contains=[{"sms_sent": True}])

# Option 2: Use PostgreSQL's native @> operator (explicit for JSONB array checks)
from django.db.models import RawSQL
Ticket.objects.filter(RawSQL("passenger @> %s", ([{"sms_sent": True}],)))

Example 2: Find tickets with a passenger named "Irfan" who hasn't received an SMS #

Match a specific combination of fields in any passenger object:

Ticket.objects.filter(passenger__contains=[{"full_name": "Irfan", "sms_sent": False}])

Example 3: Find tickets with any passenger having a specific passport number #

If you only care about matching one field (ignoring other passenger attributes), raw SQL is more flexible:

# Check if any passenger's passport equals "1231231"
Ticket.objects.filter(RawSQL("""
    EXISTS (
        SELECT 1 FROM jsonb_array_elements(passenger) elem 
        WHERE elem->>'passport' = %s
    )
""", ("1231231",)))

Note: Use ->> to extract values as strings, and -> for native JSON types (like booleans) to avoid type mismatches. For boolean comparisons, write elem->'sms_sent' = true instead of comparing to the string 'true'.

2. Filter Tickets where all passengers match a condition #

For cases where every passenger in the array must meet your criteria (e.g., no passengers have received an SMS):

# Find tickets where NO passengers have sms_sent = true
Ticket.objects.filter(RawSQL("""
    NOT EXISTS (
        SELECT 1 FROM jsonb_array_elements(passenger) elem 
        WHERE elem->'sms_sent' = true
    )
""", ()))

3. Handle blank/null passenger fields #

Since your passenger field is nullable, you might need to include or exclude empty values:

# Exclude tickets with empty or null passenger arrays
Ticket.objects.exclude(passenger__isnull=True).exclude(passenger='[]')

# Only include tickets with empty or null passenger arrays
from django.db.models import Q
Ticket.objects.filter(Q(passenger__isnull=True) | Q(passenger='[]'))

Key Notes #

• Django 2.0's JSONField supports basic lookups like __contains, but for complex array filtering, raw SQL leveraging PostgreSQL's JSON functions (like jsonb_array_elements) is more powerful.
• Always verify your generated SQL with the query attribute (e.g., print(Ticket.objects.filter(...).query)) to catch any unexpected behavior.
• Be mindful of JSON data types—comparing booleans/numbers directly with -> avoids string conversion errors.

内容的提问来源于stack exchange，提问作者Irfan Dzankovic