'PostgreSQL 12.2 with Sequelize 6.3 - UPSERT a array of data and want ON CONFLIT DO UPDATE to merge current existing JSONB and EXCLUDED JSONB

I'm currently using this query to insert or update my array of data when retrieving my list :

    try {
      const query = `INSERT INTO "Tables" ("id","tomerge","createdAt","updatedAt") VALUES ${UpsertData
        .map((_) => '(?)')
        .join(
          ',',
        )} ON CONFLICT ("id") DO UPDATE SET "tomerge"= "Tables"."tomerge" || excluded."tomerge", "updatedAt"=excluded."updatedAt";`;

      return await sequelize.query(query, {
        replacements: UpsertData,
        type: Sequelize.QueryTypes.INSERT,
      });
    } catch (error) {
      throw new DatabaseError(error);
    }

With my upsertData format :

[{
  id: uuid,
  tomerge: { // JSON B
             [id]: score,
           },
  createdAt: new Date(),
  updatedAt: new Date(),
},
{
  ...
}]

This code is currently working but I want the line

"tomerge"= "Tables"."tomerge" || excluded."tomerge"

to MERGE my current json B with the new json B or updating the already existing line inside it

I'm searching on this case but I don't find anything that really work for jsonB, am I missing some case ?

javascriptsqlarrayspostgresqlsequelize.js


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions