Matching values and merging 3 object arrays into 1

Via the aggregation pipeline on 2 collections (users and rankings) I have 3 object arrays: players, user_players and user_challengers. User_player and challengers are arrays of objects with 2 fields: name and ObjectId:

Players array matches the players to the correct challengers by ObjectId.

You can see from the screenshots that an additional problem may be that the first object in the challengers array (correctly) has name: “Hary”. Hary is a challenger, but, of course, he is Jon’s challenger and Jon is the second object in the challengers array (so there needs to be a matching of the correct id’s, not just e.g. a merging of 2 arrays).

I want a single array of corresponding names and a rank. In other words, I need to match the ObjectIds in Players to the corresponding ObjectIds and names in the other 2 arrays to give me json output that should look something like:


[
    {
      "matches": [
        {
          "player": "Hary", "challenger": "Jon", "rank": 1
        },
        {
          "player": "Jon", "challenger": "Hary", "rank": 2
        },
        {
          "player": "Birt", "challenger": "null", "rank": 3
        }
      ]
    }
  ]

There is likely a more efficient way to do this, but for now this was the only way I could find to connect the player and challenger ids to their names in the ‘users’ collection as I’m not an experienced db user.

How can I obtain the json output I’m looking for from the 3 arrays and/or is there a better, more efficient, way to achieve the same result? thanks…

Could you please publish sample documents in JSON so that we can cut-n-paste into our system? This way we could experiment. We can’t with images from Compass or other GUI. Well we could, but it would be too long to type and create these documents.

Sure. No problem:

    "players": [
      {
        "playerId": {
          "$oid": "62c2b79d966b72973fe52316"
        },
        "challengerId": {
          "$oid": "62c2b79d966b72973fe52317"
        },
        "rank": 1
      },
      {
        "playerId": {
          "$oid": "62c2b79d966b72973fe52317"
        },
        "challengerId": {
          "$oid": "62c2b79d966b72973fe52316"
        },
        "rank": 2
      },
      {
        "playerId": {
          "$oid": "62df7cec5fe09351fb906997"
        },
        "challengerId": {
          "$oid": "62df80935488fc6b309dcf32"
        },
        "rank": 3
      }
    ]
"user_players": [
      {
        "_id": {
          "$oid": "62c2b79d966b72973fe52316"
        },
        "name": "Hary"
      },
      {
        "_id": {
          "$oid": "62c2b79d966b72973fe52317"
        },
        "name": "Jon"
      },
      {
        "_id": {
          "$oid": "62df7cec5fe09351fb906997"
        },
        "name": "Birt"
      }
    ]
"user_challengers": [
      {
        "_id": {
          "$oid": "62c2b79d966b72973fe52316"
        },
        "name": "Hary"
      },
      {
        "_id": {
          "$oid": "62c2b79d966b72973fe52317"
        },
        "name": "Jon"
      }
    ]

‘Birt’ currently isn’t challenging or being challenged by anyone. Please let me know if there are any other input/output json formats I can provide that might assist. Thanks for having a look at it for me …

1 Like

Here also are the collections for reference:
Users:

[
  {
    "_id": {
    "$oid": "62c2b5f0966b72973fe52314"
  },
    "active": true,
    "name": "Mary",
    "age": 28,
    "gender": "Female",
    "description": {
      "level": "C grade",
      "comment": "Looking for good games with women only"
    },
    "ownerOf": [
      "Anglia Ladies Ladder",
      "Marys Buddies Ladder"
    ],
    "memberOf": [
      "Janes Buddies",
      "Anglia C graders"
    ]
  },
  {
    "_id": {
    "$oid": "62c2b79d966b72973fe52316"
  }
    "active": true,
    "name": "Hary",
    "age": 29,
    "gender": "Male",
    "description": {
      "level": "B grade",
      "comment": "Looking for good games with men only"
    },
    "ownerOf": [
      "Anglia Mens Ladder",
      "Harys Buddies Ladder"
    ],
    "memberOf": [
      "Bobs Buddies",
      "Anglia B graders"
    ]
  },
  {
    "_id": {
    "$oid": "62c2b79d966b72973fe52317"
  },
    "active": true,
    "name": "Jon",
    "age": 27,
    "gender": "Male",
    "description": {
      "level": "A grade",
      "comment": "Looking for good games with men only"
    },
    "ownerOf": [
      "Jons Buddies Ladder"
    ],
    "memberOf": [
      "Harys Buddies Ladder",
      "Anglia A graders"
    ]
  },
  {
    "_id": {
    "$oid": "62df7cec5fe09351fb906997"
  },
    "active": true,
    "name": "Birt",
    "age": 24,
    "gender": "Male",
    "description": {
      "level": "D grade",
      "comment": "lkjljlj"
    },
    "ownerOf": [],
    "memberOf": [
      "62c66dc612296752b7c82cde"
    ]
  }
]

