I am trying to update a record in an array. The table is participant
and the column is activities
with the shape:
{
enrolled: [{
sport: {
id: 1,
name: 'soccer'
}
}, {
sport: {
id: 2,
name: 'hockey'
}
}]
}
I'd like to run a query that says,
If activities[*].enrolled.sport == 1, add some JSON blob for the registered day
registered: {
day: 12,
month: 'Aug'
}
The resulting object would be:
{
enrolled: [{
sport: {
id: 1,
name: 'soccer'
},
registered: {
day: 12
month: 'Aug'
}
}, {
sport: {
id: 2,
name: 'hockey'
}
}]
}
I've tried the following but it replaces the entire column with the added object :(
set activities = (
select jsonb_agg(jsonb_set(sports, '{registered}', '{"day": 12, "month": "Aug"}', true))
from jsonb_array_elements(activities::jsonb -> 'enrolled') sports
)
where activities::jsonb -> 'enrolled' @? '$.sport.id ? (@ == 1)';
I am afraid that you have to rebuild the whole activities
object.
Unrelated but I think that this is a poor data design. Your examples are far from valid JSON.
update participant set activities =
(
select jsonb_build_object
(
'enrolled',
(jsonb_agg(case when j->'sport'->>'id' = '1' then j||YOUR_JSONB_BLOB else j end))
)
from jsonb_array_elements(activities->'enrolled') j
)
where activities @> '{"enrolled":[{"sport":{"id":1}}]}';
Here is an illustration with your example
select jsonb_build_object
(
'enrolled',
(jsonb_agg(case when j->'sport'->>'id' = '1' then j||'{"registered":{"day":12,"month":"Aug"}}' else j end))
)
from jsonb_array_elements('
{
"enrolled":
[
{ "sport": { "id": 1, "name": "soccer"}},
{ "sport": { "id": 2, "name": "hockey"}}
]
}'::jsonb->'enrolled') j;
Result:
{
"enrolled": [
{
"sport": {
"id": 1,
"name": "soccer"
},
"registered": {
"day": 12,
"month": "Aug"
}
},
{
"sport": {
"id": 2,
"name": "hockey"
}
}
]
}