Currently working on the backend using Express and TypeScript. Postgres is the chosen database with node-postgres as the client.
Within the database, there is a table called user_collection. One of its columns is collection of type JSONB, which holds an array of objects structured like this:
{ itemGroup: 'ANIME', itemId: 55555 }
This array can contain multiple objects, and I'm in need of a query that can remove an object from the array based on its id. I've attempted several queries, but keep running into errors. As a temporary solution, I have devised a two-step workaround method. The first step involves retrieving the entire collection.
const userCollection = await db.query(
'SELECT collection FROM user_collection WHERE user_id = $1',
[1],
);
The second step entails filtering this array and updating the filtered version back into the 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),
]);
I'm wondering if it's possible to achieve this with just one query and let the database handle the filtering process?