#32921: Incorrect SQL generation for JSON has_key__in
-------------------------------------+-------------------------------------
Reporter: Shaheed | Owner: nobody
Haque |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I have a Django 3.2.4 model with a JSONField (hosted on Postgres 12)
called "snapshot" which contains employees like this:
{{{
{
"employee": {
"6": {...},
"99": {...}
}
}
}}}
I would like to perform a .filter() on the presence of certain employee
keys, so I tried this:
{{{
qs = company.payrun_set.filter(snapshot__employee__has_key__in=['6',
'8'])
}}}
but this produces incorrect results. When I examine qs.query (which I know
does not always produce an exact rendition), I see the following
incorrect SQL:
{{{
...("paiyroll_payrun"."snapshot" #> ['employee', 'has_key']) IN ("6",
"8")
}}}
Notice how **has_key** is treated as a literal key value. I believe the
correct query can be formed like this:
{{{
qs = company.payrun_set.extra(where=[''' "paiyroll_payrun"."snapshot"
#> '{employee}' ?| array['6', '8'] '''])
}}}
and the corresponding SQL fragment should then look like this (again from
examining qs.query):
{{{
..."paiyroll_payrun"."snapshot"#>'{employee}' ?| array['6', '8']
}}}
Basically, **has_key** followed by **in** should be converted into the "?|
array[]".
I'm not an expert, so would love to be corrected if I have misunderstood.
Thanks, Shaheed
--
Ticket URL: <https://code.djangoproject.com/ticket/32921>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/055.8aef79b23ba5be6bbb3dc00694c39e4d%40djangoproject.com.
