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