0

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)';
VtoCorleone
  • 16,813
  • 5
  • 37
  • 51

1 Answers1

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"
      }
    }
  ]
}
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • Agreed that the data design is poor form. But... that's not the question ;) Sometimes you have to deal with what you're given. – VtoCorleone Aug 09 '22 at 14:48
  • I do agree with that. Does my effort help? – Stefanov.sm Aug 09 '22 at 14:50
  • So, it did what my original question asked so I'm going to leave it here in case it helps somebody else in the future. There was a couple of extra sibling properties to `enrolled` so this exact solution doesn't work in that case because it overwrites the whole object (which is what I asked). My solution is goin to be biting the bullet and untangling the JSON into their own tables. – VtoCorleone Aug 09 '22 at 22:38