How to remove object by value from a JSONB type array?

Question

I want to remove a JSONB object by their unique 'id' value from a JSONB array. I am no expert at writing SQL code, but I managed to write the concatenate function.

For an example: Remove this object from an array below.

  {
      "id": "ad26e2be-19fd-4862-8f84-f2f9c87b582e",
      "title": "Wikipedia",
      "links": [
          "https://en.wikipedia.org/1",
          "https://en.wikipedia.org/2"
      ]
    },

Schema:

CREATE TABLE users (
    url text not null,
    user_id SERIAL PRIMARY KEY,
    name VARCHAR,
    list_of_links jsonb default '[]'
);

list_of_links format:

[
  {
      "id": "ad26e2be-19fd-4862-8f84-f2f9c87b582e",
      "title": "Wikipedia",
      "links": [
          "https://en.wikipedia.org/1",
          "https://en.wikipedia.org/2"
      ]
    },
      {
      "id": "451ac172-b93e-4158-8e53-8e9031cfbe72",
      "title": "Russian Wikipedia",
      "links": [
          "https://ru.wikipedia.org/wiki/",
          "https://ru.wikipedia.org/wiki/"
      ]
    },
      {
      "id": "818b99c8-479b-4846-ac15-4b2832ec63b5",
      "title": "German Wikipedia",
      "links": [
          "https://de.wikipedia.org/any",
          "https://de.wikipedia.org/any"
      ]
    },
    ...
]

The concatenate function:

update users set list_of_links=(
     list_of_links || (select *
         from jsonb_array_elements(list_of_links) 
         where value->>'id'='ad26e2be-19fd-4862-8f84-f2f9c87b582e'
          )
  )
where url='test'
returning *
;

Patrick · Accepted Answer · 2022-04-25 10:09:16Z

1

Your json data is structured so you have to unpack it, operate on the unpacked data, and then repack it again:

SELECT u.url, u.user_id, u.name, 
       jsonb_agg(
           jsonb_build_object('id', l.id, 'title', l.title, 'links', l.links)
       ) as list_of_links
FROM users u
CROSS JOIN LATERAL jsonb_to_recordset(u.list_of_links) AS l(id uuid, title text, links jsonb)
WHERE l.id != 'ad26e2be-19fd-4862-8f84-f2f9c87b582e'::uuid
GROUP BY 1, 2, 3

The function jsonb_to_recordset is a set-returning function so you have to use it as a row source, joined to its originating table with the LATERAL clause so that the list_of_links column is available to the function to be unpacked. Then you can delete the records you are not interested in using the WHERE clause, and finally repack the structure by building the record fields into a jsonb structure and then aggregating the individual records back into an array.

edited Apr 25, 2022 at 10:09

answered Apr 25, 2022 at 10:01

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

1 Comment

Itgelt Gankhulug Over a year ago

do you know how to implement my solution in SQL

Itgelt Gankhulug · Accepted Answer · 2022-04-25 14:49:16Z

I wrote this on JS but that does not matter to how it works. Essentially, its getting all the items from the array, then finding the matching id which returns an index. And using that index, I use "-" operator which takes the index and removes it from the array. Sorry if my grammar is bad.

    //req.body is this JSON object
    //{"url":"test", "id": "ad26e2be-19fd-4862-8f84-f2f9c87b582e"}
    var { url, id } = req.body;
    pgPool.query(
        `
                select list_of_links
                from users
                where url=$1;
        `,
        [url],
        (error, result) => {
            //block code executing further if error is true
            if (error) {
                res.json({ status: "failed" });
                return;
            }
            if (result) {
                // this function just returns the index of the array element where the id matches from request's id
                // 0, 1, 2, 3, 4, 5
                var index_of_the_item = result.rows.list_of_links
                    .map(({ id: db_id }, index) =>
                        db_id === id ? index : false
                    )
                    .filter((x) => x !== false)[0];
                //remove the array element by it's index
                pgPool.query(
                    `
                    update users
                    set list_of_links=(
                       list_of_links - $1::int
                    )
                    where url=$2
                    ;
                   `,
                    [index_of_the_item, url], (e, r) => {...}

                );
            }
        }
    );

Collectives™ on Stack Overflow

How to remove object by value from a JSONB type array?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related