Question 115369 in Mongodb Question posted in
The official documentation can be found here.

Mongodb aggregation conditionally match array

I have a document with an array field named items. This is an optional field and not always set within the document. So this could either be undefined, empty array, or contain an array of id numbers [1, 2, 3].

What I’m trying to do is return all documents if the items array does not exist or is empty, otherwise it should only return those that match a provided id. For example:

//This would not match (no documents) returned
id = 4
items = [1,2,3]


//This would match
id = 4
items = []
or
items = [1,2,3,4]
or
items is undefined (does not exist)

This is being done in a pipeline aggregation and tried using $expr with $cond but just not getting there…

Any help would be greatly appreciated. Thank you!

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    This is the solution I ended up going with:

          {
        $match: {
          $or: [
            { items: { $exists: false } },
            { items: { $size: 0 } },
            { items: { $in: [$id] } }
          ]
        }
      }

  2. 0 
    db.collection.aggregate([
  {
    $addFields: {
      check: {
        "$cond": {
          "if": {
            $or: [
              {
                "$and": [
                  {
                    "$lte": [
                      {
                        "$size": "$items"
                      },
                      1
                    ]
                  },
                  {
                    $eq: [
                      {
                        $ifNull: [
                          "$items",
                          0
                        ]
                      },
                      0
                    ]
                  }
                ]
              },
              {
                "$eq": [
                  {
                    "$in": [
                      4,
                      "$items"
                    ]
                  },
                  false
                ]
              }
            ]
          },
          "then": false,
          "else": true
        }
      }
    }
  },
  {
    "$addFields": {
      checkFalse: {
        "$cond": {
          "if": {
            "$and": [
              {
                "$gte": [
                  {
                    "$size": "$items"
                  },
                  1
                ]
              },
              
            ]
          },
          "then": true,
          "else": false
        }
      }
    }
  },
  {
    "$match": {
      "$expr": {
        "$cond": [
          {
            "$and": [
              {
                "$eq": [
                  "$checkFalse",
                  false
                ]
              },
              {
                "$eq": [
                  "$check",
                  false
                ]
              }
            ]
          },
          {
            "$or": [
              {
                "$and": [
                  {
                    "$eq": [
                      "$checkFalse",
                      true
                    ]
                  },
                  {
                    "$eq": [
                      "$check",
                      true
                    ]
                  }
                ],
                
              },
              {
                "$and": [
                  {
                    "$eq": [
                      "$checkFalse",
                      false
                    ]
                  },
                  {
                    "$eq": [
                      "$check",
                      false
                    ]
                  }
                ],
                
              },
              
            ]
          },
          {
            "$or": [
              {
                "$eq": [
                  "$checkFalse",
                  false
                ]
              },
              {
                "$eq": [
                  "$check",
                  true
                ]
              }
            ]
          },
          
        ]
      }
    }
  },
  {
    "$unset": [
      "check",
      "checkFalse"
    ]
  }
])

    MONGO PLAYGROUND

    Login or Signup to reply.
  3. 0

    You can use $ifNull to wrap the undefined case and empty array case into []. Then use $in to handle the case where the array contains 4. Use $or to chain up the 2 criteria.

    db.collection.find({
  $expr: {
    $or: [
      {
        $eq: [
          {
            $ifNull: [
              "$items",
              []
            ]
          },
          []
        ]
      },
      {
        $in: [
          4,
          "$items"
        ]
      }
    ]
  }
})

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search