Node-postgres query to remove object from array

Question

I'm building backend with Express and TypeScript. DB is Postgres, client is node-postgres.

In DB I have table user_collection. This table has column collection of type JSONB. Collection column contains an array of objects like this:

{ itemGroup: 'ANIME', itemId: 55555 }

There could be a lot of objects in this array and what I need is the query that will remove object from array by id. I've already tried few queries but every time I got an error. So for now my workaround has 2 steps. First is to get full collection.

const userCollection = await db.query(
  'SELECT collection FROM user_collection WHERE user_id = $1',
  [1],
);

Second is to filter this array and save filtered array in collection.

const updatedCollection = existedCollection.filter(
  (item: any) => item.itemId !== 11111,
);    
db.query('UPDATE user_collection SET collection = $2 WHERE user_id = $1', [
  1,
  JSON.stringify(updatedCollection),
]);

Is it possible to use one query and leave filtering for DB?

Yes, it's possible, but you'd be much better off by not storing an array in each row. Store one object per row, add a new itemId column to your table. — Bergi
– Bergi, Commented Aug 18, 2024 at 10:13

JohnH · Accepted Answer · 2024-08-18 10:13:36Z

2

The following removes the selected items from collection for the specified user_id in a single query:

db.query("UPDATE user_collection
            SET collection =
              JSONB_PATH_QUERY_ARRAY(collection,
                                     ('$[*] ? (@.itemId != ' || $2 || ')')::JSONPATH)
            WHERE user_id = $1", [1, 11111]);

answered Aug 18, 2024 at 10:13

JohnH

3,2932 gold badges6 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Node-postgres query to remove object from array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related