Incorrect SQL generation for JSON has_key__in

[Shaheed Haque]

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

[Shaheed Haque]

My fix was incomplete: it turns out that an empty "array[]" gives rise to an error:

django.db.utils.ProgrammingError: cannot determine type of empty array
LINE 1: ...021-06-12'::date AND (snapshot #> '{employee}' ?| array[])) ...
                                                             ^
HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].

which I fixed by casting to an array of strings using ::varchar[] (since JSON keys are always strings) like this:

qs = company.payrun_set.extra(where=[''' "paiyroll_payrun"."snapshot" #> '{employee}' ?| array[...is empty...]::varchar[] '''])

In a similar vein, I'm not clear if the values in the array should/could be explicitly converted to/masked as strings?

[Mariusz Felisiak]

has_key cannot be chained with other lookups, if you do this it will be interpreted as a key lookup for JSONField as you observed. You should use ​has_key_any. If you're having trouble understanding how Django works, see TicketClosingReasons/UseSupportChannels for ways to get help.