Query for documents where array size is greater than 1

Question

I have a MongoDB collection with documents in the following format:

{
  "_id" : ObjectId("4e8ae86d08101908e1000001"),
  "name" : ["Name"],
  "zipcode" : ["2223"]
}
{
  "_id" : ObjectId("4e8ae86d08101908e1000002"),
  "name" : ["Another ", "Name"],
  "zipcode" : ["2224"]
}

I can currently get documents that match a specific array size:

db.accommodations.find({ name : { $size : 2 }})

This correctly returns the documents with 2 elements in the name array. However, I can't do a $gt command to return all documents where the name field has an array size of greater than 2:

db.accommodations.find({ name : { $size: { $gt : 1 } }})

How can I select all documents with a name array of a size greater than one (preferably without having to modify the current data structure)?

Scranton

The newer versions of MongoDB have the $size operator; you should check out @tobia's answer – Lungworm

Actual solution: FooArray:{$gt:{$size:'length'}} --> lenght can be any number – Reft

@SergiNadal: I don't think this FooArray:{$gt:{$size:'length'}} is working! Well at least on nested object which is an array person:{ids:[123,456]} – Ens

Arrays should have a plural name so your array field name should be named names. – Pitarys

Answer 1

Update:

For mongodb versions 2.2+ more efficient way to do this described by @JohnnyHK in another answer.

---

1. Using $where

   db.accommodations.find( { $where: "this.name.length > 1" } );

But...

Javascript executes more slowly than the native operators listed on this page, but is very flexible. See the server-side processing page for more information.

2. Create extra field NamesArrayLength, update it with names array length and then use in queries:

   db.accommodations.find({"NamesArrayLength": {$gt: 1} });

It will be better solution, and will work much faster (you can create index on it).

Isaacs

Great, that was perfect thank you. Although I actually have some documents that don't have a name so had to modify the query to be: db.accommodations.find( { $where: "if (this.name && this.name.length > 1) {return this; } "} ); – Scranton

you are welcome, yes you can use any javascript in $where, it is very flexible. – Isaacs

@Scranton I would think it would be quicker to do something like { "name": {$exists:1}, $where: "this.name.lenght > 1"} ... minimizing the part in the slower javascript query. I assume that works and that the $exists would have higher precedence. – Ubiquitous

I had no idea you could embed javascript in the query, json can be cumbersome. Many of these queries are one time only entered by hand so optimization is not required. I'll use this trick often +1 – Forebear

for the second answer, how to update the array length when I need to remove multiple elements? – Compliancy

More efficient than db.accommodations.find( { $where: "this.name && this.name.length > 1" } )? – Fixer

After adding/removing elements from the Array, we need to update the count of "NamesArrayLength". Can this done in a single query? Or it requires 2 queries, one for updating the array and another for updating the count? – Sold

Answer 2

There's a more efficient way to do this in MongoDB 2.2+ now that you can use numeric array indexes (0 based) in query object keys.

// Find all docs that have at least two name array elements.
db.accommodations.find({'name.1': {$exists: true}})

You can support this query with an index that uses a partial filter expression (requires 3.2+):

// index for at least two name array elements
db.accommodations.createIndex(
    {'name.1': 1},
    {partialFilterExpression: {'name.1': {$exists: true}}}
);

Wilmot

Could somebody please explain how to index this. – Miserere

I'm really impressed with how effective this is and also how 'out of the box' you were thinking to find this solution. This works on 2.6, as well. – Evangelical

Works on 3.0 aswell. Thank you so much for finding this. – Epileptic

What if name has space, like "Name Field"? – Berenice

@Berenice No difference, really: {'Name Field.1': {$exists: true}}. – Wilmot

@Wilmot , how can check for less than with this? ie name filed has less than 2 array elements? – Ayurveda

Can't imagine how to use this when index depends on some value stored in document. – Parlour

again it checks a fixed value ( > 1). the question is : "How to query for documents where array size is greater than one". What if array lenghts are variable? – Concretize

It'd be helpful to mention in the answer that indexation is 0-based here. – Istanbul

This isn't a good answer, because x.1 can exist while x.0 is undefined. – Chatham

Can someone please explain why MongoDB still does not allow querying for a size range? It boggles my mind. – Piccolo

I would go for db.accommodations.find({'name.0': {$exists: true}}) instead as the the first index of an array in mongodb is 0. Is there a reason why you went for 1 ? – Live

@JubaFourali The question is regarding greater than 1 element. – Wilmot

Answer 3

I believe this is the fastest query that answers your question, because it doesn't use an interpreted $where clause -- it uses the $size array operator and the $exists element operator combined with $nor logical operator

