Django, MySQL, JSONField - Filtering Issue with JSON Data

Question

I'm encountering an issue with filtering JSON data stored in a Django JSONField. While attempting to filter data using the icontains lookup, it seems to treat the JSON data as a string field, and the filtering doesn't work as expected. Here's a brief overview of my setup:

Model:

I have a Django model with a JSONField like this:

from django.db import models

class ChatMessage(models.Model):
    template_message = models.JSONField(null=True)

The JSON data is stored in the following format in the database:

{
    "header": {
        "type": "text",
        "content": "Hello"
    },
    "body": "Hi there",
    "footer": "Hello there"
}

Problem Description:

The problem I'm facing is with the filtering code. I want to filter objects based on values within the JSON data, but the icontains lookup seems to be treating it as a string field rather than a JSON field.

ChatMessage.objects.filter(
    Q(template_message__header__content__icontains=search_key) |
    Q(template_message__body__icontains=search_key) |
    Q(template_message__footer__icontains=search_key)
)

Expected Outcome:

I expect the filtering to consider the JSON structure and filter based on the presence of search_key within any of the JSON values, such as in the content, body, or footer fields.

Error Messages:

I haven't received any error messages, but the filtering doesn't work as expected.

Answer 1

Using contains/icontains to filter a JSONField's value will not give you your desired result. To achieve your desired result, you can use Substr or KT(Django >= 4.2)

from django.db.models import CharField, Q
from django.db.models.functions import Substr


ChatMessage.objects.annotate(
    header_content=Substr("template_message__header__content", 1, output_field=CharField()),
    body=Substr("template_message__body", 1, output_field=CharField()),
    footer=Substr("template_message__footer", 1, output_field=CharField()),
).filter(
    Q(header_content__icontains=search_key)
    | Q(body__icontains=search_key)
    | Q(footer__icontains=search_key)
)

If you are using Django >= 4.2, you can make use of KT as below

from django.db.models.fields.json import KT
from django.db.models import Q


ChatMessage.objects.annotate(
    header_content=KT("template_message__header__content"),
    body=KT("template_message__body"),
    footer=KT("template_message__footer"),
).filter(
    Q(header_content__icontains=search_key)
    | Q(body__icontains=search_key)
    | Q(footer__icontains=search_key)
)