There is a mongoDb collection, looks like this:

[
  {
    "_id": {
      "$oid": "63110728d74738cdc48a7de0"
    },
    "listName": "list_name",
    "alloweUidList": [
      {
        "uid": "prQUKkIxljVqbHlCKah7T1Rh7l22",
        "role": "creator",
        "boolId": 1,
        "crDate": "2022-09-01 21:25",
        "modDate": null
      }
    ],
    "offerModelList": [
      {
        "offerListenerEntity": {
          "_id": "6311072ed74738cdc48a7de1",
          "uid": "prQUKkIxljVqbHlCKah7T1Rh7l22",
          "itemName": "sometehing",
          "crDate": "2022-09-01 21:25",
          "boolId": 1,
          "modDate": null,
          "imageColorIndex": 3,
          "shoppingListId": "63110728d74738cdc48a7de0",
          "checkFlag": 0,
          "itemCount": 1
        },
        "offers": [
          {
            "id": "62fa7983b7f32cc089864a3b",
            "itemId": 127382,
            "itemName": "item_1",
            "itemCleanName": "item_clean_name",
            "imageUrl": "item.png",
            "price": 10,
            "measure": "measure",
            "salesStart": "N.a",
            "source": "source",
            "runDate": "2022.08.15-14:11:15",
            "shopName": "shop_name",
            "isSales": 1,
            "insertType": "automate",
            "timeKey": "2022_08_15_18_51",
            "imageColorIndex": 0,
            "isSelectedFlag": 1,
            "selectedBy": "not_selected",
            "itemCount": 1
          },
          {
            "id": "62fa7983b7f32cc089864a3b",
            "itemId": 127382,
            "itemName": "item_2",
            "itemCleanName": "item_clean_name",
            "imageUrl": "image.png",
            "price": 20,
            "measure": "measure",
            "salesStart": "N.a",
            "source": "source",
            "runDate": "2022.08.15-14:11:15",
            "shopName": "shop_name",
            "isSales": 1,
            "insertType": "automate",
            "timeKey": "2022_08_15_18_51",
            "imageColorIndex": 0,
            "isSelectedFlag": 0,
            "selectedBy": "not_selected",
            "itemCount": 1
          }
        ]
      },
      {
        "offerListenerEntity": {
          "_id": "6311a5c0d74738cdc48a7de2",
          "uid": "prQUKkIxljVqbHlCKah7T1Rh7l22",
          "itemName": "anything",
          "crDate": "2022-09-02 08:42",
          "boolId": 1,
          "modDate": null,
          "imageColorIndex": 1,
          "shoppingListId": "63110728d74738cdc48a7de0",
          "checkFlag": 0,
          "itemCount": 2
        },
        "offers": []
      }
    ],
    "crDate": "2022-09-01 21:25",
    "modDate": "2022-09-01 21:25",
    "boolId": 1,
    "imageColorIndex": 1
  }
]

So it has an array, with a nested array.

I would like to filter out the entire item from the offerModelList array, if the offerModelList.offerListenerEntity.boolId == 0 It's working with this aggregate query:

[
  {
    "$match": {
      "alloweUidList": {
        "$elemMatch": {
          "uid": "prQUKkIxljVqbHlCKah7T1Rh7l22",
          "boolId": 1
        }
      },
      "boolId": 1,
      
    }
  },
  {
    "$addFields": {
      "offerModelList": {
        "$filter": {
          "input": "$offerModelList",
          "as": "i",
          "cond": {
            "$eq": [
              "$$i.offerListenerEntity.boolId",
              1
            ]
          }
        }
      }
    },
    
  }
]

The problem comes, when I try to filter out items from the offerModelList.offers array based on isSelectedFlag field.

I modified my query to this:

db.collection.aggregate([
  {
    "$match": {
      "alloweUidList": {
        "$elemMatch": {
          "uid": "prQUKkIxljVqbHlCKah7T1Rh7l22",
          "boolId": 1
        }
      },
      "boolId": 1,
      
    }
  },
  {
    "$addFields": {
      "offerModelList": {
        "$filter": {
          "input": "$offerModelList",
          "as": "i",
          "cond": {
            "$eq": [
              "$$i.offerListenerEntity.boolId",
              1
            ]
          }
        }
      }
    },
    
  },
  {
    "$addFields": {
      "offerModelList.offers": {
        "$filter": {
          "input": "$offerModelList.offers",
          "as": "x",
          "cond": {
            "$eq": [
              "$$x.isSelectedFlag",
              1
            ]
          }
        }
      }
    },
    
  }
])

The problem is, it alwas return empty offers array.

Here comes an example: https://mongoplayground.net/p/kksRpoNKr1k in this specific case the offers array should cointains only 1 item.

CodePudding user response：

Don't think that you are able to directly filter from offerModelList.offers.

Instead, for the last stage,

1. $set - Set offerModelList field.

   1.1. $map - Iterate element in offerModelList array and return a new array.

   1.1.1. $mergeObjects - Merge current iterated document with the document resulted from 1.1.1.1.

   1.1.1.1. Document with offers array. Via $filter to filter the document(s) with isSelectedFlag: 1.

db.collection.aggregate([
  {
    "$match": {
      "alloweUidList": {
        "$elemMatch": {
          "uid": "prQUKkIxljVqbHlCKah7T1Rh7l22",
          "boolId": 1
        }
      },
      "boolId": 1,
      
    }
  },
  {
    "$addFields": {
      "offerModelList": {
        "$filter": {
          "input": "$offerModelList",
          "as": "i",
          "cond": {
            "$eq": [
              "$$i.offerListenerEntity.boolId",
              1
            ]
          }
        }
      }
    },
    
  },
  {
    "$set": {
      "offerModelList": {
        $map: {
          input: "$offerModelList",
          as: "offerModel",
          in: {
            $mergeObjects: [
              "$$offerModel",
              {
                offers: {
                  $filter: {
                    input: "$$offerModel.offers",
                    as: "x",
                    cond: {
                      $eq: [
                        "$$x.isSelectedFlag",
                        1
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Demo @ Mongo Playground