PostgreSQL:使用json对象从数组中删除键/值对

本文介绍了PostgreSQL:使用json对象从数组中删除键/值对的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一张桌子:

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

我想要从actor数组的每个json对象中删除电子邮件字段(键+值).

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

推荐答案

要操作数组中的所有项，您将需要使用子查询:

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

((在线演示)