How to use keyTextTransform() for nested json?

user_10857795 0
Viewed 2125

My model has a json field. I can access jsonfield['key1'] with the following query

from django.contrib.postgres.fields.jsonb import KeyTextTransform
MyModel.objects.annotate(val=KeyTextTransform('key1', 'jsonfield')).order_by('val')

But how can I access a key like jsonfield['key1']['key2'] or even more nested ones?

This can't be the only solution, right?

MyModel.objects.annotate(val=KeyTextTransform('key2', (KeyTextTransform('key1', 'jsonfield'))).order_by('val')
postgresql django json
1 Answers

The hard part is already done, thankfully. KeyTextTransform is composable. All we have to do is compose it.

class NestableKeyTextTransform:
    def __new__(cls, field, *path):
        if not path:
            raise ValueError("Path must contain at least one key.")
        head, *tail = path
        field = KeyTextTransform(head, field)
        for head in tail:
            field = KeyTextTransform(head, field)
        return field


MyModel.objects.annotate(
    single_nested_value=NestableKeyTextTransform(
      "json_field", "query", "name"
    ),
    array_access=NestableKeyTextTransform(
      "json_field", "query", "address_line", 1
    ),
)

Though I would like to point out that this may be a better way to do it:

from django.db.models import F

MyModel.objects.annotate(
    single_nested_value=F("json_field__query__name"),
    array_access=F("json_field__query__address_line__1"),
)
user_47280070
Related