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:
<code>{ itemGroup: 'ANIME', itemId: 55555 }
</code>
<code>{ itemGroup: 'ANIME', itemId: 55555 }
</code>
{ 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.
<code>const userCollection = await db.query(
'SELECT collection FROM user_collection WHERE user_id = $1',
[1],
);
</code>
<code>const userCollection = await db.query(
'SELECT collection FROM user_collection WHERE user_id = $1',
[1],
);
</code>
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.
<code>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),
]);
</code>
<code>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),
]);
</code>
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?