Postgres: Delete object from anonymous jsonb array element

Question

I have a table with 2 fields:

table documents
  docu_id     uuid
  attachments jsonb

A sample data for the attachments jsonb column would be:

[
 {
    "size": 10,
    "attach_id": "d3a21f904068"
 },{
    "Size": 0.143,
    "attach_id": "5ba4b285565b"
 }
]

I have seen many examples of how to update/delete a jsonb based on field name, but is it possible to delete an anonymous object from an anonymous array where "attach_id" = "X" and "docu_id"="Y":

delete from documents
  where docu_id = "Y" 
    and
    where attachments @> '[{"attach_id": "X"}]'

Do you want to remove the complete row from the table? Or just an element from the array? — user330315
– user330315, Commented Aug 29, 2018 at 19:34
@a_horse_with_no_name just the element from the array. Remove from attachments jsonb array where attach_id = "X" — CommonSenseCode
– CommonSenseCode, Commented Aug 29, 2018 at 19:41

CommonSenseCode · Accepted Answer · 2018-08-31 08:35:18Z

Ok found the solution so I'm sharing it here, (rextester link http://rextester.com/YICZ86369):

Inserting the data

  create table documents(docu_id text, attachments jsonb);
    insert into documents values
    ('001', 
    '[
      {
        "name": "uno",
        "id":"1"
      },
       {
        "name": "dos",
        "id":"2"
      },
       {
        "name": "tres",
        "id":"3"
      }
    ]'
    ),
    ('002', 
    '[
      {
        "name": "eins",
        "id":"1"
      },
       {
        "name": "zwei",
        "id":"2"
      }
    ]'
    );
    select * from documents;

The solution

UPDATE documents
   SET attachments = attachments #- 
          array(
                    SELECT i
                      FROM generate_series(0, jsonb_array_length(attachments) - 1) AS i
                      WHERE (attachments->i->'id' = '"2"')
           )::text[] /* cast as text */
       where docu_id = '002';

select * from documents;

Collectives™ on Stack Overflow

Postgres: Delete object from anonymous jsonb array element

1 Answer 1

Inserting the data

The solution

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Inserting the data

The solution

Comments

Your Answer

Sign up or log in

Post as a guest

Related