Error from mongo aggregate pipeline with $merge

user_8054597 0
Viewed 754

I have a simple collection alert with _id and observables array

e.g.

{
  _id: '60d496b2d08d5f0630dea368',
  observables: ['10d496b2d08d5f0630dea368', '20d496b2d08d5f0630dea368'],
}

I created query to create new collection without array in format like:

{
  _id: <default id>,
  alertId: '60d496b2d08d5f0630dea368',
  observableId: '10d496b2d08d5f0630dea368',
{
  _id: <default id>,
  alertId: '60d496b2d08d5f0630dea368',
  observableId: '20d496b2d08d5f0630dea368',
}

Also, to prevent from duplication, unique index was created

collection.createIndex({ observableId: 1, alertId: 1 }, { unique: true });

And here is my query:

db.collection.aggregate([
  {
    "$unwind": "$observables"
  },
  {
    "$project": {
      _id: 0,
      observableId: "$observables",
      alertId: "$_id"
    }
  },
  {
    "$merge": {
      into: {
        db: "some-db",
        coll: "some-coll"
      },
      on: [
        "observableId",
        "alertId"
      ]
    }
  }
])

And as result got an error *MongoError: Cannot find index to verify that join fields will be unique

But the index was created, so any idea how to fix this error?

Link to mongoplayground

aggregation-framework mongodb indexing
2 Answers

You can but do not need to create that index, what you need is to generate a unique _id value.

Since you are merging into an existing collection and not outputting into a new one, I suggest you create an _id field that is build from the concatenation of the _id and observables fields

e.g. using $toString and $concat:

"_id": {$concat: [{$toString: "$_id"}, "-", {$toString: "$observables"}]}

This way you have a repeatable process, where running twice will result in the same _id values.

You can merge into existing collection using:

{ $merge: { into: ..., on: "_id", whenMatched: "keepExisting", whenNotMatched: "insert" } }

Full example:

db.collection.aggregate([
  {
    "$unwind": "$observables"
  },
  {
    "$project": {
      _id: {$concat: [
        {$toString: "$_id"},
        "-",
        {$toString: "$observables"}
      ]},
      observableId: "$observables",
      alertId: "$_id"
    }
  },
  {
    "$merge": {
      into: {db: "some-db", coll: "some-coll"},
      on: "_id"
    }
  }
])

Regarding the way you implemented it, my guess is the index is on the wrong database or collection i.e. not on the output collection.

user_383680

The problem was in collation.

Index collation and query (in my case db.collection.aggregate) should have the same collation

user_80545970
Related