ITKeyword - 技术文章推荐分享

首页 > mongodb - Query for documents where array size is greater than 1

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

相关推荐:mongodb - Mongo index help for $or query

: 1234, "supplier_id" : { "$gt" : 0 }, "$or" : [ { "po_number" : { "$regex" : "blahblah", "$options" : "i" } }, { "shipping.firs

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 at 5:06

user3100115

18.4k62043

asked Oct 18 '11 at 17:18

emson

1,53131318

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



 | 

5 Answers

5

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

33.7k993111

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

5

 

@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

  

 

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 at 8:42



 | 

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

122k22236239

32

 

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

8

 

Could somebody please explain how to index this.

– Ben

Oct 18 '13 at 6:28

相关推荐:MongoDB query to update nested array

docs and several other SO questions. I'm thinking I might be doing something terribly wrong. The code below is supposed to update the confirms array in the confirms array but it won't ever do anything but add new entries. Any help would be

8

 

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

  

 

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

– pikanezi

Aug 12 '15 at 16:18

  

 

Fantastic solution :) thank you :)

– Gor

Feb 11 at 16:07

 | 

show 6 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

5,4482237

2

 

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 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 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":{$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 at 4:33



 | 

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

88831429



 | 

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

4,25111536

answered Sep 6 '15 at 23:32

one_cent_thought

14112

  

 

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



 | 

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.

相关推荐:How to query for documents where array size is greater than one (1) in mongodb

e8ae86d08101908e1000001"), "name" : ["Name"], "zipcode" : ["2223"]}{ "_id" : ObjectId("4e8ae86d08101908e1000002"), "name" : ["Another ", "Name"], "zipcode" : ["2224"]}I can currently get documents that match a specific array

var ados = ados || {}; ados.run = ados.run || []; ados.run.push(function () { ados_add_placement(22,8277,"adzerk1004359091",4).setZone(43); }); ...

------分隔线----------------------------