ITKeyword,专注技术干货聚合推荐

注册 | 登录

解决mongodb - Query for documents where array size is greater than 1

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

mongodb mongodb-query
|
  this question
edited Jun 11 '16 at 5:06 s.s 24.9k 8 34 68 asked Oct 18 '11 at 17:18 emson 1,978 4 14 24 3   The newer versions of MongoDB have the $size operator; you should check out @tobia's answer –  Dropped.on.Caprica Mar 27 '14 at 17:55

 | 

9 Answers
9

-------Accepted-------Accepted-------Accepted-------

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


|
  this answer
edited Jan 4 '15 at 14:39 answered Oct 18 '11 at 17:27 Andrew Orsich 37.6k 9 103 118 4   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 6   @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. –  Brian 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 8:42  |  show more comments

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

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

|
  this answer
edited Nov 12 '13 at 21:18 answered Mar 5 '13 at 13:05 JohnnyHK 154k 26 317 287 54   IMO, this is as of 2.2 a much better answer than the currently -------Accepted-------Accepted-------Accepted-------
answer. –  Eli May 21 '13 at 19:57 10   Could somebody please explain how to index this. –  Ben Oct 18 '13 at 6:28 10   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 4 '14 at 19:13 1   Works on 3.0 aswell. Thank you so much for finding this. –  pikanezi Aug 12 '15 at 16:18 1   @JoseRicardoBustosM. That would find the docs where name contains at least 1 element, but the OP was looking for greater than 1. –  JohnnyHK Jul 22 '16 at 16:15  |  show more comments

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

|
  this answer
answered Feb 11 '13 at 15:08 Tobia 7,079 33 51 3   This should be the -------Accepted-------Accepted-------Accepted-------
answer. –  Dropped.on.Caprica Mar 27 '14 at 17:54      i like this approach –  Alex Oct 9 '15 at 15:01      @Tobia does it use any indexes to
  performance. –  viren Mar 14 '16 at 5:18 1   @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 9: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":{$exis‌​ts: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 4:33

 | 

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


|
  this answer
edited Sep 23 '15 at 19:00 arun 5,175 2 20 41 answered Sep 6 '15 at 23:32 one_cent_thought 291 3 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

 | 

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

|
  this answer
answered May 14 '14 at 1:06 Zloy Smiertniy 1,170 5 18 31      javascript executes more slowly than the native operators provided by mongodb, but it's very flexible. see: stackoverflow.com/a/7811259/2893073, So the final solution is : stackoverflow.com/a/15224544/2893073 –  Eddy Sep 22 '16 at 2:06

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

|
  this answer
edited Jun 22 '16 at 13:54 answered Jun 22 '16 at 12:48 Yadvendar 476 5 9

 | 

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.


|
  this answer
answered Oct 19 '16 at 6:37 Aman Goel 550 5 5      Pretty neat trick! :) –  3zzy Jan 15 at 1:51      Thanks. 3zzy :) –  Aman Goel Mar 3 at 6:45

 | 

You can simply find whether second element exists.

db.accommodations.find({'name.1': {$exists: true}});

|
  this answer
answered Feb 13 at 2:00 Lakmal Vithanage 1,176 2 11 32 1   IMO, this is the most straight-forward solution. –  Kirill Rakhman 2 days ago

 | 

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 thats true for all the documents. If blank, i would get

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

|
  this answer
answered Feb 6 at 1:46 Barrard 97 1 8

 | 

protected by chridam Dec 24 '15 at 12:03

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged mongodb mongodb-query or ask your own question.


相关阅读排行


用户评论

游客

相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。