MongoDB: filter by array size and total with aggregate

Question

I'm using aggregate query. I need to return a document if:

"total" doesn't exist
array size is lower then "total"

So hide all document that have ALL array equal to "total".

Notes:

"container" can have different "subContainer"
"subContainer" is a dynamic key name
"total" is optional
"arr" size <= "total"

Documents in collection:

[
  {  // "container.subContainer2.arr" is lower then "total"
    "_id": 1,
    "title": "title1",
    "container": {
      "subContainer1": {
        "arr": [1, 2, 3]
      },
      "subContainer2": {
        "arr": [1]
      }
    },
    "total": 3
  },
  {  // "total" unknow
    "_id": 2,
    "title": "title2",
    "container": {
      "subContainer3": {
        "arr": [1, 2, 3, 4]
      }
    }
  },
  {  // both array size are equal to "total"
    "_id": 3,
    "title": "title3",
    "container": {
      "subContainer4": {
        "arr": [1, 2, 3, 4, 5]
      },
      "subContainer5": {
        "arr": [1, 2, 3, 4, 5]
      }
    },
    "total": 5
  }
]

Output:

[
  {
    "title": "title1"
  },
  {
    "title": "title2"
  }
]

What array size should be lower then "total"? Any of the subContainer or all of the subContainer sizes? — NeNaD
– NeNaD, Commented Sep 13, 2021 at 20:27
@NenadMilosavljevic The size is related only to "arr" array. — rtritto
– rtritto, Commented Sep 13, 2021 at 20:38
But each subContainer has one arr array. Should all of them be lower that total or at least one? — NeNaD
– NeNaD, Commented Sep 13, 2021 at 20:40
Yes, each subContainer has at least one arr array. All of them can be lower or equal total. — rtritto
– rtritto, Commented Sep 13, 2021 at 20:45

mohammad Naimi · Accepted Answer · 2021-09-13 21:34:18Z

1

first we add some extra field with $addFields and with map create array that contains arr and sum size of arr in each doc and put as newField create status Field as boolean if "arr" size <= "total" and match all doc with status : true and project title

db.collection.aggregate([
  {
    "$addFields": {
      "newField": {
        "$objectToArray": "$container"
      }
    }
  },
  {
    "$addFields": {
      "newField1": {
        "$map": {
          "input": "$newField",
          "as": "z",
          "in": {
            $size: "$$z.v.arr"
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "newField": {
        "$sum": "$newField1"
      }
    }
  },
  {
    "$addFields": {
      "status": {
        $gte: [
          "$total",
          "$newField"
        ]
      }
    }
  },
  {
    $match: {
      status: true
    }
  },
  {
    $project: {
      title: 1,
      _id: 0
    }
  }
])

https://mongoplayground.net/p/PhkeJUCkE6d

answered Sep 13, 2021 at 21:34

mohammad Naimi

2,3591 gold badge7 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

rtritto Over a year ago

Thanks for answer. I changed $sum with $min and $gte with $gt because I want return a document only if arr array size is lower then total. It doesn't return any document when total is missing.

mohammad Naimi Over a year ago

Could you fix problem?

mohammad Naimi Over a year ago

I think you need sum of arr size and should be lt of total

rtritto Over a year ago

total is like the limit to size of each arr array.

mohammad Naimi Over a year ago

ok , if my answer help you to love problem , appreciate to make up vote :)

ouflak · Accepted Answer · 2021-09-14 02:04:25Z

I solved this using $not to check if total exists (falsy) and $cond to check the result.

[
  {
    "$addFields": {
      "newField": {
        "$objectToArray": "$container"
      }
    }
  },
  {
    "$addFields": {
      "newField1": {
        "$map": {
          "input": "$newField",
          "as": "z",
          "in": {
            "$size": "$$z.v.arr"
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "newField": {
        "$min": "$newField1"
      }
    }
  },
  {
    "$addFields": {
      "status": {
        "$cond": [
          {
            "$not": [
              "$total"
            ]
          },
          true,
          {
            "$gt": [
              "$total",
              "$newField"
            ]
          }
        ]
      }
    }
  },
  {
    "$match": {
      "status": true
    }
  },
  {
    "$project": {
      "title": 1,
      "_id": 0
    }
  }
]

https://mongoplayground.net/p/qrSiAX88UFZ

Collectives™ on Stack Overflow

MongoDB: filter by array size and total with aggregate

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related