mongdb ensure uniqueness on two fields both ways

Question

Say I have the fields a and b. I want to have a compound uniqueness where if a: 1, b: 2, I would not be able to do a: 2, b: 1.

The reason I want this is because I'm making a "friends list" kind of collection, where if a is connected to b, then it's automatically the reverse as well.

is this possible on a schema level or do I need to do queries to check.

Answer 1

If you don't need to differentiate between requester and requestee, you could sort the values before saving or querying so that your two fields a and b have a predictable order for any pair of friend IDs (and you can take advantage of the unique index constraint).

For example, using the mongo shell:

• Create a helper function to return friend pairs in predictable order:

  function friendpair (friend1, friend2) {
      if ( friend1 < friend2) {
          return ({a: friend1, b: friend2})
      } else {
          return ({a: friend2, b: friend1})      
      }
  }
  

• Add a compound unique index:

  > db.friends.createIndex({a:1, b:1}, {unique: true});
  {
      "createdCollectionAutomatically" : true,
      "numIndexesBefore" : 1,
      "numIndexesAfter" : 2,
      "ok" : 1
  }
  

• Insert unique pairs (should work)

  > db.friends.insert(friendpair(1,2))
  WriteResult({ "nInserted" : 1 })
  
  > db.friends.insert(friendpair(1,3))
  WriteResult({ "nInserted" : 1 })
  

• Insert non-unique pair (should return duplicate key error):

  > db.friends.insert(friendpair(2,1))
  WriteResult({
      "nInserted" : 0,
      "writeError" : {
          "code" : 11000,
          "errmsg" : "E11000 duplicate key error collection: test.friends index: a_1_b_1 dup key: { : 1.0, : 2.0 }"
      }
  })
  

• Search should work in either order:

  db.friends.find(friendpair(3,1)).pretty()
  { "_id" : ObjectId("5bc80ed11466009f3b56fa52"), "a" : 1, "b" : 3 }
  
  db.friends.find(friendpair(1,3)).pretty()
  { "_id" : ObjectId("5bc80ed11466009f3b56fa52"), "a" : 1, "b" : 3 }
  

• Instead of handling duplicate key errors or insert versus update, you could also use findAndModify with an upsert since this is expected to be a unique pair:

  > var pair = friendpair(2,1)
  > db.friends.findAndModify({
      query: pair,
      update: {
          $set: {
              a : pair.a,
              b : pair.b
          },
          $setOnInsert: { status: 'pending' },
      },
      upsert: true
  })
  
  {
      "_id" : ObjectId("5bc81722ce51da0e4118c92f"),
      "a" : 1,
      "b" : 2,
      "status" : "pending"
  }
  

Answer 2

Doesn't seem like you can do a unique on the entire array's values so I'm doing a kind of work around. I'm using the $jsonSchema as follows:

{
    $jsonSchema:
        {
            bsonType:
                "object",
            required:
                [
                    "status",
                    "users"
                ],
            properties:
                {
                    status:
                    {
                        enum:
                        [
                            "pending",
                            "accepted"
                        ],
                        bsonType:
                            "string"
                    },
                    users:
                        {
                            bsonType:
                                "array",
                            description:
                                "references two user_id",
                            items:
                                {
                                    bsonType:
                                        "objectId"
                                },
                            maxItems: 
                                2,
                            minItems: 
                                2,
                        },
                }
        }
}

then I will use $all to find the connected users, e.g.

db.collection.find( { users: { $all: [ ObjectId1, ObjectId2 ] } } )