Query for documents where array size is greater than 1

mongodb aggregate array size greater than
mongodb array length greater than
size needs a number
mongodb query document size
mongodb query max array size
mongoose get array length
mongodb sort by array length
mongodb query size limit

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

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

Query for documents where array size is greater than 1, The query for documents where array size is greater than 1 can be written using $where:- db.accommodations.find( { $where: "this.name.length >  To understand the above syntax, let us create a collection with some documents. Display all documents from a collection with the help of find () method. Here is the query for documents where array size is greater than 1.

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

Query for documents where array size is greater than 1 in MongoDB?, You can use length to query for documents where array size is greater than 1:db.​yourCollectionName.find({$where:this. Welcome to Intellipaat Community. Get your technical queries answered by top developers !

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

MongoDB – find all documents where an array / list size is greater , How to find all documents where “tag” size is greater than 3? array size is greater than 1 · MongoDB $where clause to query array length. Query for documents where array size is greater than 1 - Wikitechy

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

mongodb Query for documents where array size is greater than 1 , Query for documents where array size is greater than 1? // Find all docs that have at least a second name array element. db.accommodations.find({'name.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)? Update: For mongodb versions 2.2+ more efficient way to do this described by @JohnnyHK in another answer.

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.

Query for documents where array size is greater than 1, Update: For mongodb versions 2.2+ more efficient way to do this. 1.To Using $​where db.accommodations.find( { $where: "this.name.length > 1" }  There is no query operator that lets you directly test for the size of an array field being greater than some value, but a trick I've seen used before is to test for the existence of an array element to deduce information about the length of an array field.

$size, The $size operator matches any array with the number of elements specified returns all documents in collection where field is an array with 2 elements. To match fields with only one element within an array use $size with a value of 1, as follows: Queries cannot use indexes for the $size portion of a query, although the  Return only matched sub-document elements within a nested array. The main collection is retailer, which contains an array for stores. Each store contains an array of offers (you can buy in this store). This offers array has an array of sizes.

Query documents with array size greater than 1, How to select all documents whose name array size is larger than one (it is better not to modify the current data structure)?  The projection parameter takes a document of the following form: { field1: <value>, field2: <value> } The <value> can be any of the following: 1 or true to include the field in the return documents. 0 or false to exclude the field. NOTE. For the _id field, you do not have to explicitly specify _id: 1 to return the _id field.

MongoDB Query Tip : Find All The Documents Where Array Length , MongoDB Query Tip : Find All The Documents Where Array Length is Greater Than N. Suppose we have blog document in blogs collection as shown below. 1. 2. 3. 4. > db.blogs.insert({author : "Shekhar Gulati","title":"Hello World" all those blogs which have more than 2 tags then query is shown below. Data Types in the mongo Shell. mongo Shell Quick Reference. MongoDB CRUD Operations. Insert Documents. Query Documents. Query on Embedded/Nested Documents. Query an Array of Embedded Documents. Project Fields to Return from Query. Query for Null or Missing Fields. Iterate a Cursor in the mongo Shell. Update Documents.

Comments
  • The newer versions of MongoDB have the $size operator; you should check out @tobia's answer
  • Actual solution: FooArray:{$gt:{$size:'length'}} --> lenght can be any number
  • 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; } "} );
  • you are welcome, yes you can use any javascript in $where, it is very flexible.
  • @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.
  • 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
  • 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?
  • Could somebody please explain how to index this.
  • 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.
  • Works on 3.0 aswell. Thank you so much for finding this.
  • @Dims No difference, really: {'Name Field.1': {$exists: true}}.
  • @JoseRicardoBustosM. That would find the docs where name contains at least 1 element, but the OP was looking for greater than 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}}