Annotate JSONField on-the-fly and retrieve keys via values

Question

I annotate a JSONField on-the-fly like this. Since this is not a field of a model I think the model is not relevant. This example should be valid for any queryset.

>>> from django.db.models import F, Func, JSONField, Value

>>> queryset = queryset.annotate(
        json=Func(Value("foo"), Value("bar"), function="jsonb_build_object", output_field=JSONField())
    )

>>> queryset.first().json
{'foo': 'bar'}

Now, when I want to retrieve a value of the annotated JSONField by calling values, I receive an error:

>>> queryset.values("json__foo")
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 841, in values
    clone = self._values(*fields, **expressions)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 836, in _values
    clone.query.set_values(fields)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/query.py", line 2174, in set_values
    self.add_fields(field_names, True)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1863, in add_fields
    join_info = self.setup_joins(name.split(LOOKUP_SEP), opts, alias, allow_many=allow_m2m)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1565, in setup_joins
    path, final_field, targets, rest = self.names_to_path(
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1481, in names_to_path
    raise FieldError("Cannot resolve keyword '%s' into field. "
django.core.exceptions.FieldError: Cannot resolve keyword 'json' into field. Choices are: <other fields>

Annotating a field by retrieving it from the previously annotated JSONField also does not work:

>>> queryset.annotate(foo=F("json__foo"))
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 1116, in annotate
    clone.query.add_annotation(annotation, alias, is_summary=False)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1018, in add_annotation
    annotation = annotation.resolve_expression(self, allow_joins=True, reuse=None,
  File "/usr/local/lib/python3.9/site-packages/django/db/models/expressions.py", line 539, in resolve_expression
    return query.resolve_ref(self.name, allow_joins, reuse, summarize)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1692, in resolve_ref
    join_info.transform_function(targets[0], final_alias)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1583, in transform
    wrapped = previous(field, alias)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1558, in final_transformer
    return field.get_col(alias)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/fields/__init__.py", line 398, in get_col
    if alias != self.model._meta.db_table or output_field != self:
AttributeError: 'JSONField' object has no attribute 'model'

I would expect both commands to work, as per the Django documentation on Querying JSONField() - Key, index and path transforms.

Can this be considered a bug or am I doing it wrong?

score 1 · Answer 1 · answered Mar 31 '21 at 13:24

So, if you need value of a specific key inside json field and this only works when you are using postgresql, you can do it as:-

from django.db.models import F, Func, JSONField, Value
from django.contrib.postgres.fields.jsonb import KeyTransform
queryset = queryset.annotate(
    json=Func(Value("foo"), Value("bar"), function="jsonb_build_object", output_field=JSONField()), 
    foo_value=KeyTransform('foo', 'json')
)
queryset.values('foo_value')

Annotate JSONField on-the-fly and retrieve keys via values

1 Answers1