How to use annotate and KeyTextTransform and KeyTransform to extract first element of nested array?

863 views Asked by At

I am using Django 2.2 and postgres

I have a jsonb field called actions

And this is test value

actions={
    "accept": [
        {
            "id": 123,  # some random action id
            "created": "{:%Y-%m-%dT%H:%M:%S.%f%z}".format(datetime.now()),
            "user_id": 247,  # some random user id
        }
    ]
},

SO I want to use values_list and annotate. I do not know how to just get the actions.accept.0.created

WHat I tried

.annotate(first_accept=KeyTextTransform(0, KeyTextTransform("accept", "quotation__actions")))

This fails. I get the error

django.db.utils.DataError: invalid input syntax for integer: "accept"
LINE 1: ...te_type", ("sometable"."actions" #>> ARRAY['accept',0..

.annotate(accept_actions=KeyTextTransform("accept", "quotation__actions"))

This works but it gives me

'[{"id": 123, "created": "2020-10-16T12:28:16.452071", "user_id": 247}]' as a string.

I can subsequently parse that string yes, but I prefer to directly access the created of the first element of the array.

How do I do that?

0

There are 0 answers