I'm trying to update the list of contacts by deleting whatever contact is requested to be deleted by user input. In other words, trying to remove an entire JSON object from a JSON array in my PostgreSQL database from a Node.js script, but I get error

error: null value in column "info" of relation "user_emails" violates not-null constraint

I double-checked and the value and everything is there. When I try it here online it works, but on my server it returns the error. How can I fix this?

    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": []
}');

this is my UPDATE

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;

CodePudding user response：

I don't think there is an efficient way to do that using only built-in functions.

There is an operator #- that removes an element by specifying the path, e.g. info #- '{contacts, 0}' would do what you want. However, it's not straight forward to build such a "path array" directly.

I would write a function that finds the index of the contact to be deleted and generates the path array:

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;

The -1 is necessary because SQL uses 1-based numbering, but in JSON arrays start at zero.

With that function you can then do:

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

CodePudding user response：

jsonb_agg, like so many other aggregate functions, returns NULL if there are no rows to aggregate. You might be looking to COALESCE it to an empty array instead:

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