Not able to find right query in MongoDB

Question

I have been trying to come up with a query for these (simplified) documents below. My database consists of several data similar as these.

Since there is no nested querying in Mongo shell, is there another possible way to get what I want?

I am trying to get a list of Medicines that are owned by more than 30% of the pharmacies in my DB (regardless of quantity).

[
  {
    "Pharmacy": "a",
    "Medicine": [
      {
        "MedName": "MedA",
        "Quantity": 55
      },
      {
        "MedName": "MedB",
        "Quantity": 34
      },
      {
        "MedName": "MedD",
        "Quantity": 25
      }
    ]
  },
  {
    "Pharmacy": "b",
    "Medicine": [
      {
        "MedName": "MedB",
        "Quantity": 60
      },
      {
        "MedName": "MedC",
        "Quantity" : 34
      }

    ]
  }
]

How can I do this (if possible)?

Answer 1

Please check the answer here: https://mongoplayground.net/p/KVZ4Ee9Qhu-

var PharmaCount = db.collection.count();


db.collection.aggregate([
  {
    "$unwind": "$Medicine"
  },
  {
    "$project": {
      "medName": "$Medicine.MedName",
      "Pharmacy": "$Pharmacy"
    }
  },
  {
    "$group": {
      "_id": {
        "medName": "$medName"
      },
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$project": {
      "count": 1,
      "percentage": {
        "$concat": [
          {
            "$substr": [
              {
                "$multiply": [
                  {
                    "$divide": [
                      "$count",
                      {
                        "$literal": 2 // Your total number of pharmacies i.e PharmaCount
                      }
                    ]
                  },
                  100
                ]
              },
              0,
              3
            ]
          },
          "",
          "%"
        ]
      }
    }
  }
])

You should get results like:

[
  {
    "_id": {
      "medName": "MedC"
    },
    "count": 1,
    "percentage": "50%"
  },
  {
    "_id": {
      "medName": "MedD"
    },
    "count": 1,
    "percentage": "50%"
  },
  {
    "_id": {
      "medName": "MedB"
    },
    "count": 2,
    "percentage": "100%"
  },
  {
    "_id": {
      "medName": "MedA"
    },
    "count": 1,
    "percentage": "50%"
  }
]

Hope this helps.

Answer 2

You can not do this in a single query, but here is a way :

size = (db['01'].distinct("Pharmacy")).length;
minPN = Math.ceil(size*0.3);

db['01'].aggregate(
    // Pipeline
    [
        // Stage 1
        {
            $unwind: {
                path : "$Medicine", 
            }
        },
        // Stage 2
        {
            $group: {
            _id:"$Medicine.MedName",
            pharmacies:{$addToSet:"$Pharmacy"}
            }
        },
        // Stage 3
        {
            $project: {
              pharmacies:1,
            pharmacies_count:{$size:"$pharmacies"}
            }
        },
        {
          $match:{pharmacies_count:{$gte:minPN}}
        }   
    ]
);