本文介绍了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';((在线演示)
更多推荐
PostgreSQL:使用json对象从数组中删除键/值对
发布评论