Repository navigation
Fixed #33552 -- Fixed JSONField has key lookups with numeric keys on MariaDB, MySQL, Oracle, and SQLite. - #15503
Conversation
felixxm
left a comment
felixxm
left a comment
There was a problem hiding this comment.
@laymonage Thanks for this patch 👍 Unfortunately it makes this code less readable. What do you think about adding HasKeyLookup.compile_json_path() that could be overridden in a new subclass e.g. HasKeyOrArrayIndex used in KeyTransformIsNull and KeyTransformExact on SQLite and Oracle? For example (not tested draft):
class HasKeyLookup(PostgresOperatorLookup):
def compile_json_path(self, key_transforms, include_root=True):
path = ["$"] if include_root else []
for key_transform in key_transforms:
path.append(".")
path.append(json.dumps(key_transform))
return "".join(path)
...
class HasKeyOrArrayIndex(HasKey):
def compile_json_path(self, key_transforms, include_root=True):
return compile_json_path(self, key_transforms, include_root)
...
class KeyTransformIsNull(lookups.IsNull):
# key__isnull=False is the same as has_key='key'
def as_oracle(self, compiler, connection):
sql, params = HasKeyOrArrayIndex(
self.lhs.lhs,
self.lhs.key_name
).as_oracle(compiler, connection)
...
def as_sqlite(self, compiler, connection):
template = "JSON_TYPE(%s, %%s) IS NULL"
if not self.rhs:
template = "JSON_TYPE(%s, %%s) IS NOT NULL"
return HasKeyOrArrayIndex(self.lhs.lhs, self.lhs.key_name).as_sql(
compiler,
connection,
template=template,
)
ae19b51 to
01dbbe2
Compare
That's much cleaner, thanks! I've made those changes. It's a bit ambiguous to have |
…MariaDB, MySQL, Oracle, and SQLite.
|
@laymonage Thanks 👍 I noticed that we need to change the current behavior only for the final key, so I renamed |
felixxm
changed the title
HasKeyLookup with numeric keys on SQLite, MySQL, MariaDB, and Oracle.
…ield lookups to handle numeric keys on SQLite, MySQL, and Oracle. The issue was that numeric string keys like '1111' were being treated as array indices instead of object keys. The fix: 1. Added try_parse_int parameter to compile_json_path() to control whether numeric strings should be parsed as integers 2. Modified HasKeyLookup to use try_parse_int=False for plain key values (treating numeric strings as object keys) 3. Modified KeyTransform to preserve the original type of keys (integers vs strings) 4. Modified KeyTransformFactory to parse numeric strings as integers for array access 5. Updated HasKeys.get_prep_lookup() to preserve key types instead of converting all to strings This ensures that: - Array indices like value__d__0 correctly use [0] syntax - Numeric string keys like value__has_key='1111' correctly use ."1111" syntax - The lookups work correctly on SQLite, MySQL, and Oracle databases
Fixes ticket-33552.
Doesn't seem very sound, but it's tricky because the
HasKeylookup is used by other lookups (isnullandexact). It's the best I can come up with for now.