Add/Delete a property of every object inside JSONB column in PostgreSQL-CodePudding

TL;DR: I need two UPDATE scripts that would turn (1) into (2) and vice-versa (add/delete color property from every object in the JSONB column)

(1)

id | name | squares

 1 |   s1 | [{"x": 5, "y": 5, "width": 10, "height": 10}, {"x": 0, "y": 0, "width": 20, "height": 20}]
 2 |   s2 | [{"x": 0, "y": 3, "width": 13, "height": 11}, {"x": 2, "y": 3, "width": 20, "height": 20}]

(2)

id | name | squares

 1 |   s1 | [{"x": 5, "y": 5, "width": 10, "height": 10, "color": "#FFFFFF"}, {"x": 0, "y": 0, "width": 20, "height": 20, "color": "#FFFFFF"}]
 2 |   s2 | [{"x": 0, "y": 3, "width": 13, "height": 11, "color": "#FFFFFF"}, {"x": 2, "y": 3, "width": 20, "height": 20, "color": "#FFFFFF"}]

My schema

I have a table called scene with squares column, which has a type of JSONB. Inside this column I store values like this: [{"x": 5, "y": 5, "width": 10, "height": 10}, {"x": 0, "y": 0, "width": 20, "height": 20}].

What I want to do

I want to now add color to my squares, which implies also adding some default color (like "#FFFFFF") to every square in every scene record in the existing production database, so I need a migration.

The problem

I need to write a migration that would add "color": "#FFFFFF" to every square in the production database. With a relational schema that would be as easy as writing ALTER TABLE square ADD color... for the forward migration and ALTER TABLE square DROP COLUMN color... for the rollback migration, but since square is not a separate table, it is an array-like JSONB, I need two UPDATE queries for the scene table.

CodePudding user response：

(1) Adding color:

update scene set squares = (select array_to_json(array_agg(jsonb_insert(v.value, '{color}', '"#FFFFFF"'))) 
        from jsonb_array_elements(squares) v);
select * from scene;

See demo.

(2) Removing color:

update scene set squares = (select array_to_json(array_agg(v.value - 'color')) 
    from jsonb_array_elements(squares) v);
select * from scene;

See demo.