javascript - 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

Question

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 ?

question from:https://stackoverflow.com/questions/65906197/postgresql-12-2-with-sequelize-6-3-upsert-a-array-of-data-and-want-on-conflit

Answer 1

Waitting for answers