Remove entire json object from JSON array

Question

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;

Do you really want to set the column to an array, or do you want to set the info->'contact' object property to an array? — Bergi
– Bergi, Commented May 9, 2022 at 19:51
This would be so much easier with a properly normalized data model. — user330315
– user330315, Commented May 9, 2022 at 20:18
@a_horse_with_no_name: What do you mean by normalized? This is as normal as jsonb gets yes? — Grogu
– Grogu, Commented May 9, 2022 at 20:20
Using json is de-normalizing. With a properly normalized model it would be as easy as delete from contacts where id = 'ghr3gk8dez4' — user330315
– user330315, Commented May 9, 2022 at 20:26
@a_horse_with_no_name : yes if I wanted to delete the entire row I could use the id of the row which is present there but that's not what I'm trying to do here. I'm just wanting to update the list of contacts of a user by deleting the contact the user wants to delete — Grogu
– Grogu, Commented May 9, 2022 at 20:34

Bergi · Accepted Answer · 2022-05-09 22:03:57Z

1

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

edited May 9, 2022 at 22:03

answered May 9, 2022 at 19:52

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

6 Comments

Grogu Over a year ago

This deletes everything! This is not the task. The task is to update the list of contacts by deleting whatever contacts the user is wanting the delete.

Bergi Over a year ago

@Grogu Yes, you're updating the whole info, not just the contacts. I just solved the non-null violation. To update the object, use jsonb_set(info, 'contacts', …)

Grogu Over a year ago

how would your last example work in real time?

Bergi Over a year ago

@Grogu Like this

Grogu Over a year ago

But this deletes the entire list of contacts. How to just delete the one the user wants to delete?

|

user330315 · Accepted Answer · 2022-05-09 20:32:54Z

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 ...

Collectives™ on Stack Overflow

Remove entire json object from JSON array

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related