{$nor: [
    {name: {$exists: false}},
    {name: {$size: 0}},
    {name: {$size: 1}}
]}

It means "all documents except those without a name (either non existant or empty array) or with just one name."

Test:

> db.test.save({})
> db.test.save({name: []})
> db.test.save({name: ['George']})
> db.test.save({name: ['George', 'Raymond']})
> db.test.save({name: ['George', 'Raymond', 'Richard']})
> db.test.save({name: ['George', 'Raymond', 'Richard', 'Martin']})
> db.test.find({$nor: [{name: {$exists: false}}, {name: {$size: 0}}, {name: {$size: 1}}]})
{ "_id" : ObjectId("511907e3fb13145a3d2e225b"), "name" : [ "George", "Raymond" ] }
{ "_id" : ObjectId("511907e3fb13145a3d2e225c"), "name" : [ "George", "Raymond", "Richard" ] }
{ "_id" : ObjectId("511907e3fb13145a3d2e225d"), "name" : [ "George", "Raymond", "Richard", "Martin" ] }
>

Aponte

@viren I don't know. This was certainly better than Javascript solutions, but for newer MongoDB you should probably use {'name.1': {$exists: true}} – Aponte

@Aponte my first use was $exists only but it actually use whole table scan so very slow. db.test.find({"name":"abc","d.5":{$exists:true},"d.6":{$exists:true}}) "nReturned" : 46525, "executionTimeMillis" : 167289, "totalKeysExamined" : 10990840, "totalDocsExamined" : 10990840, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "name" : 1, "d" : 1 }, "indexName" : "name_1_d_1", "direction" : "forward", "indexBounds" : { "name" : [ "[\"abc\", \"abc\"]" ], "d" : [ "[MinKey, MaxKey]" ] } } If you see it scanned whole table. – Nightwalker

Would be nice to update the answer to recommend other alternatives (like 'name.1': {$exists: true}}, and also because this is hardcoded for "1" and doesn't scale to an arbitrary or parametric minimum array length. – Goddamned

This may be fast but falls apart if you're looking for lists > N, where N isn't small. – Churchgoer

This doesn't work if you're looking for a nested array where the inside array has length of at least 2, but {'foo.bar.details.2': {$exists: true}} will find those. – Giddens

Answer 4

You can use aggregate, too:

db.accommodations.aggregate(
[
     {$project: {_id:1, name:1, zipcode:1, 
                 size_of_name: {$size: "$name"}
                }
     },
     {$match: {"size_of_name": {$gt: 1}}}
])

// you add "size_of_name" to transit document and use it to filter the size of the name

Jerrine

This solution is the most general, along with @JohnnyHK's since it can be used for any array size. – Whitby

if i want to use "size_of_name" inside projection then how can i do that ?? Actually i want to use $slice inside projection where its value is equal to $slice : [0, "size_of_name" - skip] ?? – Tace

Answer 5

You can use $expr ( 3.6 mongo version operator ) to use aggregation functions in regular query.

Compare query operators vs aggregation comparison operators.

db.accommodations.find({$expr:{$gt:[{$size:"$name"}, 1]}})

Shumway

How would you pass instead of $name an array that is a subdocument, for example in a "person" record, passport.stamps? I tried various quoting combinations but I get "The argument to $size must be an array, but was of type: string/missing". – Goddamned

@DanDascalescu It looks like stamps is not present in all documents. You can use ifNull to output empty array when the stamps is not present. Something like db.col.find({$expr:{$gt:[{$size:{$ifNull:["$passport.stamps", []]}}, 1]}}) – Shumway

Thanks, I was looking to size an array key and this works perfectly! db.getCollection("companies").find({ $expr: {$gt: [{$size:"$keywords"}, 0]} }) Also, you may change $gt to $eq to query those docs with no data to size: db.getCollection("companies").find({ $expr: {$eq: [{$size:"$keywords"}, 0]} }) – Nippers

Answer 6

Try to do something like this:

db.getCollection('collectionName').find({'ArrayName.1': {$exists: true}})

1 is number, if you want to fetch record greater than 50 then do ArrayName.50 Thanks.

Chophouse

The same answer was given three years earlier. – Goddamned

can we put some dynamic number like "ArrayName.<some_num>" inside the query? – Paleopsychology

Yes you can use any number. If you want to fetch record greater than N then pass n. – Chophouse

Answer 7

MongoDB 3.6 include $expr https://docs.mongodb.com/manual/reference/operator/query/expr/

You can use $expr in order to evaluate an expression inside a $match, or find.

{ $match: {
           $expr: {$gt: [{$size: "$yourArrayField"}, 0]}
         }
}

or find

collection.find({$expr: {$gte: [{$size: "$yourArrayField"}, 0]}});

