[Answered ]-Django, MySQL, JSONField – Filtering Issue with JSON Data

by

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)
)
👤Temi

Leave a comment