PostgreSQL: Delete key/value pair from array with json objects

Question

I have a table:

CREATE TABLE movies( id text, data jsonb );

INSERT INTO movies(id, data) VALUES (
  '1', 
  {
      "actors": [
        {
            "name": "actor1",
            "email": "[email protected]"
        },
        {
            "name": "actor2",
            "email": "[email protected]"
        }
      ]
  }
);

What I want is to delete the email field (key + value) from each json object of the actors array.

I've tried the following solution and although it does execute, it doesn't have any effect on the array at all:

update movies
set data = jsonb_set(data, '{actors}', (data->'actors') - '{actors, email}')
where id = '1';

Bergi · Accepted Answer · 2019-11-18 22:49:09Z

4

To manipulate all items in the array, you will need to use a subquery:

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

(online demo)

answered Nov 18, 2019 at 22:49

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barbaros Özhan · Accepted Answer · 2019-11-18 20:36:55Z

0

You need to specify indexes individually to delete the array element email

update movies
   set data = jsonb_set(data, '{actors}', data -> 'actors' #- '{0,email}' #- '{1,email}')
 where id = '1';

the path element {1,email} might be replaced by {-1,email} (Negative integers count from the end).

Demo

answered Nov 18, 2019 at 20:36

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: Delete key/value pair from array with json objects

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related