Styx

While correct, this is a duplicate answer. See https://mcmap.net/q/53281/-query-for-documents-where-array-size-is-greater-than-1 by @user2683814 – Bradleybradly

Answer 8

None of the above worked for me. This one did so I'm sharing it:

db.collection.find( {arrayName : {$exists:true}, $where:'this.arrayName.length>1'} )

Phalanstery

javascript executes more slowly than the native operators provided by mongodb, but it's very flexible. see:https://mcmap.net/q/53281/-query-for-documents-where-array-size-is-greater-than-1, So the final solution is : https://mcmap.net/q/53281/-query-for-documents-where-array-size-is-greater-than-1 – Seavey

Answer 9

Although the above answers all work, What you originally tried to do was the correct way, however you just have the syntax backwards (switch "$size" and "$gt")..

Correct:

db.collection.find({items: {$gt: {$size: 1}}})

Hooked

I don't see why so many downvotes - this works perfectly for me! – Fodder

Works perfectly fine, v 4.2.5 – Miniver

always post version when posting solutions like that. not working on 4.2 – Correspond

As of 4.4, the "correct" is not working. Just because $gt parameter must be a number. Either $size parameter must be a number. – Sawyere

Works fine on "4.4.8" – Anchylose

works fine on 5.0.6 – Dawna

The people saying this doesn't work must have pasted the part that clearly says incorrect – Narcotize

This should definitely be the accepted answer. This answer is more generalizable than other "hacky" answers and is faster than any answers base on "where", and simpler than any answer based on aggregate. – Dispute

It does not work for me. I have 300K records, they all have either 1 or 2 entries. If I do {items: {$gt: {$size: 2}}} I get back all 300K and it should be empty list. (assumming items is the array) ver 5.0.15 – Avalanche

Mysteriously it only works for me with $gt, but not with $lt which is what I need .. – Laurilaurianne

For me, on Mongo 6, this format always returns all documents (even those that shouldn't match) if all documents have the array field, and always returns no documents if at least some of the documents do not have the array field. – Johannejohannes

Answer 10

db.accommodations.find({"name":{"$exists":true, "$ne":[], "$not":{"$size":1}}})

Aplacental

This doesn't scale well to other minimum sizes (say, 10). – Goddamned

same as first answer – Hemostat

Answer 11

I found this solution, to find items with an array field greater than certain length

db.allusers.aggregate([
    { $match: { username: { $exists: true } } },
    { $project: { count: { $size: "$locations.lat" } } },
    { $match: { count: { $gt: 20 } } },
]);

The first $match aggregate uses an argument that's true for all the documents. Without it, I would get an error exception

"errmsg" : "exception: The argument to $size must be an Array, but was of type: EOO"

Palermo

This is essentially the same answer as this one, provided 2 years earlier. – Goddamned

Answer 12

You can MongoDB aggregation to do the task:

db.collection.aggregate([
  {
    $addFields: {
      arrayLength: {$size: '$array'}
    },
  },
  {
    $match: {
      arrayLength: {$gt: 1}
    },
  },
])

Phototype

Answer 13

this will work for you

db.collection.find({
  $expr: {
    $gt: [{ $size: "$arrayField" }, 1]
  }
})

Copal

Answer 14

This will work in Compass also. This is the fastest of all i have tried without indexing.

 {$expr: {
            $gt: [
              {
                $size: { "$ifNull": [ "$name", [] ] }
              },
              1
            ]
          }}

Stupefy

Answer 15

you can use $expr to cover this

// $expr: Allows the use of aggregation expressions within the query language.
// syntax: {$expr: {<expression>}}

db.getCollection("person_service").find(
    { 
        "$expr" : { 
            // services is Array, find services length gt 3
            "$gt" : [
                { 
                    "$size" : "$services"
                }, 
                3.0
            ]
        }
    }
)

Femineity

Answer 16

This worked for me:

{ $where: function() { return this.name && this.name.length > 2;}}

The trick was the check for if the value existed first before checking for the length.

I kept getting an error:

Executor error during find command :: caused by :: TypeError: this.name is undefined : @:1:15

That's because not all the documents had a name field (or whatever field you call it, replace name with the array you are interested in, e.g scores).

Unfortunately the error was very vague and I tried a whole bunch of other things before realising this was the issue.

Note: I'm using MongoDB (Atlas) v6.0.10

Suki

Ohh, I just realised mine is basically like https://mcmap.net/q/53281/-query-for-documents-where-array-size-is-greater-than-1 but with a single $where, but the answer by @Phalanstery is probably more performant if you have an index and it's a concern for you. But this one is probably easier to remember. Depends on your use case. – Suki