MongoDB - How to aggregate collection sub-array objects with its actual object from the other collection

Question

How to aggregate collection sub-array objects with their actual object which is present in the other collection?

Ex: This is how my collections look like

Collection 1 - (Teams)

const Teams = [
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a1',
        teamName: 'World XI A',
        totalPlayers: 15,
        player: [
            {
                playerId: '60f3b0b3f0b9a1b0b4f9b1a2',
                playerName: 'Player A',
                score: 10,
            },
            {
                playerId: '60f3b0b3f0b9a1b0b4f9b1a3',
                playerName: 'Player B',
                score: 20,
            },
        ],
    },
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a4',
        teamName: 'Crackers',
        totalPlayers: 15,
        player: [
            {
                playerId: '60f3b0b3f0b9a1b0b4f9b1a5',
                playerName: 'Player C',
                score: 30,
            },
            {
                playerId: '60f3b0b3f0b9a1b0b4f9b1a6',
                playerName: 'Player D',
                score: 40,
            },
        ],
    },
];

Collection 2 - (Players)

const Players = [
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a2',
        playerName: 'Player A',
        rank: 2,
        totalRuns: 5000,
    },
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a3',
        playerName: 'Player B',
        rank: 1,
        totalRuns: 3050,
    },
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a5',
        playerName: 'Player C',
        rank: 3,
        totalRuns: 2050,
    },
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a6',
        playerName: 'Player D',
        rank: 4,
        totalRuns: 1000,
    },
];

Result: This is how the resultant array should look like

Resultant array

const result = [
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a1',
        teamName: 'World XI A',
        totalPlayers: 15,
        player: [
            {
                playerInfo: {
                    _id: '60f3b0b3f0b9a1b0b4f9b1a2',
                    playerName: 'Player A',
                    rank: 2,
                    totalRuns: 5000,
                },
                playerName: 'Player A',
                score: 10,
            },
            {
                playerInfo: {
                    _id: '60f3b0b3f0b9a1b0b4f9b1a3',
                    playerName: 'Player B',
                    rank: 1,
                    totalRuns: 3050,
                },
                playerName: 'Player B',
                score: 20,
            },
        ],
    },
    {
        _id: '60f3b0b3f0b9a1b0b4f9b1a4',
        teamName: 'Crackers',
        totalPlayers: 15,
        player: [
            {
                playerInfo: {
                    _id: '60f3b0b3f0b9a1b0b4f9b1a5',
                    playerName: 'Player C',
                    rank: 3,
                    totalRuns: 2050,
                },
                playerName: 'Player C',
                score: 30,
            },
            {
                playerInfo: {
                    _id: '60f3b0b3f0b9a1b0b4f9b1a6',
                    playerName: 'Player D',
                    rank: 4,
                    totalRuns: 1000,
                },
                playerName: 'Player D',
                score: 40,
            },
        ],
    },
];

Does this answer your question? [JavaScript merging objects by id](https://stackoverflow.com/questions/19480008/javascript-merging-objects-by-id) — pilchard, Jun 03 '23 at 10:31
It depends on if you want to query the result directly from Mongo or aggregate them on the front end. Answers for both below. — pilchard, Jun 03 '23 at 10:43

score 3 · Accepted Answer · answered Jun 03 '23 at 10:42

$lookup - Join both Teams and Players collections with fields. And return the players array field.
$set - Set the player field.

2.1. $map - Iterate element in the player array and return a new array.

2.1.1. $mergeObjects - Merge the current iterated document with the document with the playerInfo field from the result 2.1.1.1.

2.1.1.1. $first - Get the first matching document from the result 2.1.1.1.1.

2.1.1.1.1. $filter - Filter the document in the players array that matches the _id.
$unset - Remove the fields: players field and player.playerId fields.

db.Teams.aggregate([
  {
    "$lookup": {
      "from": "Players",
      "localField": "player.playerId",
      "foreignField": "_id",
      "as": "players"
    }
  },
  {
    $set: {
      player: {
        $map: {
          input: "$player",
          as: "p",
          in: {
            $mergeObjects: [
              "$$p",
              {
                playerInfo: {
                  $first: {
                    $filter: {
                      input: "$players",
                      cond: {
                        $eq: [
                          "$$p.playerId",
                          "$$this._id"
                        ]
                      }
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: [
      "players",
      "player.playerId"
    ]
  }
])

Demo @ Mongo Playground

pilchard · Answer 2 · 2023-06-03T12:09:58.950

It is unclear whether you are looking to accomplish this in a MongoDB query or whether you want to aggregate the data in javascript. If the latter then it is really just a case of a nested map over the Player array within each team in the Teams array and adding the matching element from the Players array by id. Here creating a Map of players by id first for efficient access.

const Teams = [{ _id: '60f3b0b3f0b9a1b0b4f9b1a1', teamName: 'World XI A', totalPlayers: 15, player: [{ playerId: '60f3b0b3f0b9a1b0b4f9b1a2', playerName: 'Player A', score: 10, }, { playerId: '60f3b0b3f0b9a1b0b4f9b1a3', playerName: 'Player B', score: 20, },], }, { _id: '60f3b0b3f0b9a1b0b4f9b1a4', teamName: 'Crackers', totalPlayers: 15, player: [{ playerId: '60f3b0b3f0b9a1b0b4f9b1a5', playerName: 'Player C', score: 30, }, { playerId: '60f3b0b3f0b9a1b0b4f9b1a6', playerName: 'Player D', score: 40, },], },];
const Players = [{ _id: '60f3b0b3f0b9a1b0b4f9b1a2', playerName: 'Player A', rank: 2, totalRuns: 5000, }, { _id: '60f3b0b3f0b9a1b0b4f9b1a3', playerName: 'Player B', rank: 1, totalRuns: 3050, }, { _id: '60f3b0b3f0b9a1b0b4f9b1a5', playerName: 'Player C', rank: 3, totalRuns: 2050, }, { _id: '60f3b0b3f0b9a1b0b4f9b1a6', playerName: 'Player D', rank: 4, totalRuns: 1000, },];

const playerInfoMap = new Map(Players.map((p) => [p._id, p]));

const result = Teams.map(({ player, ...team }) => ({
  ...team,
  player: player.map(({ playerId, ...player }) => ({
    ...player,
    ...(playerInfoMap.has(playerId)
      ? { playerInfo: playerInfoMap.get(playerId) }
      : { playerId }),
  })),
}));

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

Thanks for your response, but I'm looking for the mongo query to do the same! — Rajath, Jun 03 '23 at 11:13

MongoDB - How to aggregate collection sub-array objects with its actual object from the other collection

2 Answers2