Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 346
emson Avatar asked Oct 18 '11 17:10

emson


People also ask

Is there a way to query array fields with size greater than some specified value?

Yes, you can do that using $expr operator along with the $size operator. Here, we are using $expr operator to filter out all those documents which has size greater than or equal to 4.

What is the maximum size of an array inside a document?

An array can have a maximum of 32 dimensions.

What is $size in MongoDB?

$size. Counts and returns the total number of items in an array. $size has the following syntax: { $size: <expression> } The argument for $size can be any expression as long as it resolves to an array.

How do I get the size of a field in MongoDB?

The $size operator in MongoDB is used to fetch the document that has an array field of a specific size. The $size only deals with arrays and accepts only numeric values as a parameter.


3 Answers

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}}}
);
like image 195
JohnnyHK Avatar answered Nov 09 '22 08:11

JohnnyHK


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.

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

like image 32
Andrew Orsich Avatar answered Nov 09 '22 07:11

Andrew Orsich


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" ] }
>
like image 169
Tobia Avatar answered Nov 09 '22 08:11

Tobia