从JSON数组中删除整个JSON对象

Question

我正在尝试通过删除用户输入删除任何联系人的联系来更新联系人列表。换句话说，尝试从我的postgresql数据库中的json数组中删除整个JSON对象，但我会收到错误

错误：列中的null值列“关系”的“信息” user_emails违反 非限制

我仔细检查了 ，并且一切都在那里。当我在这里尝试时在线它可以正常运行，但是在我的服务器上，它返回了错误。我该如何解决？

    DROP table if exists user_emails;
CREATE table user_emails (
  id serial not null PRIMARY KEY,
  info jsonb NOT NULL
  );
  insert into user_emails(info) values('{
  "userid": "4",
  "mailbox": "[email protected]",
  "contacts": [
    {
      "id": "ghr3gk8dez4",
      "email": "[email protected]",
      "last_name": "Doe",
      "first_name": "Jane",
      "date_created": "2022-05-08T20:52:47.967Z"
    },
    {
      "id": "th2lypvoxpr1652045110763",
      "email": "[email protected]",
      "last_name": "Doe",
      "first_name": "Al",
      "date_created": "2022-05-08T21:25:10.763Z"
    },
    {
      "id": "ld123tqicmj1652045372671",
      "email": "[email protected]",
      "last_name": "Doe",
      "first_name": "Stella",
      "date_created": "2022-05-08T21:29:32.671Z"
    },
    {
      "id": "1ltbrpbj8xf1652045768004",
      "email": "[email protected]",
      "last_name": "Doe",
      "first_name": "Marta",
      "date_created": "2022-05-08T21:36:08.004Z"
    },
    {
      "id": "1dgntfwvsmf1652045832589",
      "email": "[email protected]",
      "last_name": "La",
      "first_name": "Na",
      "date_created": "2022-05-08T21:37:12.589Z"
    },
    {
      "id": "ll3z1n0jkhc1652045984538",
      "email": "[email protected]",
      "last_name": "doe",
      "first_name": "bruno",
      "date_created": "2022-05-08T21:39:44.538Z"
    },
    {
      "id": "kzr996xxxt1652046050118",
      "email": "[email protected]",
      "last_name": "Perf",
      "first_name": "Perf",
      "date_created": "2022-05-08T21:40:50.118Z"
    },
    {
      "id": "41bovnvsihq1652046121940",
      "email": "[email protected]",
      "last_name": "Doe",
      "first_name": "Melinda",
      "date_created": "2022-05-08T21:42:01.940Z"
    },
    {
      "id": "tnjlj4dcg2b1652046154937",
      "email": "[email protected]",
      "last_name": "Kee",
      "first_name": "Kee",
      "date_created": "2022-05-08T21:42:34.937Z"
    },
    {
      "id": "hor0wafkuj1652046684582",
      "email": "[email protected]",
      "last_name": "Jo",
      "first_name": "Jo",
      "date_created": "2022-05-08T21:51:24.582Z"
    }
  ],
  "auto_reply": false,
  "email_name": "johndoe",
  "signatures": [],
  "domain_name": "example.com",
  "date_created": "2022-05-08T20:39:54.881Z",
  "forward_email": [],
  "auto_reply_messages": []
}');

这是我的更新

UPDATE user_emails SET info = (SELECT jsonb_agg(j) 
                               FROM jsonb_array_elements(user_emails.info->'contacts') as t(j) 
                               WHERE j ->> 'id' not in ('ghr3gk8dez4'));
                               
SELECT * FROM user_emails;

Answer 1

JSONB_AGG与许多其他汇总功能一样，返回null如果没有行汇总。您可能正在寻找cocece它将其转到一个空数组：

UPDATE user_emails
SET info = jsonb_set(
  user_emails.info,
  '{contacts}',
  COALESCE(
    (SELECT jsonb_agg(j) 
      FROM jsonb_array_elements(user_emails.info->'contacts') as t(j) 
      WHERE j ->> 'id' not in ('ghr3gk8dez4')
    ),
    '[]'::jsonb
  )
);

Answer 2

我认为仅使用内置功能没有一种有效的方法来做到这一点。

有一个操作员＃ -，可以通过指定路径来删除元素，例如info＃ - '{Contacts，0}，0}'会做您想要的。但是，直接构建这样的“路径阵列”并不是直接的。

我会编写一个函数，该函数找到要删除的联系人的索引并生成路径数组：

create or replace function find_entry(p_info jsonb, p_id text)
  returns text[]
as
$
  select array['contacts', (idx - 1)::text]
  from jsonb_array_elements(p_info -> 'contacts') with ordinality as t(element, idx)
  where t.element ->> 'id' = p_id
  limit 1;
$
language sql;

-1是必要的，因为SQL使用1个基本编号，但在JSON数组中以零开始。

使用该功能，您可以做：

update user_emails 
   set info #- find_entry(info, 'ghr3gk8dez4')
where ...