I have two collections - col1 and col2 under the same db. Those collections contain documents that has two fields - _id and arr. I'm trying to find matches for same _id and whenever a match was found I'd like to concatenate the two arr arrays.
In col1 I have the following document
{'_id': 'shalom', 'arr': [1, 2, 3]}
In col2 I have the following document
{'_id': 'shalom', 'arr': [4, 5, 6]}
I want to create a new document in col1 that will look like
{'_id': 'shalom', 'arr': [1, 2, 3, 4, 5, 6]}
What I did up to now (which not works) is
merge_query = {'into': col1,
'on': '_id',
'whenMatched': [
{'$addFields': {
'processed_words': {'$add': ['$arr', '$$new.arr']}}}],
'whenNotMatched': 'insert'}
col2.aggregate([{'$project': project_query},
{'$merge': merge_query}])
When running this I'm getting the following exception
pymongo.errors.OperationFailure: $add only supports numeric or date types, not array, full error: {'ok'...
So it looks like it's almost there, just need to be able to support arrays concatenation.
You can use $concatArrays or $setUnion in this way:
First $lookup to get the other collection array and create a new 'auxiliar' array called newArray.
Here, $lookup returns an array, so is necessary use $arrayElementAt to get the first (and unique) position.
Then use $concatArrays or $setUnion to concat fields and remove the 'auxiliar' array.
db.coll1.aggregate([
{
"$lookup": {
"from": "coll2",
"localField": "_id",
"foreignField": "_id",
"as": "newArray"
}
},
{
"$set": {
"newArray": {
"$arrayElemAt": [
"$newArray",
0
]
}
}
},
{
"$set": {
"arr": {
"$setUnion": [
"$arr",
"$newArray.arr"
]
}
}
},
{
"$project": {
"newArray": 0
}
}
])
Example here
I have checked this and working fine. When you join arrays you need to use $concatArrays. Operation $add is for numbers which will sum it Eg (1+1=2)
db.col2.aggregate([
{
$merge:{'into': "col1",
'on': '_id',
'whenMatched': [
{'$addFields': {
'processed_words': {'$concatArrays': ['$arr', '$$new.arr']}}}],
'whenNotMatched': 'insert'
}
}
])