CODEWITHSUNDEEP
mongodbconcatenationpymongo
Lior Magen
Lior Magen

Reputation: 1573

Concatenating MongoDB arrays from different collection for same ID

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.

Upvotes: 1

Views: 650

Answers (2)

varman
varman

Reputation: 8894

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'
    }
    }
])

Upvotes: 1

J.F.
J.F.

Reputation: 15205

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

Upvotes: 1

Related Questions