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
32921	Incorrect SQL generation for JSON has_key__in	Shaheed Haque	nobody	"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
"	Bug	closed	Database layer (models, ORM)	3.2	Normal	invalid			Unreviewed	0	0	0	0	0	0