[SOLVED] Node-postgres query to remove object from array

Node-postgres query to remove object from array

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?

Solution

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