Retrieving related elements from MongoDB-CodePudding

I have the following data in MongoDB. Based alone on an id that I have available how can I retrieve all other entries where the player matches the player for my current id.

For example : find who the player for id 12 is, search all other entries that match that player name and return a list of all of them.

[
{_id: '62ecdf342f1193134043964c', id: '12', player: 'David Beckham', team: 'Manchester United'},
{_id: '62ecdf342f1193134043965c', id: '17', player: 'Cristiano Rolando', team: 'Manchester United'},
{_id: '62ecdf342f1193134043966c', id: '22', player: 'Cristiano Rolando', team: 'Juventus'},
{_id: '62ecdf342f1193134043967c', id: '42', player: 'David Beckham', team: 'Real Madrid'},
]

This is the code that I'm using to retrieve the one single entry that matches a specific id and then I'd also like to get the related entries.

export async function getStaticProps({ params }) {
  const { db } = await connectToDatabase();
  const jerseyA = await db
    .collection("Jerseys")
    .find({ id: params.jersey })
    .sort()
    .toArray();
  const jersey = JSON.parse(JSON.stringify(jerseyA))[0];
  return { props: { jersey } };
}

CodePudding user response：

Now that you know the name, do another fetch like .find({player: jersey.player})

CodePudding user response：

I'm not sure of the output format you want, but here's one way to return all documents that match the "player" name of the given "id".

db.Jerseys.aggregate([
  {
    "$match": {
      // your id goes here
      "id": "17"
    }
  },
  {
    "$lookup": {
      "from": "Jerseys",
      "localField": "player",
      "foreignField": "player",
      "as": "docs"
    }
  },
  {"$unwind": "$docs"},
  {"$replaceWith": "$docs"}
])

Example output:

[
  {
    "_id": "62ecdf342f1193134043965c",
    "id": "17",
    "player": "Cristiano Rolando",
    "team": "Manchester United"
  },
  {
    "_id": "62ecdf342f1193134043966c",
    "id": "22",
    "player": "Cristiano Rolando",
    "team": "Juventus"
  }
]

Try it on mongoplayground.net.

CodePudding user response：

Just an addition to @rickhg12hs solution, to ignore the first record. You can use the following query to ignore the first record (where the id also matched) and the others.

db.Jerseys.aggregate([
  {
    "$match": {
      "id": "12"
    }
  },
  {
    "$lookup": {
      "from": "Jerseys",
      "localField": "player",
      "foreignField": "player",
      "as": "docs"
    }
  },
  {
    "$unwind": "$docs"
  },
  {
    "$replaceWith": "$docs"
  },
  {
    "$match": {
      "id": {
        "$not": {
          "$eq": "12"
        }
      }
    }
  }
])

A possible javascript translation of it, should be,

export async function getStaticProps({ params }) {
  const { db } = await connectToDatabase();

  const { jersey: id } = params;

  const jerseyA = await db
    .collection("Jerseys")
    .aggregate([
  {
    "$match": {
      id
    }
  },
  {
    "$lookup": {
      "from": "Jerseys",
      "localField": "player",
      "foreignField": "player",
      "as": "docs"
    }
  },
  {
    "$unwind": "$docs"
  },
  {
    "$replaceWith": "$docs"
  },
  {
    "$match": {
      "id": {
        "$not": {
          "$eq": id
        }
      }
    }
  }
]).toArray();
  const jersey = JSON.parse(JSON.stringify(jerseyA))[0];
  return { props: { jersey } };
}