insert for $out failed

Question

My documents in my DB are stored an array of results for each user.

Data types:

age: {type: Number}, 
gender: {type: String},
result: [{ game: String,time: Number, level: Number, mistakes: Number,moves: Number }]

In order to analyze my data I want to unwind the results, For example the following document:

{"_id":"5ce58a662f6fcb3b782013e3","age":10,"gender":"Male","result":[{"_id":"5ce58a662f6fcb3b782013e9","game":"puzzle","time":20,"level":3,"mistakes":5,"moves":50},{"_id":"5ce58a662f6fcb3b782013e8","game":"puzzle","time":20,"level":3,"mistakes":5,"moves":50},{"_id":"5ce58a662f6fcb3b782013e7","game":"puzzle","time":20,"level":3,"mistakes":5,"moves":50},{"_id":"5ce58a662f6fcb3b782013e6","game":"memory","time":20,"level":3,"mistakes":5,"moves":50},{"_id":"5ce58a662f6fcb3b782013e5","game":"memory","time":20,"level":3,"mistakes":5,"moves":50},{"_id":"5ce58a662f6fcb3b782013e4","game":"memory","time":20,"level":3,"mistakes":5,"moves":50}],"__v":0}]

will be devided to a 6 docs, and stored on a new collection with the same userId.

I've tried to run:

 db.userschemes.aggregate([{ "$unwind": "$result"},{ $out : "newcollection" }])

but got the following:

assert: command failed: { "ok" : 0, "errmsg" : "insert for $out failed: { connectionId: 1, err: \"E11000 dup licate key error index: vpdata.tmp.agg_out.2.$id dup key: { : ObjectId('5ce58a 662f6fcb3b782013e3') }\", code: 11000, n: 0, ok: 1.0 }", "code" : 16996 } : aggregate failed _getErrorWithCode@src/mongo/shell/utils.js:25:13 doassert@src/mongo/shell/assert.js:16:14 assert.commandWorked@src/mongo/shell/assert.js:290:5 DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5 @(shell):1:1

2019-05-26T19:04:05.395+0200 E QUERY [thread1] Error: command failed: { "ok" : 0, "errmsg" : "insert for $out failed: { connectionId: 1, err: \"E11000 dup licate key error index: vpdata.tmp.agg_out.2.$id dup key: { : ObjectId('5ce58a 662f6fcb3b782013e3') }\", code: 11000, n: 0, ok: 1.0 }", "code" : 16996 } : aggregate failed : _getErrorWithCode@src/mongo/shell/utils.js:25:13 doassert@src/mongo/shell/assert.js:16:14 assert.commandWorked@src/mongo/shell/assert.js:290:5 DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5 @(shell):1:1

How can I avoid this? I'm understand the issue that I have multiple docs with the same id but this is exactly what I want.

Any workaround?

mickl mickl · Accepted Answer · 2019-05-26T17:21:47

Using $out you're creating new collection named newcollection. MongoDB requires every _id to be unique. That constraint cannot be dropped, so as you're $unwind-ing an array there might be duplicated _id values in a final result set.

As a workaround you can introduce $project stage and move current _id into different field so that there will be no unique constraint on it. MongoDB will generate new _id values for all documents, try:

db.userschemes.aggregate([
    { "$unwind": "$result"},     
    { $addFields: { userId : "$_id" } },
    { $project: { _id: 0 } },
    { $out : "newcollection" }
])

insert for $out failed

1 Answers