Question 181397 in Mongodb Question posted in
The official documentation can be found here.

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

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,
            },
        ],
    },
];
Tags:

2

Answers


  1. 0

    1. $lookup – Join both Teams and Players collections with fields. And return the players array field.

    2. $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.

    3. $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

    Login or Signup to reply.
  2. 0

    This 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; }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search