Rankings:


[
  {
    "_id": "62c66dc612296752b7c82cde",
    "active": true,
    "name": "Jons Buddies Ladder",
    "owner": "Jon",
    "number": 2,
    "base address": {
      "street": "99 George Street",
      "city": "Super City"
    },
    "players": [
      {
        "playerId": "62c2b79d966b72973fe52316",
        "challengerId": "62c2b79d966b72973fe52317",
        "rank": 1
      },
      {
        "playerId": "62c2b79d966b72973fe52317",
        "challengerId": "62c2b79d966b72973fe52316",
        "rank": 2
      },
      {
        "playerId": "62df7cec5fe09351fb906997",
        "challengerId": "62df80935488fc6b309dcf32",
        "rank": 3
      }
    ],
    "owner_id": "62c2b79d966b72973fe52317",
    "lastModified": "2022-07-21T02:51:41.842Z"
  },
  {
    "_id": "62c66dc612296752b7c82cdd",
    "active": true,
    "name": "Harys Buddies Ladder",
    "owner": "Hary",
    "number": 2,
    "base address": {
      "street": "99 George Street",
      "city": "Super City"
    },
    "players": [
      {
        "playerId": "62c2b79d966b72973fe52317",
        "challengerId": "62c2b79d966b72973fe52316"
      },
      {
        "playerId": "62c2b79d966b72973fe52316",
        "challengerId": "62c2b79d966b72973fe52317"
      }
    ],
    "owner_id": "62c2b79d966b72973fe52316"
  },
  {
    "_id": "62df827ed09d7e792a2531e5",
    "active": true,
    "name": "Birt's Ladder",
    "owner": "62df7cec5fe09351fb906997",
    "base address": {
      "street": "99 George Street",
      "city": "Super City"
    },
    "players": [
      {
        "playerId": "62df7cec5fe09351fb906997",
        "challengerId": null,
        "rank": 1
      }
    ]
  }
]

Please note that I have noticed that VSCode renders all ‘View Documents’ with e.g.
rankings _id as:
"_id": "62c66dc612296752b7c82cde",
but individual documents as:

"_id": {
    "$oid": "62c66dc612296752b7c82cde"
  },

perhaps this behavior is well known to more experienced users(?). To clarify all references to _id in the collections on the mongodb server side use the ObjectId (not e.g. _id:) …

meant to say “(not e.g. simply _id: string_value)”

(I don’t know how to edit my responses so I have to use a reply instead …

Another way of expressing the problem in Playground. I can match player/challenger to name, but not at the same time and in a way that distinguishes between players and their challengers.
In this Playground attempt I distinguish between players and challengers, but I do not see how to match ids to names.

I managed to solve this:

[{$match: {
  _id: ObjectId('62c66dc612296752b7c82cde')
}}, {$unwind: {
  path: '$players'
}}, {$lookup: {
  from: 'users',
  localField: 'players.playerId',
  foreignField: '_id',
  as: 'players.player'
}}, {$lookup: {
  from: 'users',
  localField: 'players.challengerId',
  foreignField: '_id',
  as: 'players.challenger'
}}, {$unwind: {
  path: '$players.player',
  preserveNullAndEmptyArrays: true
}}, {$unwind: {
  path: '$players.challenger',
  preserveNullAndEmptyArrays: true
}}, {$project: {
  name: 1,
  players: {
    player: {
      name: 1
    },
    challenger: {
      name: 1
    },
    rank: 1
  }
}}, {$group: {
  _id: '$_id',
  ranking: {
    $push: '$players'
  }
}}, {$lookup: {
  from: 'rankings',
  localField: '_id',
  foreignField: '_id',
  as: 'rankingDetails'
}}, {$unwind: {
  path: '$rankingDetails',
  preserveNullAndEmptyArrays: true
}}, {$addFields: {
  'rankingDetails.ranking': '$ranking'
}}, {$replaceRoot: {
  newRoot: '$rankingDetails'
}}, {$sort: {
  'rankingDetails.ranking.rank': -1
}}, {$project: {
  active: 1, owner: 1, 'base address': 1, name: 1, ranking:1
}}]

with the assistance of this excellent article.
Thank you to the author, Krishna Bose and to anyone else who took a look at this.

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.