Combine two array of objects, based on a common field, into one array in aggregation query in mongodb

Question

I have the below schema

{
 _id: ObjectId,
 account: [{
   accountId: String,
   commonId: string
  }
 ],
 sessions: [{
   sessionId: String,
   commonId: String
  }
 ]
}

I want to combine both these array into one array of objects in aggregate query, where the result should be

{
 _id: ObjectId,
 merged: [{
   accountId: String,
   stringId: String,
   commonId: string
  }
 ],
}

Sample document:

{
  _id: 0,
  account: [
  { 
    accountId: '1234',
    commonId: '0'
  },
  {
    accountId: '1235',
    commonId: '1'
  },
  {
    accountId: '1236',
    commonId: '2'
  }
 ],
 sessions: [
  {
   sessionId: '6781',
   commonId:'0'
  },
  {
   sessionId: '6782',
   commonId:'1'
  },
  {
   sessionId: '6783',
   commonId:'2'
  } 
 ]

expected output:

{
  _id: 0,
  merged: [
  { 
    accountId: '1234',
    sessionId: '6781',
    commonId: '0'
  },
  {
    accountId: '1235',
    sessionId: '6782',
    commonId: '1'
  },
  {
    accountId: '1236',
    sessionId: '6783',
    commonId: '2'
  }
 ],

this or unwinded version of this

How can i do this? Any help is appreciated

account and sessions will always have the same size / order? Or we need to check commonId to merge items? — Valijon
– Valijon, Commented Apr 29, 2020 at 9:25
@Valijon it can be different, So we need to check commonId to merge — Jerin A Mathews
– Jerin A Mathews, Commented Apr 29, 2020 at 10:35

Ashh · Accepted Answer · 2020-04-29 09:27:31Z

1

You can use below aggregation

db.collection.aggregate([
  { "$project": {
    "merged": {
      "$map": {
        "input": "$account",
        "in": {
          "accountId": "$$this.accountId",
          "commonId": "$$this.commonId",
          "sessionId": {
            "$arrayElemAt": [
              "$sessions.sessionId",
              {
                "$indexOfArray": ["$sessions.commonId", "$$this.commonId"]
              }
            ]
          }
        }
      }
    }
  }}
])

MongoPlayground

answered Apr 29, 2020 at 9:27

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Valijon · Accepted Answer · 2020-04-29 09:36:52Z

Try the query below with $mergeObjects operator:

db.collection.aggregate([
  {
    $project: {
      merged: {
        $map: {
          input: "$account",
          as: "acc",
          in: {
            $mergeObjects: [
              "$$acc",
              {
                $arrayElemAt: [
                  {
                    $filter: {
                      input: "$sessions",
                      as: "ses",
                      cond: {
                        $eq: [
                          "$$ses.commonId",
                          "$$acc.commonId"
                        ]
                      }
                    }
                  },
                  0
                ]
              }
            ]
          }
        }
      }
    }
  }
])

MongoPlayground

Collectives™ on Stack Overflow

Combine two array of objects, based on a common field, into one array in aggregation query in mongodb

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related