Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE movies(id, data) AS VALUES
  ('1', '{"actors": [ {"name": "actor1", "email": "actor1@somemail.com"}, {"name": "actor2", "email": "actor2@somemail.com"} ] }'::jsonb);

SELECT jsonb_pretty(data) FROM MOVIES;

SELECT 1




jsonb_pretty


{
    "actors": [
        {
            "name": "actor1",
            "email": "actor1@somemail.com"
        },
        {
            "name": "actor2",
            "email": "actor2@somemail.com"
        }
    ]
}



SELECT 1


UPDATE movies
SET data = jsonb_set(data, '{actors}', (
  SELECT jsonb_agg(actor - 'email')
  FROM jsonb_array_elements(data->'actors') actor
))
WHERE id = '1';

UPDATE 1


SELECT jsonb_pretty(data) FROM MOVIES;


jsonb_pretty


{
    "actors": [
        {
            "name": "actor1"
        },
        {
            "name": "actor2"
        }
    ]
}



SELECT 1