Error deleting json object from array in Postgres

Question

I have a Postgres table timeline with two columns:

user_id (varchar)
items (json)

This is the structure of items json field:

[
  {
    itemId: "12345",
    text: "blah blah"
  },
  //more items with itemId and text
]

I need to delete all the items where itemId equals a given value. e.g. 12345

I have this working SQL:

UPDATE timeline 
SET items = items::jsonb - cast(( 
  SELECT position - 1 timeline, jsonb_array_elements(items::jsonb)
  WITH ORDINALITY arr(item_object, position) 
  WHERE item_object->>'itemId' = '12345') as int)

It works fine. It only fails when no items are returned by the subquery i.e. when there are no items whose itemId equals '12345'. In those cases, I get this error:

null value in column "items" violates not-null constraint

How could I solve this?

Akhilesh Mishra · Accepted Answer · 2020-08-13 10:31:58Z

2

Try this:

update timeline 
set items=(select 
           json_agg(j) 
          from json_array_elements(items) j 
          where j->>'itemId' not in ( '12345')
          );

DEMO

edited Aug 13, 2020 at 10:31

answered Aug 13, 2020 at 9:44

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

3 Comments

Héctor Valls Over a year ago

Thank you! What about performance? Isn't this solution too heavy when there are many entries?

Mike Organek Over a year ago

@Héctor This answer is much better than mine. The performance should be about the same as your original.

Héctor Valls Over a year ago

Thanks for your comment. Certainly, it's more readable as well. Thank you very much anyway.

Mike Organek · Accepted Answer · 2020-08-13 10:56:18Z

The problem is that when null is passed to the - operator, it results in null for the expression. That not only violates your not null constraint, but it is probably also not what you are expecting.

This is a hack way of getting past it:

UPDATE timeline 
SET items = items::jsonb - coalesce(
   cast(( 
     SELECT position - 1 timeline, jsonb_array_elements(items::jsonb)
     WITH ORDINALITY arr(item_object, position) 
     WHERE item_object->>'itemId' = '12345') as int), 99999999)

A more correct way to do it would be to collect all of the indexes you want to delete with something like the below. If there is the possibility of more than one userId: 12345 within a single user_id row, then this will either fail or mess up your items(I have to test to see which), but at least it updates only rows with the 12345 records.

WITH deletes AS (
  SELECT t.user_id, e.rn - 1 as position
    FROM timeline t
   CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(t.items)
          WITH ORDINALITY as e(jobj, rn)
   WHERE e.jobj->>'itemId' = '12345'
)
UPDATE timeline 
   SET items = items - d.position
  FROM deletes d
 WHERE d.user_id = timeline.user_id;

Collectives™ on Stack Overflow

Error deleting json object from array in Postgres

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related