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)?

The newer versions of MongoDB have the $size operator; you should check out @tobia's answer — AlbertEngelB, Mar 27 '14 at 17:55
Actual solution: FooArray:{$gt:{$size:'length'}} --> lenght can be any number — Sergi Nadal, Jul 17 '18 at 09:32
@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]}` — whoami - fakeFaceTrueSoul, Apr 29 '21 at 23:14
Arrays should have a plural name so your array field `name` should be named `names`. — Paul, Aug 21 '21 at 16:25

JohnnyHK · Answer 1 · 2021-05-08T04:43:54.310

1750

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}}}
);

edited May 08 '21 at 04:43

answered Mar 05 '13 at 13:05

JohnnyHK

305,182
66
621
471

21

Could somebody please explain how to index this. – Ben Oct 18 '13 at 06:28
55

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. – earthmeLon Jul 04 '14 at 19:13
2

Works on 3.0 aswell. Thank you so much for finding this. – pikanezi Aug 12 '15 at 16:18
What if `name` has space, like `"Name Field"`? – Dims Feb 15 '16 at 10:55
1

@Dims No difference, really: `{'Name Field.1': {$exists: true}}`. – JohnnyHK Feb 15 '16 at 12:58
@JohnnyHK , how can check for less than with this? ie name filed has less than 2 array elements? – Rajasankar Feb 16 '16 at 18:50
Can't imagine how to use this when index depends on some value stored in document. – Arsenii Fomin Apr 22 '16 at 18:46
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? – MiniScalope Apr 27 '16 at 23:39
4

It'd be helpful to mention in the answer that indexation is 0-based here. – Dennis Golomazov Jun 15 '17 at 21:26
1

This isn't a good answer, because `x.1` can exist while `x.0` is undefined. – Hobbyist Dec 29 '17 at 11:47
1

Can someone please explain why MongoDB still does not allow querying for a size range? It boggles my mind. – Steven Spungin Feb 06 '22 at 15:30
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 ? – Juba Fourali May 22 '23 at 19:46
@JubaFourali The question is regarding _greater_ than 1 element. – JohnnyHK May 26 '23 at 01:38

score 613 · Accepted Answer · edited Apr 13 '22 at 15:05

613

Update:

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

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.

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).

edited Apr 13 '22 at 15:05

Ahmed Ashour

5,179
10
35
56

answered Oct 18 '11 at 17:27

Andrew Orsich

52,935
16
139
134

6

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; } "} ); – emson Oct 18 '11 at 17:51
you are welcome, yes you can use any javascript in `$where`, it is very flexible. – Andrew Orsich Oct 18 '11 at 17:58
8

@emson 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. – nairbv Dec 13 '12 at 19:40
1

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 – pferrel Mar 20 '14 at 15:37
for the second answer, how to update the array length when I need to remove multiple elements? – Freedom May 31 '16 at 08:42
More efficient than `db.accommodations.find( { $where: "this.name && this.name.length > 1" } )`? – Cees Timmerman Jul 25 '16 at 08:54
3

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? – WarLord Dec 05 '16 at 13:44

score 177 · Answer 3 · answered Feb 11 '13 at 15:08

177

I believe this is the fastest query that answers your question, because it doesn't use an interpreted $where clause:

{$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" ] }
>

answered Feb 11 '13 at 15:08

Tobia

17,856
6
74
93

13

@viren I don't know. This was certainly better than Javascript solutions, but for newer MongoDB you should probably use `{'name.1': {$exists: true}}` – Tobia Mar 14 '16 at 09:20
@Tobia 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. – viren Mar 16 '16 at 04:33
1

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. – Dan Dascalescu Jul 20 '18 at 10:12
2

This may be fast but falls apart if you're looking for lists > N, where N isn't small. – Brandon Hill Jan 21 '19 at 20:14
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. – Kyrstellaine Jan 28 '22 at 18:27

score 92 · Answer 4 · edited Sep 23 '15 at 19:00

92

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

edited Sep 23 '15 at 19:00

arun

10,685
6
59
81

answered Sep 06 '15 at 23:32

one_cent_thought

929
6
2

This solution is the most general, along with @JohnnyHK's since it can be used for any array size. – arun Sep 23 '15 at 19:01
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] ?? – Sudhanshu Gaur Jul 12 '16 at 20:27

s7vr · Answer 5 · 2018-01-23T21:13:50.863

88

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]}})

edited Jan 23 '18 at 21:13

answered Jan 23 '18 at 21:08

s7vr

73,656
11
106
127

1

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"`. – Dan Dascalescu Jul 20 '18 at 10:23
4

