Insert multiple documents on duplicate update existing document with the new document?-CodePudding

What is the correct method for inserting multiple documents, say 5,000 of them in one command, on duplicate unique index, updating existing documents with new documents on all fields?

For instance, out of the 5,000 documents, 1,792 of them are new with no duplicates by unique indexes so they are inserted, and 3,208 of them have duplicates in the collection by unique indexes which should be replaced into the existing ones by all values.

I tried insertMany() with the unordered option but it seems to skip duplicate documents.

And then updateMany() with upsert:true isn't for inserting multiple documents but only updating certain fields in a collection?

Is this possible at all?

========Example=========

For a business collection with unique index of field "name":

{"name":"Google", "address":"...", "employees":38571, "phone":12345}
{"name":"Microsoft", "address":"...", "employees":73859, "phone":54321}
{"name":"Apple", "address":"...", "employees":55177, "phone":88888}
{"name":"Meta", "address":"...", "employees":88901, "phone":77777}

Now we want to update the collection with these 4 documents:

{"name":"Apple", "address":"...", "employees":55177, "phone":22222}
{"name":"Dell", "address":"...", "employees":77889, "phone":11223}
{"name":"Google", "address":"...", "employees":33333, "phone":44444}
{"name":"IBM", "address":"...", "employees":77777, "phone":88888}

In MySQL, I could just do this in one query:

INSERT INTO business (name, address, employees, phone)
    VALUES
    ('Apple', '...', 55177, 22222),
    ('Dell', '...', 77889, 11223),
    ('Google', '...', 33333, 44444),
    ('IBM', '...', 77777, 88888)
    AS new
ON DUPLICATE KEY UPDATE
    address = new.address
    employees = new.employees
    phone = new.phone

And the collection documents become:

{"name":"Google", "address":"...", "employees":33333, "phone":44444} # updated
{"name":"Microsoft", "address":"...", "employees":73859, "phone":54321} # no change
{"name":"Apple", "address":"...", "employees":55177, "phone":22222} # updated
{"name":"Meta", "address":"...", "employees":88901, "phone":77777} # no change
{"name":"Dell", "address":"...", "employees":77889, "phone":11223} # inserted
{"name":"IBM", "address":"...", "employees":77777, "phone":88888} # inserted

How do I do this in MongoDB?

CodePudding user response：

You probably just need the $merge. Put the documents you need to go through into another collection(says toBeInserted). $merge toBeInserted into the existing collection.

db.toBeInserted.aggregate([
  {
    "$project": {
      // select the relevant fields
      _id: 0,
      name: 1,
      address: 1,
      employees: 1,
      phone: 1
    }
  },
  {
    "$merge": {
      "into": "companies",
      "on": "name",
      "whenMatched": "merge",
      "whenNotMatched": "insert"
    }
  }
])

Mongo Playground