postgresqlpostgres-12

Updating a nested array element in a jsonb column with postgres 12.10


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)';

Solution

  • 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"
          }
        }
      ]
    }