Passing an integer-only key to KeyTransform

[Niccolò Mineo]

On a Django 3.2 installation, I am trying to pass a stringified integer-only zip code as the key_name argument to KeyTransform:

>>> stats_qs = stats.annotate(municipality_stats=KeyTransform("66049", "data")).values("municipality_stats")

<QuerySet [{'municipality_stats': None}]>

As highlighted above, it fails to work. The reason, as you can see in bold below, is that the key is being interpolated as an integer in the resulting query:

>>> str(stats_qs.query)

'SELECT ("datasets_stats"."data" -> 66049) AS "municipality_stats" FROM "datasets_stats" WHERE "datasets_stats"."stats_type" = totals_municipalities LIMIT 1'

This seems like a bug to me, as a JSON object is not allowed to have an int key, though - only str.

Has anyone successfully managed to make this work?

[Mariusz Felisiak]

Hi,

Integers (and strings that can be cast to integers) on the first-level are always interpreted as index transforms in arrays (as documented) because you can store a JSON array in the JSONField. Unfortunately, you have to use a custom database function or  RawSQL() to get this data. Please also take into account that KeyTransform() is an undocumented transformation (ticket #26511).

Best,
Mariusz

[Niccolò Mineo]

I ended up using the JSONB_EXTRACT_PATH function (I am using Postgres), although it's a pity not to be able to use KeyTransform in this situation.