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

In DB I have tableuser_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?

• 2
  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 newitemId column to your table.

  Bergi

  – Bergi

  2024-08-18 10:13:54 +00:00

  CommentedAug 18, 2024 at 10:13

1 Answer1

Start asking to get answers

Find the answer to your question by asking.

Explore related questions

See similar questions with these tags.