CODEWITHSUNDEEP
mongodbfindsizeaggregation
Will H
Will H

Reputation: 33

MongoDB query to return all based on an array size after filtering array?

I am trying to create a template for a Mongo find query that will return all that match the given size of an array property. For example, this is the query to find all documents that contain the array of size 5:

{ arrayProperty: { $size: 5} }

The problem: some of these arrays have a value of "nil" and don't want these values to be counted when querying for the size. For example, I would like a document containing an array structured like this to be returned when querying for a size of 5:

[0,1,2,3,"nil"]

I am already doing something like this in the aggregation framework:

  [
      {
         $project: {
            item: 1,
            numberOfItems: { $size: {
              $filter: {
                input: "$arrayProperty",
                cond: { $ne: [ "$$this", "nil" ] }
              }
            }
            }
         }
      }
   ]

This is counting the length/size of each of the arrays and pushing that number to an array called numberOfItems. I believe I need to pass filtered results to the $size operator. I cannot seem to figure out how to leverage this in the find query syntax though and I am desperate for some help.

The idea I have in mind for the flow I described is something like this, though this is not valid:

    {$expr:{
      $filter: {
        input: "$arrayProperty",
        cond: { $ne: [ "$$this", "nil" ] }
      }: { $size: 5 }
    }}

Can anyone assist me? How do I properly pass a filtered result to the $size operator? I cannot find any other questions/answers where folks have tried to do something like this.

The other situation is when the "nil" is nested as a value in a JSON object in an array, which I know will require a different solution. Example:

[
  {
    "arrayProperty": [
      {
        "model": "sedan",
        "turbo": "nil"
      },
      {
        "model": "sedan",
        "turbo": "60cc"
      }
    ]
  },
  {
    "arrayProperty": [
      {
        "model": "coupe",
        "turbo": "50cc"
      },
      {
        "model": "coupe",
        "turbo": "60cc"
      }
    ]
  }
]

Upvotes: 2

Views: 893

Answers (1)

turivishal
turivishal

Reputation: 36094

You can just try $ne condition together $size condition,

db.collection.find({
  arrayProperty: {
    $size: 5,
    $ne: "nil"
  }
})

Playground

The other situation is when the "nil" is nested as a value in a JSON object in an array, which I know will require a different solution.

db.collection.find({
  "arrayProperty.turbo": {
    $ne: "nil"
  },
  arrayProperty: {
    $size: 2
  }
})

Playground

Upvotes: 1

Related Questions