Can't find right query for MongoDB using mongo shell

Question

I have been trying to get a list of the newest(According to its EstablishedDate) Pharmacy that has each medicine.

Take this output as a result of the following array of documents as a sample:

Output:
Medicine : MedA , Pharmacy : a
Medicine : MedB , Pharmacy : b
Medicine : MedC , Pharmacy : b
Medicine : MedD , Pharmacy : a

[
  {
    "Pharmacy": "a",
    "EstablishedDate": ISODate("2006-10-12"),
    "Medicine": [
      {
        "MedName": "MedA",
        "Quantity": 55
      },
      {
        "MedName": "MedB",
        "Quantity": 34
      },
      {
        "MedName": "MedD",
        "Quantity": 25
      }
    ]
  },
  {
    "Pharmacy": "b",
    "EstablishedDate": ISODate("2015-2-2"),
    "Medicine": [
      {
        "MedName": "MedB",
        "Quantity": 60
      },
      {
        "MedName": "MedC",
        "Quantity": 34
      }
    ]
  }
]

How can this be solved?

Answer 1

1.Answer for all medicines with respective pharmacies

db.collection.aggregate([
  {
    $unwind: "$Medicine"
  },
  {
    $project: {
      "_id": 0,
      "Medicine": "$Medicine.MedName",
      "Pharmacy": "$Pharmacy"
    }
  }
])

Output

[
  {
    "Medicine": "MedA",
    "Pharmacy": "a"
  },
  {
    "Medicine": "MedB",
    "Pharmacy": "a"
  },
  {
    "Medicine": "MedD",
    "Pharmacy": "a"
  },
  {
    "Medicine": "MedB",
    "Pharmacy": "b"
  },
  {
    "Medicine": "MedC",
    "Pharmacy": "b"
  }
]

Refer playground and run the script

2.All Medicine with latest Pharmacies

db.collection.aggregate([
  {
    $unwind: "$Medicine"
  },
  {
    $sort: {
      EstablishedDate: -1
    }
  },
  {
    $group: {
      _id: "$Medicine.MedName",
      Pharmacy: {
        $first: "$Pharmacy"
      }
    }
  },
  {
    $project: {
      _id: 0,
      "Medicine": "$_id",
      "Pharmacy": "$Pharmacy"
    }
  }
])

Output

[
  {
    "Medicine": "MedA",
    "Pharmacy": "a"
  },
  {
    "Medicine": "MedC",
    "Pharmacy": "b"
  },
  {
    "Medicine": "MedD",
    "Pharmacy": "a"
  },
  {
    "Medicine": "MedB",
    "Pharmacy": "b"
  }
]

Refer Respective playground and run the script