[SOLVED] In Sequelize, update all rows where a JSONB object's array contains a given string

In Sequelize, update all rows where a JSONB object's array contains a given string

I am using PostgreSQL. I have a table users with a JSONB column preferences. It can take a shape like so:

{
  pets: ['Cat', 'Dog', 'Goldfish'],
  cars: ['Sedan', 'Van'],
  // Other keys; irrelevant

}

In TypeScript, I have a function like so:

function removePetFromAllUsers(pet: string) {
  // Sequelize query to go here
}

I need a sequelize query to update all rows in the users table whose pets contain the string in the parameter. For instance, any user with "Dog" as a preferred pet should have it removed in their preferences.pets array.

How do I write this query?

I am expecting all rows from which preferences.pets contain the string pet to be updated such that preferences.pets do not contain the string pet anymore.

Solution

You can write a query like so:

await users.update(
    {
      preferences: literal(
        `jsonb_set(preferences, '{pets}', (preferences->'pets') - 'Dog')`,
      ),
    },
    {
      where: {
          { preferences: { pets: { [Op.contains]: `["Dog"]` } } },
      }
    },
  );

Here you can use jsonb_set() and the delete operator -.