I'm starting MongoDB and I have difficulties to understand how to filter some nested documents in an array. The objective if to keep only relevant data from a nested array.

Here is the data:

{
  "_id": {
    "$oid": "47bb"
  },
  "email": "[email protected]",
  "orders": [
    {
      "orderNumber": "",
      "products": [
        {
          "brand": "Brand 1",
          "processing": {
            "status": "pending"
          }
        }
      ],
      "updated": {
        "$date": {
          "$numberLong": "1673031718883"
        }
      }
    },
    {
      "orderNumber": "",
      "products": [
        {
          "brand": "Brand 2",
          "processing": {
            "status": "pending"
          }
        }
      ],
      "updated": {
        "$date": {
          "$numberLong": "1673031718883"
        }
      }
    },
    {
      "orderNumber": "",
      "products": [
        {
          "brand": "Brand 3",
          "processing": {
            "status": "processing"
          }
        }
      ],
      "updated": {
        "$date": {
          "$numberLong": "1673031718883"
        }
      }
    }
  ],
  "privilege": {
    "admin": false
  },
  "isVerified": {
    "email": "true"
  }
}

I want exactly the same data structure with 'orders.products.processing.status': 'pending'

The response from the database should be:

{
  "_id": {
    "$oid": "62b333644f70f94aa47bb4da"
  },
  "email": "[email protected]",
  "orders": [
    {
      "orderNumber": "",
      "products": [
        {
          "brand": "Brand 1",
          "processing": {
            "status": "pending"
          }
        }
      ],
      "updated": {
        "$date": {
          "$numberLong": "1673031718883"
        }
      }
    },
    {
      "orderNumber": "",
      "products": [
        {
          "brand": "Brand 2",
          "processing": {
            "status": "pending"
          }
        }
      ],
      "updated": {
        "$date": {
          "$numberLong": "1673031718883"
        }
      }
    }
  ],
  "privilege": {
    "admin": false
  },
  "isVerified": {
    "email": "true"
  }
}

My closest attempt to a correct query is:

db.collection.aggregate([{
        $unwind: '$orders'
    },
    {
        $unwind: '$orders.products'
    },
    {
        $match: {
            "orders.products.processing.status": 'pending'
        }
    }, {
        $group: {

            _id: {

                "_id": "$_id",
                "email": "$email",
                "orders": {
                    "orderNumber": "$orders.orderNumber",
                    "products": {
                        "processing": "$orders.products.processing.updated",
                        "brand": "$orders.products.brand",
                    }

                },

            },

            products: {
                $push: "$orders.products"
            },

        }
    }, {
        $project: {
            products: 0,
        }
    }
])

The problem is that the result lose the grouping by _id and loosing the initial json structure. Thanks.

CodePudding user response：

You can try this query:

• First $match to get only documents which have orders.products.processing.status as pending (later will be filtered and maybe is redundant but using $map and $filter I prefer to avoid to do over all collection).
• Then $project to get only desired values. Here the trick is to return in orders only the orders you want.

To accomplish that you can use $map to iterate over the array and return a new one with values that matches the filter (like a JS map).

And then the $filter. Here are filtered values whose status is not pending and returned to the map that output in the field orders.

And this without $unwind and $group :)

db.collection.aggregate([
  {
    "$match": {
      "orders.products.processing.status": "pending"
    }
  },
  {
    "$project": {
      "email": 1,
      "isVerified": 1,
      "privilege": 1,
      "orders": {
        "$map": {
          "input": "$orders",
          "as": "order",
          "in": {
            "orderNumber": "$$order.orderNumber",
            "products": {
              "$filter": {
                "input": "$$order.products",
                "cond": {
                  "$eq": [ "$$this.processing.status", "pending" ]
                }
              }
            }
          }
        }
      }
    }
  }
])

Example here

And also a bonus... check this example here I've added a one more $filter. It's so messy but if you can understand is quite easy. The $map from the first example return an array, so now I'm using a $filter ussing that array and filtering (not show) the objects where products is empty (i.e. where products.processing.status is not pending).