mongodb query filter documents by array value or size

solarenqu
July 27, 2022
52 views
0 votes
2 Answers

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",
             "boolId": 1,
          }
       ],
       "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",
             "boolId": 1,
          }
       ],
       "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.

Update, I Forget. If I have elements with boolId:1 and also with boolId:0 in itemList i only want to return values that has boolId:1 So if the document looks like this:

{
   "_id":{
      "$oid":"62e1855473a8c3469e635d94"
   },
   "listName":"name of list",
   "alloweUidList":[
      {
         "uid":"prQUKkIxljVqbHlCKah7T1Rh7l22",
         "role":"creator",
         "boolId":1,
      }
   ],
   "itemList":[
      {
         "itemDetail":{
            "itemName":"item name 1",
            "price":459,
         },
         "crDate":"2022-07-27 20:35",
         "checkFlag":0,
         "boolId":1,
         "volume":1,
         "modDate":null
      },
      {
         "itemDetail":{
            "itemName":"item name 2",
            "price":549,
         },
         "crDate":"2022-07-27 20:35",
         "checkFlag":0,
         "boolId":0,
         "volume":1,
         "modDate":"2022-07-27 20:35"
      }
   ],
   "crDate":"2022-07-27 20:34",
   "modDate":"2022-07-27 20:34",
   "boolId":1
}

I would like to get this:

{
   "_id":{
      "$oid":"62e1855473a8c3469e635d94"
   },
   "listName":"name of list",
   "alloweUidList":[
      {
         "uid":"prQUKkIxljVqbHlCKah7T1Rh7l22",
         "role":"creator",
         "boolId":1,
      }
   ],
   "itemList":[
      {
         "itemDetail":{
            "itemName":"item name 1",
            "price":459,
         },
         "crDate":"2022-07-27 20:35",
         "checkFlag":0,
         "boolId":1,
         "volume":1,
         "modDate":null
      }
   ],
   "crDate":"2022-07-27 20:34",
   "modDate":"2022-07-27 20:34",
   "boolId":1
}

And if the itemList array is empty i would return the document with empty itemList array

clarification: if the document has only boolId:0 entries, the query should return the document with empty itemList array.

Tags: mongodb mongodb-query

Answers

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

db.collection.aggregate([
  {
    $match: {
      "alloweUidList.uid": "prQUKkIxljVqbHlCKah7T1Rh7l22",
      "alloweUidList.boolId": 1,
      "boolId": 1
    }
  },
  {
    $match: {
      $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
            ]
          }
        ]
      }
    }
  },
  {
    "$addFields": {
      "itemList": {
        "$filter": {
          "input": "$itemList",
          "as": "i",
          "cond": {
            $eq: [
              "$$i.boolId",
              1
            ]
          }
        }
      }
    }
  }
])

Here is the Mongo Playground for your reference.

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.
This "alloweUidList.boolId": 1 search criteria will lead to no document is returned as the attached documents do not contain alloweUidList.boolId property.
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

Please signup or login to give your own answer.

Click here to cancel reply.