Create an Index for a document inside array inside document in MongoDB

Question

Let's say I have this document:

{
  "_id": objectId(),
  "products": [
    {
      "product_id": ""
    }
  ]
}

I would like to make that the key product_id of documents in the array products must be unique for that document in the collection, (i don't want it to affect other documents in the collection).

I tried to use the next command in mongosh console:

db.season.createIndex({products.product_id:1},{unique:true})

But it doesn't work. the console says:

Error: clone(t={}){const r=t.loc||{};return e({loc:new Position("line"in r?r.line:this.loc.line,"column"in r?r.column:...<omitted>...)} could not be cloned.

score 0 · Accepted Answer · answered Mar 08 '23 at 06:24

0

Unfortunately, that is not possible. The index only works across multiple documents. You can have repeating values within the same document.

See this.

answered Mar 08 '23 at 06:24

Khanna111

3,627
1
23
25

oh... that's a shame, thank you tho – Clara Raquel Mar 08 '23 at 06:34

score -1 · Answer 2 · answered Mar 09 '23 at 13:41

The answer from @Khanna111 correctly answers the original question about the current limitation on enforcing uniqueness within an array on a single document. A (longstanding) ticket to track that feature is SERVER-1068.

That said, I believe there is a way to achieve what you want. As noted in one of the comments on that ticket, you can use document validation instead. Using that comment as a guide, I ran the following:

> db.runCommand({collMod:"foo", validator: {$expr:{$eq:[{$size:"$products.product_id"},{$size:{$setUnion:"$products.product_id"}}]}}})
{ ok: 1 }

With this validation in place I was able to successfully insert documents that did not have duplicates. For example:

> db.foo.insert({_id:1, products:[]})
{ acknowledged: true, insertedIds: { '0': 1 } }
> db.foo.insert({_id:2, products:[{product_id:4},{product_id:5}]})
{ acknowledged: true, insertedIds: { '0': 2 } }
> db.foo.insert({_id:3, products:[{product_id:4},{product_id:5}]})
{ acknowledged: true, insertedIds: { '0': 3 } }

It correctly failed when I attempted to insert a (single) document which contained duplicate values in its array:

> db.foo.insert({_id:4, products:[{product_id:4},{product_id:4}]})
Uncaught:
MongoBulkWriteError: Document failed validation
Result: BulkWriteResult {
  result: {
    ok: 1,
    writeErrors: [
      WriteError {
        err: {
          index: 0,
          code: 121,
          errmsg: 'Document failed validation',
          errInfo: {
            failingDocumentId: 4,
            details: {
              operatorName: '$expr',
              specifiedAs: {
                '$expr': {
                  '$eq': [
                    { '$size': '$products.product_id' },
                    {
                      '$size': { '$setUnion': '$products.product_id' }
                    }
                  ]
                }
              },
              reason: 'expression did not match',
              expressionResult: false
            }
          },
          op: {
            _id: 4,
            products: [ { product_id: 4 }, { product_id: 4 } ]
          }
        }
      }
    ],
    writeConcernErrors: [],
    insertedIds: [ { index: 0, _id: 4 } ],
    nInserted: 0,
    nUpserted: 0,
    nMatched: 0,
    nModified: 0,
    nRemoved: 0,
    upserted: []
  }
}

Separately, regarding this:

But it doesn't work. the console says:

Error: clone(t={}){const r=t.loc||{};return e({loc:new Position("line"in r?r.line:this.loc.line,"column"in r?r.column:...<omitted>...)} could not be cloned.

That error message is unrelated to the actual problem at hand. Instead it means that you have some typo in the command. It's impossible to say what it is without seeing the actual command that you are trying to execute, but as an educated guess be sure that you put quotation marks around nested fields when trying to create an index, eg:

> db.foo.createIndex({"products.product_id":1})
products.product_id_1

Create an Index for a document inside array inside document in MongoDB

2 Answers2