MongoDB - conditional uniqueness constraints on different "id" fields

Question

I have a requirement to enforce uniqueness in documents in a MongoDB collection.

My documents have an "id" field and a "name" field, and several other fields. The "id" field is NOT unique.

My requirement is such that the "name" should be unique across different values of "id". However, there can be multiple documents with the same "id" and same "name". (There can be multiple "name"s in the same "id").

For example:
Following documents are allowed (same id, same/different names):

{ "id" : "1", "name": "Alice"}  
{ "id" : "1", "name": "Bob"}  
{ "id" : "1", "name": "Alice"}

However following are not allowed (same name, different ids):

{ "id" : "1", "name": "Alice"}  
{ "id" : "2", "name": "Alice"}

Is there anyway possible to enforce this on the database itself, and not in application logic?

Answer 1

I think there is no way to "enforce" the rule with existing data by only database means. You may need to do a one-time clean-up if you have data that violates the rule. However, for newly inserted documents, you can do the following in aggregation and merge them into the collection.

1. $unionWith the new documents you wanted to insert(by putting them in $documents
2. $setWindowFields to compute the $denseRank within the partition name and by the order of id
   • For case 1, if they have different names, they will both have rank: 1
   • For case 2, if they have the same name and the same id, they will both have rank: 1
   • For case 3, if they have same name but different id, the new document will have rank > 1
3. $match to select only the documents with rank: 1
4. (cosmetics) $unset the rank field
5. $merge into the collection

db.collection.aggregate([
  {
    "$unionWith": {
      "coll": "collection",
      "pipeline": [
        {
          "$documents": [
            // documents to be inserted here
            {
              "id": "1",
              "name": "Alice"
            }
          ]
        }
      ]
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": "$name",
      "sortBy": {
        "id": 1
      },
      "output": {
        "idRankInName": {
          "$denseRank": {}
        }
      }
    }
  },
  {
    "$match": {
      "idRankInName": 1
    }
  },
  {
    "$unset": "idRankInName"
  },
  {
    "$merge": {
      "into": "collection"
    }
  }
])

Mongo Playground for cases 1 and 2
Mongo Playground for case 3