CODEWITHSUNDEEP
mongodbindexing
Pawan
Pawan

Reputation: 179

in mongodb, how to create a unique index for a list of documents?

I have an array of documents like this:

[
  {
    _id: ObjectId("63845afd1f4ec22ab0d11db9"),
    ticker: 'ABCD',
    aggregates: [
      { date: '2022-05-20' },
      { date: '2022-05-20' },
      { date: '2022-05-20' }
    ]
  }
]

How may I create an unique index on aggregates.date, so user may not push a duplicate date into array aggregates.

My existing aggregates are as follows:

db.aggregates_1_day.getIndexes()
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  { v: 2, key: { ticker: 1 }, name: 'ticker_1', unique: true },
  {
    v: 2,
    key: { 'aggregates.date': 1 },
    name: 'aggregates.date_1',
    unique: true
  }
]

Upvotes: 0

Views: 490

Answers (1)

R2D2
R2D2

Reputation: 10697

Unique index ensure no duplicates across documents , but do not enforce uniqness for objects in array in same collection document.

But you have few other options here:

1. Do not use $push , but use $addToSet instead to add unique objects inside aggregates array of objects:

    db.collection.update({},
    {
    "$addToSet": {
    "aggregates": {
     date: "2022-05-20"
      }
     }
   })

note: $addToSet only ensures that there are no duplicate items added to the set and does not affect existing duplicate elements.

Playground

2. You can configure schema validation:

   > db.runCommand({collMod:"aggregates_1_day", validator: {$expr:{$eq:[{$size:"$aggregates.date"},{$size:{$setUnion:"$aggregates.date"}}]}}})
   > db.aggregates_1_day.insert({aggregates:[{date:1}]}) /* success */
   > db.aggregates_1_day.update({},{ '$push' : { 'aggregates':{date:1}}})
   WriteResult({
    "nMatched" : 0,
    "nUpserted" : 0,
    "nModified" : 0,
    "writeError" : {
        "code" : 121,
        "errmsg" : "Document failed validation"
    }
   })
   >

more details in the mongoDB ticket

Note: In this approach you will need to clean the duplicates in advance otherways the validation will not allow to $push new objects at all.

In case you dont like it you can remove validation with:

   db.runCommand({
     collMod: "aggregates_1_day",
     validator: {},
     validationLevel: "off"
   })

3. You can use update/aggregation as follow:

   db.collection.update({},
   [
   {
    $set: {
    aggregates: {
     $cond: [
       {
        $in: [
          "2022-02-02",
          "$aggregates.date"
        ]
       },
       "$aggregates",
      {
        $concatArrays: [
          "$aggregates",
          [
            {
              date: "2022-02-02"
            }
          ]
        ]
       }
      ]
     }
    }
   }
   ])

Explained:

  1. Add the object to the array only if do not exist in the array of objects.

Playground3

Upvotes: 2

Related Questions