I have a document that's look like this if it hasn't got any items in the itemList field:

    {
       "_id":{
          "$oid":"62e12a0b73a8c3469e635d93"
       },
       "listName":"name of list",
       "alloweUidList":[
          {
             "uid":"prQUKkIxljVqbHlCKah7T1Rh7l22",
             "role":"creator",
          }
       ],
       "itemList":[
       ],
       "crDate":"2022-07-27 14:05",
       "modDate":"2022-07-27 14:05",
       "boolId":1
    }

and looks like this if i have some elements in the itemList field:

    {
       "_id":{
          "$oid":"62e12a0b73a8c3469e635d93"
       },
       "listName":"Kuli Gábor listája nr: 1",
       "alloweUidList":[
          {
             "uid":"prQUKkIxljVqbHlCKah7T1Rh7l22",
             "role":"creator",
          }
       ],
       "itemList":[
          {
             "itemDetail":{
                "itemName":"item 1 name",
                "price":459,
             },
             "crDate":"2022-07-27 14:13",
             "checkFlag":0,
             "boolId":1,
             "volume":1,
             "modDate":null
          },
          {
             "itemDetail":{
                "itemName":"item 2 name",
                "price":169,
             },
             "crDate":"2022-07-27 14:15",
             "checkFlag":0,
             "boolId":0,
             "volume":1,
             "modDate":"2022-07-27 14:16"
          }
       ],
       "crDate":"2022-07-27 14:05",
       "modDate":"2022-07-27 14:05",
       "boolId":1
    }

I would like to find documents that has at least one element with boolId: 1 in the itemList array or the itemList array is empty. This query works only if i have item in my array with boolId: 1 but not works if the array is empty:

    db.shoppingList.find(
      {
        "itemList.boolId": 1,
        "alloweUidList.uid": "prQUKkIxljVqbHlCKah7T1Rh7l22",
        "alloweUidList.boolId": 1,
        "boolId": 1
      },
      {
        listName: 1,
        alloweUidList: 1,
        crDate: 1,
        modDate: 1,
        boolId: 1,
        itemList: {
          $elemMatch: {
            $or: [
              {boolId: 1},
              {itemList:{$exists:true,$eq:[]}}
            ]
          },
        },
      }
    )

Also tried: {$size : 0} thats not works either.

CodePudding user response：

You can do an $or in $expr to cater to the 2 criteria.

db.collection.find({
  $expr: {
    $or: [
      // itemList is empty array
      {
        $eq: [
          "$itemList",
          []
        ]
      },
      // itemList has more than 1 boolId:1 elem
      {
        $gt: [
          {
            $size: {
              "$filter": {
                "input": "$itemList",
                "as": "i",
                "cond": {
                  $eq: [
                    "$$i.boolId",
                    1
                  ]
                }
              }
            }
          },
          0
        ]
      }
    ]
  }
})

Here is the Mongo Playground for your reference.

CodePudding user response：

1. Shouldn't do filtering for at least one element with boolId: 1 in the itemList array or the itemList array is empty in the projection.

2. This "alloweUidList.boolId": 1 search criteria will lead to no document is returned as the attached documents do not contain alloweUidList.boolId property.

3. Use $expr operator to use the aggregation operators.

db.shoppingList.find({
  $expr: {
    $and: [
      {
        $or: [
          {
            $eq: [
              {
                $ifNull: [
                  "$itemList",
                  []
                ]
              },
              []
            ]
          },
          {
            $in: [
              1,
              "$itemList.boolId"
            ]
          }
        ]
      },
      {
        $in: [
          "prQUKkIxljVqbHlCKah7T1Rh7l22",
          "$alloweUidList.uid"
        ]
      },
      {
        $in: [
          1,
          "$alloweUidList.boolId"
        ]
      },
      {
        $eq: [
          "$boolId",
          1
        ]
      }
    ]
  }
},
{
  listName: 1,
  alloweUidList: 1,
  crDate: 1,
  modDate: 1,
  boolId: 1,
  itemList: 1
})

Sample Mongo Playground