MongoDB createIndex compound unique index on array field

Question

I'm trying to create a unique index in MongoDB that enforces uniqueness based on the existence and non-undefined nature of a field (barcodes) in my skus collection. Here's the index creation query I'm using:

db.skus.createIndex({ tenant_id: 1, seller_id: 1, barcodes: 1 }, { unique: true, name: "tenant_seller_seller_sku_code_barcode_1", partialFilterExpression: { barcodes: { $exists: true } } })

MongoServerError[DuplicateKey]: Index build failed: 796ad1b8-4527-47dd-8cd8-8b498e29099f: Collection product_service_db.skus ( 1cfaec4d-475e-42d7-ae23-6183651f2203 ) :: caused by :: E11000 duplicate key error collection: product_service_db.skus index: tenant_seller_seller_sku_code_barcode_1 dup key: { tenant_id: "1", seller_id: "133", barcodes: undefined }

db.skus.createIndex(
  {
    tenant_id: 1,
    seller_id: 1,
    barcodes: 1
  },
  {
    unique: true,
    name: "tenant_seller_seller_sku_code_barcode_1",
    partialFilterExpression: {
      barcodes: { $exists: true, $ne: undefined }
    }
  }
)

MongoServerError[BadValue]: Error in specification { unique: true, name: "tenant_seller_seller_sku_code_barcode_1", partialFilterExpression: { barcodes: { $exists: true, $ne: undefined } }, key: { tenant_id: 1, seller_id: 1, barcodes: 1 } } :: caused by :: unknown operator: $ne

It seems MongoDB doesn't recognize $ne: undefined in the partialFilterExpression. How can I correctly specify the condition to create a unique index based on the existence and non-undefined nature of the barcodes field?

Collection Sample

{
  "_id": ObjectId("..."),
  "tenant_id": "1",
  "seller_id": "133",
  "barcodes": ["barcode1", "barcode2"]
},
{
  "_id": ObjectId("..."),
  "tenant_id": "1",
  "seller_id": "134",
  "barcodes": null
},
{
  "_id": ObjectId("..."),
  "tenant_id": "2",
  "seller_id": "135"
}

Wernfried Domscheit · Accepted Answer · 2024-06-20 10:48:36Z

See Create a Partial Index

The partialFilterExpression option accepts a document that specifies the filter condition using:

equality expressions (i.e. field: value or using the $eq operator),

$exists: true expression,

$gt, $gte, $lt, $lte expressions,

$type expressions,

$and operator,

$or operator,

$in operator

I think $type should work:

partialFilterExpression: {
   barcodes: { 
      $exists: true, 
      $type: [ "string", "null", "array" ]
   }
}

Be aware to this: https://stackoverflow.com/questions/68255363/or-with-if-and-in-mongodb/68255564#68255564

Stack Exchange Network

MongoDB createIndex compound unique index on array field

1 Answer 1

Your Answer

Hot Network Questions

MongoDB createIndex compound unique index on array field

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions