0

I am trying to add a new key value pair to a jsonb array and I am having some difficulties in doing it. What I am trying to add is a key id with a uuid value

This is how my data looks right now:

{
    "id": "12c78bb5-8f81-49c0-b226-feb0164573c3",
    "type": "imageGalleryModule",
    "content": {
        "images": [{
            "title": "Mollit anim id est",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }, {
            "title": "Officia Deserunt",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }]
    }
}

And this is the result I am looking to achieve:

{
    "id": "12c78bb5-8f81-49c0-b226-feb0164573c3",
    "type": "imageGalleryModule",
    "content": {
        "images": [{
            "id": "6a1d15ad-468b-4714-81f8-dccffdea3021",
            "title": "Mollit anim id est",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }, {
            "id": "8099c2cf-2ff1-4855-9b54-aa0e62d4bfa5",
            "title": "Officia Deserunt",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }]
    }
}

So far I've only managed to add the id key with a static value using this query

select  jsonb(
                COALESCE(
                        jsonb_set(
                                imageElements,
                                '{id}',
                                jsonb '3',
                                true
                            ), imageElements
                    )
            )
from  jsonb_array_elements(
    (
        select entitiesArray->'content' -> 'images' from (
                select jsonb_array_elements(column) as entitiesArray from table
        ) as column
            where entitiesArray->> 'type' = 'imageGalleryModule'
    )
    ) as imageElements;

My question is, how can I generate a uuid for each key I am trying to add? Instead of my current static value 3 I want to add a uuid that must not be the same for all the elements in the array

1 Answer 1

1

I managed to solve this problem using this query:

select to_jsonb(array_agg(jsonb(
                COALESCE(
                        jsonb_set(
                                imageElements,
                                '{id}',
                                to_jsonb(uuid_generate_v4()),
                                true
                            ), imageElements
                    )
            )))
from  jsonb_array_elements(
    (
        select entitiesArray->'content' -> 'images' from (
                select jsonb_array_elements(column) as entitiesArray from table
        ) as column
            where entitiesArray->> 'type' = 'imageGalleryModule'
    )
    ) as imageElements;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.