id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36612	KeyTextTransform uses ->> on CASTed TextField to JSONField in MySQL, causing a syntax error	Jacob Tavener		"== Background / Context ==

In Django 4.2, '''KeyTextTransform''' is used for JSON key lookups that return text, such as when performing __icontains on a JSON key.

On MySQL, Django generates SQL using the ->> operator. While this works for '''native JSONField columns''', it '''fails when the JSON expression comes from a CAST of a TextField to a JSONField''', because MySQL does not allow ->> on expressions like CAST(text_column AS JSON). The ->> operator is only valid on actual JSON columns. Attempting it on a CASTed expression produces a '''syntax error'''.

== Steps to Reproduce ==

Create a model with a TextField storing JSON:

{{{#!div style=""font-size: 80%""
{{{#!python
from django.db import models

class ExampleModel(models.Model):
json_text = models.TextField()
}}}
}}}

Store a sample JSON string in the TextField:

{{{#!div style=""font-size: 80%""
{{{#!python
ExampleModel.objects.create(json_text='{""key"": ""value""}')
}}}
}}}

Perform a JSON key lookup using a cast and string lookup:

{{{#!div style=""font-size: 80%""
{{{#!python
from django.db.models import F, JSONField
from django.db.models.functions import Cast

ExampleModel.objects.annotate(
json_data=Cast('json_text', JSONField())
).filter(json_data__key__icontains='val')
}}}
}}}

== Expected Behavior ==

Django should generate valid MySQL SQL that extracts the JSON key as text, compatible with CASTed TextField columns. Example:

{{{#!div style=""font-size: 80%""
{{{#!sql
LOWER(JSON_UNQUOTE(JSON_EXTRACT(CAST(json_text as json), '$.key'))) LIKE LOWER('%val%')
}}}
}}}

This SQL should execute without errors.

== Actual Behavior ==

Django generates SQL using the ->> operator on the CASTed expression:

{{{#!div style=""font-size: 80%""
{{{#!sql
LOWER(CAST(json_text as json)) ->> '$.key' LIKE LOWER('%val%')
}}}
}}}

This is invalid in MySQL when applied to a CASTed expression.

Results in a syntax error at runtime:

{{{#!div style=""font-size: 80%""
{{{#!text
ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->> '$.\""key\""')) LIKE LOWER('%val%') LIMIT 21' at line 1')
}}}
}}}

== Current Workaround ==

A temporary workaround in projects using MySQL is to monkey patch KeyTextTransform.as_mysql to use JSON_UNQUOTE(JSON_EXTRACT(...)) instead of the ->> operator. This allows JSON key lookups on CASTed TextField → JSONField expressions to work correctly.

Example monkey patch:

{{{#!div style=""font-size: 80%""
{{{#!python
from django.db.models.fields.json import KeyTextTransform, compile_json_path
from django.db.models import JSONField
from django.db.models.functions import Cast

original_as_mysql = KeyTextTransform.as_mysql

def safe_as_mysql(self, compiler, connection):
lhs, params, key_transforms = self.preprocess_lhs(compiler, connection)
json_path = compile_json_path(key_transforms)

sql, params = ""JSON_EXTRACT(%s, %%s)"" % lhs, tuple(params) + (json_path,)
return ""JSON_UNQUOTE(%s)"" % sql, params


KeyTextTransform.as_mysql = safe_as_mysql
}}}
}}}

Notes:

This patch only affects MySQL connections.

It applies globally and must be applied once at project startup (e.g., in an AppConfig.ready() method).

This is a temporary workaround; a proper fix in Django core is preferred.

== Notes / Additional Context ==

This issue occurs only on MySQL.

Native JSONField columns are not affected; ->> works fine on them.

The problem arises specifically when a TextField containing JSON is cast to JSONField for ORM JSON lookups.
"	Bug	new	Database layer (models, ORM)	4.2	Normal			Jacob Tavener	Unreviewed	0	0	0	0	0	0