Want to know the query which will produce the below result in best way without performing much aggregation stages.

Sample document

{
    "_id" : ObjectId("62d6699126d40c3cbca985f7"),
    "name" : "Cafe2",
    "customers" : [
        {
            "name" : "David",
            "foods" : [
                {
                    "name" : "cheese"
                },
                {
                    "name" : "beef"
                }
            ]
        },
        {
            "name" : "Bill",
            "foods" : [
                {
                    "name" : "fish"
                }
            ]
        },
        {
            "name" : "Ram",
            "foods" : [
                {
                    "name" : "cheese"
                },
                {
                    "name" : "beef"
                },
                {
                    "name" : "paneer"
                }
            ]
        }
    ]
}

Now my requirement is to get those embeded objects from 'foods' array which have name equals to "cheese".

Note :

1. Don't consider that embeded object in 'foods' array if it's name is not equal to "cheese".
2. Don't consider that embeded object in 'customers' array if it has an empty 'foods' array field.

Expected output

{
    "_id" : ObjectId("62d6699126d40c3cbca985f7"),
    "name" : "Cafe2",
    "customers" : [
        {
            "name" : "David",
            "foods" : [
                {
                    "name" : "cheese"
                }
            ]
        },
        {
            "name" : "Ram",
            "foods" : [
                {
                    "name" : "cheese"
                }
            ]
        }
    ]
 }

CodePudding user response：

Query

• map on customers
• and replace the foods array, with the filtered array
• filtered array contains only foods with name = cheese or empty array
• filter customers to remove those that have empty foods

*you can combine them in 1 stage all, but i think you cant do it shorter way. $reduce filter could work instead of map filter filter, but reduce and concat arrays is slow.

Playmongo

aggregate(
[{"$set": 
   {"customers": 
     {"$map": 
       {"input": "$customers",
        "in": 
         {"$mergeObjects": 
           ["$$this",
             {"foods": 
               {"$filter": 
                 {"input": "$$this.foods",
                  "cond": {"$eq": ["$$this.name", "cheese"]}}}}]}}}}},
 {"$set": 
   {"customers": 
     {"$filter": 
       {"input": "$customers", "cond": {"$ne": ["$$this.foods", []]}}}}}])