[FIXED] How to filter queryset by string field containing json (Django + SQLite)

Issue

I have the following situation. The Flight model (flights) has a field named 'airlines_codes' (TextField) in which I store data in JSON array like format:

["TB", "IR", "EP", "XX"]

I need to filter the flights by 2-letter airline code (IATA format), for example 'XX', and I achieve this primitively but successfully like this:

filtered_flights = Flight.objects.filter(airlines_codes__icontains='XX')

This is great but actually not. I have flights where airlines_codes look like this:

["TBZ", "IR", "EP", "XXY"]

Here there are 3-letter codes (ICAO format) and obviously the query filter above will not work.

PS. I cannot move to PostgreSQL, also I cannot alter in anyway the database. This has to be achieved only by some query.

Thanks for any idea.

Solution

Without altering the database in any way you need to filter the value as a string. Your best bet might be airlines_codes__contains. Here's what I would recommend assuming your list will always be cleaned exactly as you represent it.

Flight.objects.filter(airlines_codes__contains='"XX"')

As of Django 3.1 JSONField is supported on a wider array of databases. Ideally, for someone else building a similar system from the ground up, this field would be a preferable approach.

Answered By - Jacinator