@DanDascalescu It looks like stamps is not present in all documents. You can use [ifNull](https://docs.mongodb.com/manual/reference/operator/aggregation/ifNull/) to output empty array when the stamps is not present. Something like `db.col.find({$expr:{$gt:[{$size:{$ifNull:["$passport.stamps", []]}}, 1]}})` – s7vr Jul 20 '18 at 13:29
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]} })** – Jcc.Sanabria Nov 13 '22 at 15:46

score 61 · Answer 6 · answered Oct 19 '16 at 06:37

61

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.

answered Oct 19 '16 at 06:37

Aman Goel

3,351
1
21
17

7

The same answer was given [three years earlier](https://stackoverflow.com/revisions/15224544/1). – Dan Dascalescu Jun 30 '19 at 04:01
1

can we put some dynamic number like "ArrayName." inside the query? – Sahil Mahajan Jul 22 '19 at 15:11
Yes you can use any number. If you want to fetch record greater than N then pass n. – Aman Goel Jul 23 '19 at 07:02

score 53 · Answer 7 · edited Oct 15 '19 at 06:49

53

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]}});

edited Oct 15 '19 at 06:49

Dhaval Chaudhary

5,428
2
24
39

answered Jan 30 '19 at 13:37

Daniele Tassone

2,104
2
17
25

2

While correct, this is a duplicate answer. See https://stackoverflow.com/a/48410837/2424641 by @user2683814 – SteveB Aug 12 '19 at 15:39

score 46 · Answer 8 · answered May 14 '14 at 01:06

46

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'} )

answered May 14 '14 at 01:06

lesolorzanov

3,536
8
35
53

2

javascript executes more slowly than the native operators provided by mongodb, but it's very flexible. see:http://stackoverflow.com/a/7811259/2893073, So the final solution is : http://stackoverflow.com/a/15224544/2893073 – Eddy Sep 22 '16 at 02:06

score 32 · Answer 9 · edited Jul 20 '22 at 17:00

32

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}}})

edited Jul 20 '22 at 17:00

uhfocuz

3,178
2
13
27

answered Jul 10 '17 at 01:15

Steffan Perry

2,112
1
21
21

4

I don't see why so many downvotes - this works perfectly for me! – Jake Stokes Nov 25 '18 at 18:38
1

Works perfectly fine, v 4.2.5 – jperl May 19 '20 at 18:27
2

always post version when posting solutions like that. not working on 4.2 – Lucas Flicky Jun 16 '20 at 21:36
As of 4.4, the "correct" is not working. Just because `$gt` parameter must be a number. Either `$size` parameter must be a number. – Victor Gorban Mar 18 '21 at 08:52
1

Works fine on "4.4.8" – Farhan Syah Sep 18 '21 at 11:54
1

works fine on 5.0.6 – Phạm Tuấn Anh Jun 09 '22 at 03:25
1

The people saying this doesn't work must have pasted the part that clearly says incorrect – uhfocuz Jul 20 '22 at 16:59
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. – Joseph Summerhays Feb 08 '23 at 23:23
1

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 – Garr Godfrey Mar 07 '23 at 00:49
Mysteriously it only works for me with ```$gt```, but not with ```$lt``` which is what I need .. – ינון רחמים Aug 31 '23 at 07:20

Yadvendar · Answer 10 · 2016-06-22T13:54:14.960

28

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

edited Jun 22 '16 at 13:54

answered Jun 22 '16 at 12:48

Yadvendar

955
9
14

2

This doesn't scale well to other minimum sizes (say, 10). – Dan Dascalescu Jul 20 '18 at 10:25
same as first answer – arianpress Jun 03 '20 at 06:52

Barrard · Answer 11 · 2023-08-16T06:04:27.083

14

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"

edited Aug 16 '23 at 06:04

answered Feb 06 '17 at 01:46

Barrard

1,783
1
18
25

This is essentially the same answer as [this one](https://stackoverflow.com/a/32429310/1269037), provided 2 years earlier. – Dan Dascalescu Jul 20 '18 at 10:17

Nagabhushan Baddi · Answer 12 · 2020-10-03T07:56:59.717

11

You can MongoDB aggregation to do the task:

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

edited Oct 03 '20 at 07:56

answered Oct 02 '20 at 14:29

Nagabhushan Baddi

1,164
10
18

score 3 · Answer 13 · answered May 23 '23 at 11:35

3

this will work for you

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

answered May 23 '23 at 11:35

Nishang Raiyani

194
5

score 1 · Answer 14 · answered Jan 03 '23 at 05:31

1

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

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

answered Jan 03 '23 at 05:31

Hiren Namera

390
1
10

score 1 · Answer 15 · answered Feb 09 '23 at 12:28

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
            ]
        }
    }
)

Query for documents where array size is greater than 1

15 Answers15

Linked

Related