I have a &quot;status&quot; collection like this strcture -

    {
        _id: ObjectId(&quot;545a0b63b03dbcd1238b4567&quot;),
        status: 1004,
        comment: &quot;Rem dolor ipsam placeat omnis non. Aspernatur nobis qui nisi similique.&quot;,
        created_at: ISODate(&quot;2014-11-05T11:34:59.804Z&quot;)
    },
    {
        _id: ObjectId(&quot;545a0b66b03dbcd1238b4568&quot;),
        status: 1001,
        comment: &quot;Sint et eos vero ipsa voluptatem harum. Hic unde voluptatibus et blanditiis quod modi.&quot;,
        created_at: ISODate(&quot;2014-11-05T11:35:02.814Z&quot;)
    }
    ....
    ....

**I need to get result grouped by 15 minutes interval from that collection.**

Group result by 15 minutes time interval in MongoDb

Is the aggregation framework introduced in mongodb 2.2, has any special performance improvements over map/reduce?

If yes, why and how and how much?

(Already I have done a test for myself, and the performance was nearly same)

Is Mongodb Aggregation framework faster than map/reduce?

I&#39;ve got a list of articles, and each of them has an array property which lists various individuals mentioned in them:

    _id: {
        $oid: &quot;52b632a9e4f2ba13c82ccd23&quot;
    },
    providerName: &quot;The Guardian&quot;,
    url: &quot;http://feeds.theguardian.com/c/34708/f/663860/s/3516cebc/sc/38/l/0L0Stheguardian0N0Cmusic0C20A130Cdec0C220Cwaterboys0Efishermans0Eblues0Etour0Ehammersmith/story01.htm&quot;,
    subject: &quot;The Waterboys – review&quot;,
    class_artist: [
        &quot;paul mccartney&quot;
    ]

I&#39;ve been trying (unsuccessfully) to get a list of all the individual artists (`class_artist`), based on the number of articles they&#39;ve been tagged in within the past 7 days.

I&#39;ve gotten as far as:

    var date = new Date();
    date.setDate(date.getDate() - 7);
    
    db.articles.group({
        key: { class_artist: 1 },
        cond: { class_date: { $gt: date } },
        reduce: function ( curr, result ) { result.cnt++; },
        initial: { cnt : 0 }
    }).sort({cnt: -1});

But unfortunately, it doesn&#39;t count them based on the individual array values, but by array compositions (that is, lists of artists).

I tried using the `$unwind` function, but have not been able to make it work.

MongoDB group by array inner-elements

I have two collections

1. User
   
        {
           &quot;_id&quot; : ObjectId(&quot;584aac38686860d502929b8b&quot;),
           &quot;name&quot; : &quot;John&quot;
        }

2. Role
 

        {
           &quot;_id&quot; : ObjectId(&quot;584aaca6686860d502929b8d&quot;),
           &quot;role&quot; : &quot;Admin&quot;,
           &quot;userId&quot; : &quot;584aac38686860d502929b8b&quot;  
        }


I want to join these collection based on the **userId** (in **role** collection) - **_id** ( in **user** collection).

I tried the below query:

   

    db.role.aggregate({
      &quot;$lookup&quot;: {
        &quot;from&quot;: &quot;user&quot;,
        &quot;localField&quot;: &quot;userId&quot;,
        &quot;foreignField&quot;: &quot;_id&quot;,
        &quot;as&quot;: &quot;output&quot;
      }
    })

This gives me expected results as long as i store userId as a ObjectId. When my userId is a string there are no results. 
Ps: I tried 

&gt; foreignField: &#39;_id&#39;.valueOf()

 and 

&gt; foreignField: &#39;_id&#39;.toString()

. But no luck to match/join based on a ObjectId-string fields.

Any help will be appreciated.



Mongodb Join on _id field from String to ObjectId

I have a query like this (simplified):

&lt;!-- language-all: lang-javascript --&gt;

    db.collection.aggregate([
      { $match: { main_id: ObjectId(&quot;58f0f67f50c6af16709fd2c7&quot;) } }, 
      {
        $group: {
          _id: &quot;$name&quot;,
          count: { $sum: 1 },
          sum: { $sum: { $add: [&quot;$P31&quot;, &quot;$P32&quot;] } }
        }
      }
    ])

I do this query from Java, and I want to map it on my class, but I don&#39;t want `_id` to be mapped on `name` field. Because if I do something like this:

&lt;!-- language-all: lang-java --&gt;

    @JsonProperty(&quot;_id&quot;)
    private String name;

then when I save this data back to mongo (after some modification) the data is saved with name as `_id` while I want a real Id to be generated.

So, how can I **rename** `_id` after `$group` operation?


Is it possible to rename _id field after mongo&#39;s group aggregation?

How can I (in MongoDB) combine data from multiple collections into one collection?

Can I use map-reduce and if so then how? 

I would greatly appreciate some example as I am a novice. 

MongoDB: Combine data from multiple collections into one..how?

I am playing around with MongoDB trying to figure out how to do a simple

    SELECT province, COUNT(*) FROM contest GROUP BY province

But I can&#39;t seem to figure it out using the aggregate function. I can do it using some really weird group syntax

    db.user.group({
        &quot;key&quot;: {
            &quot;province&quot;: true
        },
        &quot;initial&quot;: {
            &quot;count&quot;: 0
        },
        &quot;reduce&quot;: function(obj, prev) {
            if (true != null) if (true instanceof Array) prev.count += true.length;
            else prev.count++;
        }
    });

But is there an easier/faster way using the aggregate function?

MongoDB SELECT COUNT GROUP BY

How would I find duplicate fields in a mongo collection.

I&#39;d like to check if any of the &quot;name&quot; fields are duplicates.

    {
        &quot;name&quot; : &quot;ksqn291&quot;,
        &quot;__v&quot; : 0,
        &quot;_id&quot; : ObjectId(&quot;540f346c3e7fc1054ffa7086&quot;),
        &quot;channel&quot; : &quot;Sales&quot;
    }

Many thanks!

Find duplicate records in MongoDB

For example, I have these documents:

    {
      &quot;addr&quot;: &quot;address1&quot;,
      &quot;book&quot;: &quot;book1&quot;
    },
    {
      &quot;addr&quot;: &quot;address2&quot;,
      &quot;book&quot;: &quot;book1&quot;
    },
    {
      &quot;addr&quot;: &quot;address1&quot;,
      &quot;book&quot;: &quot;book5&quot;
    },
    {
      &quot;addr&quot;: &quot;address3&quot;,
      &quot;book&quot;: &quot;book9&quot;
    },
    {
      &quot;addr&quot;: &quot;address2&quot;,
      &quot;book&quot;: &quot;book5&quot;
    },
    {
      &quot;addr&quot;: &quot;address2&quot;,
      &quot;book&quot;: &quot;book1&quot;
    },
    {
      &quot;addr&quot;: &quot;address1&quot;,
      &quot;book&quot;: &quot;book1&quot;
    },
    {
      &quot;addr&quot;: &quot;address15&quot;,
      &quot;book&quot;: &quot;book1&quot;
    },
    {
      &quot;addr&quot;: &quot;address9&quot;,
      &quot;book&quot;: &quot;book99&quot;
    },
    {
      &quot;addr&quot;: &quot;address90&quot;,
      &quot;book&quot;: &quot;book33&quot;
    },
    {
      &quot;addr&quot;: &quot;address4&quot;,
      &quot;book&quot;: &quot;book3&quot;
    },
    {
      &quot;addr&quot;: &quot;address5&quot;,
      &quot;book&quot;: &quot;book1&quot;
    },
    {
      &quot;addr&quot;: &quot;address77&quot;,
      &quot;book&quot;: &quot;book11&quot;
    },
    {
      &quot;addr&quot;: &quot;address1&quot;,
      &quot;book&quot;: &quot;book1&quot;
    }

and so on.&lt;br&gt;&lt;br&gt;&lt;br&gt;How can I make a request, which will describe the top N addresses and the top M books per address?&lt;br&gt;&lt;br&gt;Example of expected result:&lt;br&gt;&lt;br&gt; address1 | book_1: 5&lt;br&gt;          | book_2: 10&lt;br&gt;          | book_3: 50&lt;br&gt;          | total: 65&lt;br&gt; ______________________&lt;br&gt; address2 | book_1: 10&lt;br&gt;          | book_2: 10&lt;br&gt;          |...&lt;br&gt;          | book_M: 10&lt;br&gt;          | total: M*10&lt;br&gt;...&lt;br&gt; ______________________&lt;br&gt; addressN | book_1: 20&lt;br&gt;          | book_2: 20&lt;br&gt;          |...&lt;br&gt;          | book_M: 20&lt;br&gt;          | total: M*20

mongodb group values by multiple fields

I would like to define a $project aggregation stage where I can instruct it to add a new field and include all existing fields, without having to list all the existing fields.

My document looks like this, with many fields:

&lt;!-- language: lang-json --&gt;

    {
        obj: {
            obj_field1: &quot;hi&quot;,
            obj_field2: &quot;hi2&quot;
        },
        field1: &quot;a&quot;,
        field2: &quot;b&quot;,
        ...
        field26: &quot;z&quot;
    }

I want to make an aggregation operation like this:

&lt;!-- language: lang-json --&gt;

    [
        {
            $project: {
                custom_field: &quot;$obj.obj_field1&quot;,
                //the next part is that I don&#39;t want to do
                field1: 1,
                field2: 1,
                ...
                field26: 1
            }
        },
        ... //group, match, and whatever...
    ]

Is there something like an &quot;include all fields&quot; keyword that I can use in this case, or some other way to avoid having to list every field separately?

Include all existing fields and add new fields to document

Is there a query for calculating how many distinct values a field contains in DB.

f.e I have a field for country and there are 8 types of country values (spain, england, france, etc...)

If someone adds more documents with a new country I would like the query to return 9.

Is there easier way then group and count?



mongodb count num of distinct values per field/key

&lt;!-- language-all: lang-js --&gt;

Is there an explain function for the Aggregation framework in MongoDB? I can&#39;t see it in the documentation.

If not is there some other way to check, how a query performs within the aggregation framework?

I know with find you just do 

    db.collection.find().explain()

But with the aggregation framework I get an error

    db.collection.aggregate(
    	{ $project : { &quot;Tags._id&quot; : 1 }},
    	{ $unwind : &quot;$Tags&quot; },
    	{ $match: {$or: [{&quot;Tags._id&quot;:&quot;tag1&quot;},{&quot;Tags._id&quot;:&quot;tag2&quot;}]}},
    	{ 
    		$group: 
    		{ 
    			_id : { id: &quot;$_id&quot;},
    			&quot;count&quot;: { $sum:1 } 
    		}
    	},
    	{ $sort: {&quot;count&quot;:-1}}
    ).explain()

Mongodb Explain for Aggregation framework

What&#39;s the syntax for doing a $lookup on a field that is an array of ObjectIds rather than just a single ObjectId?

Example Order Document:

    {
      _id: ObjectId(&quot;...&quot;),
      products: [
        ObjectId(&quot;..&lt;Car ObjectId&gt;..&quot;),
        ObjectId(&quot;..&lt;Bike ObjectId&gt;..&quot;)
      ]
    }

Not Working Query:

    db.orders.aggregate([
	    {
	       $lookup:
		     {
		       from: &quot;products&quot;,
		       localField: &quot;products&quot;,
		       foreignField: &quot;_id&quot;,
		       as: &quot;productObjects&quot;
		     }
	    }
    ])

Desired Result

    {
      _id: ObjectId(&quot;...&quot;),
      products: [
        ObjectId(&quot;..&lt;Car ObjectId&gt;..&quot;),
        ObjectId(&quot;..&lt;Bike ObjectId&gt;..&quot;)
      ],
      productObjects: [
        {&lt;Car Object&gt;},
        {&lt;Bike Object&gt;}
      ],
    }

$lookup on ObjectId&#39;s in an array

This is my first day with MongoDB so please go easy with me :)

I can&#39;t understand the `$unwind` operator, maybe because English is not my native language.

    db.article.aggregate(
        { $project : {
            author : 1 ,
            title : 1 ,
            tags : 1
        }},
        { $unwind : &quot;$tags&quot; }
    );

The project operator is something I can understand, I suppose (it&#39;s like `SELECT`, isn&#39;t it?). But then, `$unwind` (citing) *returns one document for every member of the unwound array within every source document*.

Is this like a `JOIN`? If yes, how the result of `$project` (with `_id`, `author`, `title` and `tags` fields) can be compared with the `tags` array?

**NOTE**: I&#39;ve taken the example from MongoDB website, I don&#39;t know the structure of `tags` array. I think it&#39;s a simple array of tag names.

What&#39;s the $unwind operator in MongoDB?

I have array in subdocument like this

    {
    	&quot;_id&quot; : ObjectId(&quot;512e28984815cbfcb21646a7&quot;),
    	&quot;list&quot; : [
    		{
    			&quot;a&quot; : 1
    		},
    		{
    			&quot;a&quot; : 2
    		},
    		{
    			&quot;a&quot; : 3
    		},
    		{
    			&quot;a&quot; : 4
    		},
    		{
    			&quot;a&quot; : 5
    		}
    	]
    }

Can I filter subdocument for a &gt; 3

My expect result below

    {
    	&quot;_id&quot; : ObjectId(&quot;512e28984815cbfcb21646a7&quot;),
    	&quot;list&quot; : [
    		{
    			&quot;a&quot; : 4
    		},
    		{
    			&quot;a&quot; : 5
    		}
    	]
    }

I try to use `$elemMatch` but returns the first matching element in the array

My query:

    db.test.find( { _id&quot; : ObjectId(&quot;512e28984815cbfcb21646a7&quot;) }, { 
    	list: { 
    		$elemMatch: 
    			{ a: { $gt:3 } 
    			} 
    	} 
    } )

The result return one element in array

    { &quot;_id&quot; : ObjectId(&quot;512e28984815cbfcb21646a7&quot;), &quot;list&quot; : [ { &quot;a&quot; : 4 } ] }

and I try to use aggregate with `$match` but not work

    db.test.aggregate({$match:{_id:ObjectId(&quot;512e28984815cbfcb21646a7&quot;), &#39;list.a&#39;:{$gte:5}  }})

It&#39;s return all element in array

    {
        &quot;_id&quot; : ObjectId(&quot;512e28984815cbfcb21646a7&quot;),
        &quot;list&quot; : [
            {
                &quot;a&quot; : 1
            },
            {
                &quot;a&quot; : 2
            },
            {
                &quot;a&quot; : 3
            },
            {
                &quot;a&quot; : 4
            },
            {
                &quot;a&quot; : 5
            }
        ]
    }

Can I filter element in array to get result as expect result?

How to filter array in subdocument with MongoDB

Is it possible to do an OR in the $match? 

I mean something like this: 

    db.articles.aggregate(
        { $or: [ $match : { author : &quot;dave&quot; }, $match : { author : &quot;john&quot; }] }
    );

MongoDB aggregation framework match OR

When using MongoDB&#39;s [`$in`][1] clause, does the order of the returned documents always correspond to the order of the array argument?


  [1]: http://docs.mongodb.org/manual/reference/operator/query/in/

Does MongoDB&#39;s $in clause guarantee order

In MongoDB, is it possible to update the value of a field using the value from another field?  The equivalent SQL would be something like:

    UPDATE Person SET Name = FirstName + &#39; &#39; + LastName

And the MongoDB pseudo-code would be:

    db.person.update( {}, { $set : { name : firstName + &#39; &#39; + lastName } );

Update MongoDB field using value of another field

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. (See example below)

Now I try to find all offers, which are available in the size ``L``.

    {
        &quot;_id&quot; : ObjectId(&quot;56f277b1279871c20b8b4567&quot;),
        &quot;stores&quot; : [
            {
            &quot;_id&quot; : ObjectId(&quot;56f277b5279871c20b8b4783&quot;),
            &quot;offers&quot; : [
                {
                    &quot;_id&quot; : ObjectId(&quot;56f277b1279871c20b8b4567&quot;),
                    &quot;size&quot;: [
                        &quot;XS&quot;,
                        &quot;S&quot;,
                        &quot;M&quot;
                    ]
                },
                {
                    &quot;_id&quot; : ObjectId(&quot;56f277b1279871c20b8b4567&quot;),
                    &quot;size&quot;: [
                        &quot;S&quot;,
                        &quot;L&quot;,
                        &quot;XL&quot;
                    ]
                }
            ]
        }
    }

I&#39;ve try this query: ``db.getCollection(&#39;retailers&#39;).find({&#39;stores.offers.size&#39;: &#39;L&#39;})``

I expect some Output like that:

     {
    &quot;_id&quot; : ObjectId(&quot;56f277b1279871c20b8b4567&quot;),
    &quot;stores&quot; : [
        {
            &quot;_id&quot; : ObjectId(&quot;56f277b5279871c20b8b4783&quot;),
            &quot;offers&quot; : [
                {
                    &quot;_id&quot; : ObjectId(&quot;56f277b1279871c20b8b4567&quot;),
                    &quot;size&quot;: [
                        &quot;S&quot;,
                        &quot;L&quot;,
                        &quot;XL&quot;
                    ]
                }
            ]
        }
    }

But the Output of my Query contains also the non matching offer with ``size`` XS,X and M.

How I can force MongoDB to return only the offers, which matched my query?

Greetings and thanks.

Return only matched sub-document elements within a nested array

&lt;!-- language-all: lang-javascript --&gt;

Suppose you have the following documents in my collection:

    {  
       &quot;_id&quot;:ObjectId(&quot;562e7c594c12942f08fe4192&quot;),
       &quot;shapes&quot;:[  
          {  
             &quot;shape&quot;:&quot;square&quot;,
             &quot;color&quot;:&quot;blue&quot;
          },
          {  
             &quot;shape&quot;:&quot;circle&quot;,
             &quot;color&quot;:&quot;red&quot;
          }
       ]
    },
    {  
       &quot;_id&quot;:ObjectId(&quot;562e7c594c12942f08fe4193&quot;),
       &quot;shapes&quot;:[  
          {  
             &quot;shape&quot;:&quot;square&quot;,
             &quot;color&quot;:&quot;black&quot;
          },
          {  
             &quot;shape&quot;:&quot;circle&quot;,
             &quot;color&quot;:&quot;green&quot;
          }
       ]
    }


----------


Do query:

    db.test.find({&quot;shapes.color&quot;: &quot;red&quot;}, {&quot;shapes.color&quot;: 1})
Or

    db.test.find({shapes: {&quot;$elemMatch&quot;: {color: &quot;red&quot;}}}, {&quot;shapes.color&quot;: 1})


----------


Returns matched document *(Document 1)*, but always with ALL array items in `shapes`:

    { &quot;shapes&quot;: 
      [
        {&quot;shape&quot;: &quot;square&quot;, &quot;color&quot;: &quot;blue&quot;},
        {&quot;shape&quot;: &quot;circle&quot;, &quot;color&quot;: &quot;red&quot;}
	  ] 
    }

However, I&#39;d like to get the document *(Document 1)* only with the array that contains `color=red`:

    { &quot;shapes&quot;: 
      [
        {&quot;shape&quot;: &quot;circle&quot;, &quot;color&quot;: &quot;red&quot;}
	  ] 
    }

How can I do this?


Retrieve only the queried element in an object array in MongoDB collection

For some collection with a field `{ wins: Number }`, how could I use *MongoDB Aggregation Framework* to get the total number of wins across all documents in a collection?

### Example:
If I have 3 documents with `wins: 5`, `wins: 8`, `wins: 12` respectively, how could I use MongoDB Aggregation Framework to return the total number, i.e. `total: 25`.

How to aggregate sum in MongoDB to get a total count?

**Env:**
 
 - MongoDB (3.2.0) with Mongoose


----------


**Collection:**

- users


----------


**Text Index creation:**


      BasicDBObject keys = new BasicDBObject();
      keys.put(&quot;name&quot;,&quot;text&quot;);
    
      BasicDBObject options = new BasicDBObject();
      options.put(&quot;name&quot;, &quot;userTextSearch&quot;);
      options.put(&quot;unique&quot;, Boolean.FALSE);
      options.put(&quot;background&quot;, Boolean.TRUE);
      
      userCollection.createIndex(keys, options); // using MongoTemplate


----------


**Document:**

- {&quot;name&quot;:&quot;LEONEL&quot;}


----------


**Queries:**

- `db.users.find( { &quot;$text&quot; : { &quot;$search&quot; : &quot;LEONEL&quot; } } )` =&gt; FOUND
- `db.users.find( { &quot;$text&quot; : { &quot;$search&quot; : &quot;leonel&quot; } } )` =&gt; FOUND (search caseSensitive is false)
- `db.users.find( { &quot;$text&quot; : { &quot;$search&quot; : &quot;LEON&#201;L&quot; } } )` =&gt; FOUND (search with diacriticSensitive is false)
- `db.users.find( { &quot;$text&quot; : { &quot;$search&quot; : &quot;LEONE&quot; } } )` =&gt; FOUND (Partial search)
- `db.users.find( { &quot;$text&quot; : { &quot;$search&quot; : &quot;LEO&quot; } } )` =&gt; NOT FOUND (Partial search)
- `db.users.find( { &quot;$text&quot; : { &quot;$search&quot; : &quot;L&quot; } } )` =&gt; NOT FOUND (Partial search)


Any idea why I get 0 results using as query &quot;LEO&quot; or &quot;L&quot;?

Regex with Text Index Search is not allowed.


    db.getCollection(&#39;users&#39;)
         .find( { &quot;$text&quot; : { &quot;$search&quot; : &quot;/LEO/i&quot;, 
                              &quot;$caseSensitive&quot;: false, 
                              &quot;$diacriticSensitive&quot;: false }} )
         .count() // 0 results

    db.getCollection(&#39;users&#39;)
         .find( { &quot;$text&quot; : { &quot;$search&quot; : &quot;LEO&quot;, 
                              &quot;$caseSensitive&quot;: false, 
                              &quot;$diacriticSensitive&quot;: false }} )
    .count() // 0 results


----------


**MongoDB Documentation:**

- [Text Search][1]
- [$text][2]
- [Text Indexes][3]
- [Improve Text Indexes to support partial word match][4]


  [1]: https://docs.mongodb.com/v3.2/text-search/
  [2]: https://docs.mongodb.com/manual/reference/operator/query/text/
  [3]: https://docs.mongodb.com/manual/core/index-text/
  [4]: https://jira.mongodb.org/browse/SERVER-15090

MongoDB Full and Partial Text Search

I want to convert ObjectID (Mongodb) to String in JavaScript.
When I get a Object form MongoDB. it like as a object has: timestamp, second, inc, machine.
I can&#39;t convert to string.

Convert ObjectID (Mongodb) to String in JavaScript

I have a mongoDB collection with millions of rows and I&#39;m trying to optimize my queries. I&#39;m currently using the aggregation framework to retrieve data and group them as I want. My typical aggregation query is something like : `$match &gt; $group &gt; $ group &gt; $project`

However, I noticed that the last parts only take a few ms, the beginning is the slowest.

I tried to perform a query with only the $match filter, and then to perform the same query with collection.find. The aggregation query takes ~80ms while the find query takes 0 or 1ms.

I have indexes on pretty much each field so I guess this isn&#39;t the problem. Any idea on what could go wrong ? Or is it just a &quot;normal&quot; drawback of the aggregation framework ?

I could use find queries instead of aggregation queries, however I would have to perform a lot of processing after the request and this process can be done quickly with `$group` etc. so I would rather keep the aggregation framework.

Thanks,

EDIT :

Here is my criteria :

    {
        &quot;action&quot; : &quot;click&quot;,
        &quot;timestamp&quot; : {
                &quot;$gt&quot; : ISODate(&quot;2015-01-01T00:00:00Z&quot;),
                &quot;$lt&quot; : ISODate(&quot;2015-02-011T00:00:00Z&quot;)
        },
        &quot;itemId&quot; : &quot;5&quot;
    }



MongoDB {aggregation $match} vs {find} speed

I have a hard time believing this question hasn&#39;t been asked and answered somewhere already, but I can&#39;t find any trace of it.

I have a MongoDB aggregation query that needs to group by a boolean: the existence of another field.

For example let&#39;s start with this collection:

&lt;!-- language: lang-js --&gt;

    &gt; db.test.find()
    { &quot;_id&quot; : ObjectId(&quot;53fbede62827b89e4f86c12e&quot;),
      &quot;field&quot; : ObjectId(&quot;53fbede62827b89e4f86c12d&quot;), &quot;name&quot; : &quot;Erik&quot; }
    { &quot;_id&quot; : ObjectId(&quot;53fbee002827b89e4f86c12f&quot;), &quot;name&quot; : &quot;Erik&quot; }
    { &quot;_id&quot; : ObjectId(&quot;53fbee092827b89e4f86c131&quot;),
      &quot;field&quot; : ObjectId(&quot;53fbee092827b89e4f86c130&quot;), &quot;name&quot; : &quot;John&quot; }
    { &quot;_id&quot; : ObjectId(&quot;53fbee122827b89e4f86c132&quot;), &quot;name&quot; : &quot;Ben&quot; }

2 documents have &quot;field&quot;, and 2 don&#39;t.
Note that each value of &quot;field&quot; may be different; we just want to group on its existence (or non-nullness works for me too, I don&#39;t have any null values stored).

I&#39;ve tried using $project, but $exists doesn&#39;t exist there, and $cond and $ifNull haven&#39;t helped me.  The field always appears to exist, even when it doesn&#39;t:

&lt;!-- language: lang-js --&gt;

    &gt; db.test.aggregate(
      {$project:{fieldExists:{$cond:[{$eq:[&quot;$field&quot;, null]}, false, true]}}},
      {$group:{_id:&quot;$fieldExists&quot;, count:{$sum:1}}}
    )
    { &quot;_id&quot; : true, &quot;count&quot; : 4 }


I would expect the following much simpler aggregate to work, but for some reason $exists isn&#39;t supported in this way:

    &gt; db.test.aggregate({$group:{_id:{$exists:&quot;$field&quot;}, count:{$sum:1}}})
    assert: command failed: {
      &quot;errmsg&quot; : &quot;exception: invalid operator &#39;$exists&#39;&quot;,
      &quot;code&quot; : 15999,
      &quot;ok&quot; : 0
    } : aggregate failed
    Error: command failed: {
      &quot;errmsg&quot; : &quot;exception: invalid operator &#39;$exists&#39;&quot;,
      &quot;code&quot; : 15999,
      &quot;ok&quot; : 0
    } : aggregate failed
        at Error (&lt;anonymous&gt;)
        at doassert (src/mongo/shell/assert.js:11:14)
        at Function.assert.commandWorked (src/mongo/shell/assert.js:244:5)
        at DBCollection.aggregate (src/mongo/shell/collection.js:1149:12)
        at (shell):1:9
    2014-08-25T19:19:42.344-0700 Error: command failed: {
      &quot;errmsg&quot; : &quot;exception: invalid operator &#39;$exists&#39;&quot;,
      &quot;code&quot; : 15999,
      &quot;ok&quot; : 0
    } : aggregate failed at src/mongo/shell/assert.js:13


Does anyone know how to get the desired result from a collection like this?

Expected result:

&lt;!-- language: lang-js --&gt;

    { &quot;_id&quot; : true, &quot;count&quot; : 2 }
    { &quot;_id&quot; : false, &quot;count&quot; : 2 }

MongoDB aggregate by field exists

Trying to create a MongoDB data source with [icCube][1]. The idea is to return the size of an array as a new field. Something like :

    $project:
    { 
     &quot;people&quot;: 1, 
     &quot;Count myFieldArray&quot; : {$size : &quot;$myFieldArray&quot; }
    }

But I&#39;m getting for some records the following error :

    The argument to $size must be an Array, but was of type: EOO

Is there a way that size is 0 if the field is empty or not an array (getting rid of the error) ?


  [1]: http://www.iccube.com

MongoDB - The argument to $size must be an Array, but was of type: EOO / missing

So as you all know, `find()` returns an array of results, with `findOne()` returning just a simply object.

With Angular, this makes a huge difference. Instead of going `{{myresult[0].name}}`, I can simply just write `{{myresult.name}}`.

I have found that the `$lookup` method in the aggregate pipeline returns an array of results instead of just a single object.

For example, I have two colletions:

**`users` collection:**

    [{
      &quot;firstName&quot;: &quot;John&quot;,
      &quot;lastName&quot;: &quot;Smith&quot;,
      &quot;country&quot;: 123
    }, {
      &quot;firstName&quot;: &quot;Luke&quot;,
      &quot;lastName&quot;: &quot;Jones&quot;,
      &quot;country&quot;: 321
    }]

**`countries` collection:**

    [{
      &quot;name&quot;: &quot;Australia&quot;,
      &quot;code&quot;: &quot;AU&quot;,
      &quot;_id&quot;: 123
    }, {
      &quot;name&quot;: &quot;New Zealand&quot;,
      &quot;code&quot;: &quot;NZ&quot;,
      &quot;_id&quot;: 321
    }]

**My aggregate `$lookup`:**

    db.users.aggregate([{
      $project: {
        &quot;fullName&quot;: {
          $concat: [&quot;$firstName&quot;, &quot; &quot;, &quot;$lastName&quot;]
        },
        &quot;country&quot;: &quot;$country&quot;
      }
    }, {
      $lookup: {
        from: &quot;countries&quot;,
        localField: &quot;country&quot;,
        foreignField: &quot;_id&quot;,
        as: &quot;country&quot;
      }
    }])

**The results from the query:**

    [{
      &quot;fullName&quot;: &quot;John Smith&quot;,
      &quot;country&quot;: [{
        &quot;name&quot;: &quot;Australia&quot;,
        &quot;code&quot;: &quot;AU&quot;,
        &quot;_id&quot;: 123
      }]
    }, {
     &quot;fullName&quot;: &quot;Luke Jones&quot;,
     &quot;country&quot;: [{
       &quot;name&quot;: &quot;New Zealand&quot;,
       &quot;code&quot;: &quot;NZ&quot;,
       &quot;_id&quot;: 321
     }]
    }]

As you can see by the above results, each `country` is an array instead of a single object like `&quot;country&quot;: {....}`.

How can I have my `$lookup` return a single object instead of an array since it will only ever match a single document?

How to use MongoDBs aggregate `$lookup` as `findOne()`

I am interested in optimizing a &quot;pagination&quot; solution I&#39;m working on with MongoDB. My problem is straight forward. I usually limit the number of documents returned using the `limit()` functionality. This forces me to issue a redundant query without the `limit()` function in order for me to also capture the total number of documents in the query so I can pass to that to the client letting them know they&#39;ll have to issue an additional request(s) to retrieve the rest of the documents.

Is there a way to condense this into 1 query? Get the total number of documents but at the same time only retrieve a subset using `limit()`? Is there a different way to think about this problem than I am approaching it?

Get a count of total documents with MongoDB when using limit

I have the following problem retrieving data from MongoDB using mongoose.

Here is my Schema:


    const BookSchema = new Schema(
    	{
    		_id:Number,
    		title:String,
    		authors:[String],
    		subjects:[String]	
    	}
    );


as you can see i have 2 arrays embedded in the object, let&#39;s say the content of the authors can be something like this: authors:[&quot;Alex Ferguson&quot;, &quot;Didier Drogba&quot;, &quot;Cristiano Ronaldo&quot;, &quot;Alex&quot;]  
what I&#39;m trying to achieve is get all the Alex in the array.

So far, I&#39;ve been able to get the values if they match the value completely. However if I try to get the ones containing Alex the answer is always [].

What I want to know is how I can do this using find() without performing a map-reduce to create a view or a collection and then applying find() over that.

The code here works for exact matches


    Book.find( {authors:req.query.q} , function(errs, books){
    			if(errs){
    				res.send(errs);	
    			}
    			
    			res.json(books);
    		});

	

I tried some things but no luck 
{authors:{$elemMatch:req.query.q}}
{authors:{$in:[req.query.q]}}

This one gives me an error and on top of that says is very inefficient in another post I found here.
{$where:this.authors.indexOf(req.query.q) != -1}

and I also tried {authors:{$regex:&quot;./*value*/i&quot;}}

The map-reduce works fine, I need to make it work using the other approach to see which one is better?  

Any help is greatly appreciated. I&#39;m sure this is easy, but I&#39;m new with NodeJS and Mongo and I haven&#39;t been able to figure it out on my own.

How to get all the values that contains part of a string using mongoose find?

I am using Mongoose aggregation (MongoDB version 3.2).

I have a field `users` which is an array. I want to `$project` first item in this array to a new field `user`.

I tried 

      { $project: {
        user: &#39;$users[0]&#39;,
        otherField: 1
      }},

      { $project: {
        user: &#39;$users.0&#39;,
        otherField: 1
      }},

      { $project: {
        user: ｛ $first: &#39;$users&#39;},
        otherField: 1
      }},

But neither works.

How can I do it correctly? Thanks

Project first item in an array to new field (MongoDB aggregation)

I&#39;d like to get the names of all the keys in a MongoDB collection.

For example, from this:

    db.things.insert( { type : [&#39;dog&#39;, &#39;cat&#39;] } );
    db.things.insert( { egg : [&#39;cat&#39;] } );
    db.things.insert( { type : [] } );
    db.things.insert( { hello : []  } );

I&#39;d like to get the unique keys:

    type, egg, hello


Get names of all keys in the collection

I have some docs in mongo that looks something like this: 

    {
      _id : ObjectId(&quot;...&quot;),
      &quot;make&quot; : &quot;Nissan&quot;,
      ..
    },
    {
      _id : ObjectId(&quot;...&quot;),
      &quot;make&quot; : &quot;Nissan&quot;,
      &quot;saleDate&quot; :  ISODate(&quot;2013-04-10T12:39:50.676Z&quot;),
      ..
    }

Ideally, I&#39;d like to be able to count, by make, the number of vehicles sold per day.  I&#39;d then like to view either today, or a window such as today through the last seven days.

I was able to accomplish the daily view with some ugly code

    db.inventory.aggregate(
      { $match : { &quot;saleDate&quot; : { $gte: ISODate(&quot;2013-04-10T00:00:00.000Z&quot;), $lt: ISODate(&quot;2013-04-11T00:00:00.000Z&quot;)  } } } ,
      { $group : { _id : { make : &quot;$make&quot;, saleDayOfMonth : { $dayOfMonth : &quot;$saleDate&quot; } }, cnt : { $sum : 1 } } }
    )

Which then yields the results

    {
      &quot;result&quot; : [
        {
          &quot;_id&quot; : {
            &quot;make&quot; : &quot;Nissan&quot;,
            &quot;saleDayOfMonth&quot; : 10
          },
          &quot;cnt&quot; : 2
        },
        {
          &quot;_id&quot; : {
            &quot;make&quot; : &quot;Toyota&quot;,
            &quot;saleDayOfMonth&quot; : 10
          },
          &quot;cnt&quot; : 4
        },
      ],
      &quot;ok&quot; : 1
    }

So that is ok, but I would much prefer to not have to change the two datetime values in the query.  Then, as I mentioned above, I&#39;d like to be able to run this query (again, without having to modify it each time) and see the same results binned by day over the last week.

Oh and here is the sample data I&#39;ve been using for the query

    db.inventory.save({&quot;make&quot; : &quot;Nissan&quot;,&quot;saleDate&quot; :  ISODate(&quot;2013-04-10T12:39:50.676Z&quot;)});
    db.inventory.save({&quot;make&quot; : &quot;Nissan&quot;});
    db.inventory.save({&quot;make&quot; : &quot;Nissan&quot;,&quot;saleDate&quot; :  ISODate(&quot;2013-04-10T11:39:50.676Z&quot;)});
    db.inventory.save({&quot;make&quot; : &quot;Toyota&quot;,&quot;saleDate&quot; :  ISODate(&quot;2013-04-09T11:39:50.676Z&quot;)});
    db.inventory.save({&quot;make&quot; : &quot;Toyota&quot;,&quot;saleDate&quot; :  ISODate(&quot;2013-04-10T11:38:50.676Z&quot;)});
    db.inventory.save({&quot;make&quot; : &quot;Toyota&quot;,&quot;saleDate&quot; :  ISODate(&quot;2013-04-10T11:37:50.676Z&quot;)});
    db.inventory.save({&quot;make&quot; : &quot;Toyota&quot;,&quot;saleDate&quot; :  ISODate(&quot;2013-04-10T11:36:50.676Z&quot;)});
    db.inventory.save({&quot;make&quot; : &quot;Toyota&quot;,&quot;saleDate&quot; :  ISODate(&quot;2013-04-10T11:35:50.676Z&quot;)});

Thanks in advance,
Kevin


MongoDB aggregate within daily grouping

When I read the document I found the following notes:

&gt; When a $sort immediately precedes a $limit in the pipeline, the $sort operation only maintains the top n results as it progresses, where n is the specified limit, and MongoDB only needs to store n items in memory. This optimization still applies when allowDiskUse is true and the n items exceed the aggregation memory limit.

If I&#39;m right about this, it applies only when I use the $sort and $limit together like

    db.coll.aggregate([
        ...,
        {$sort: ...},
        {$limit: limit},
        ...
    ]);
However, I think most of the time we would have

    db.coll.aggregate([
        ...,
        {$sort: ...},
        {$skip: skip},
        {$limit: limit},
        ...
    ]);
**Question 1**: Does it mean the rule above doesn&#39;t apply if I use $skip here?

I ask this question because theoretically MongoDB can still  calculate the top **n** records and  enhance performance by sorting only top **n** records. I didn&#39;t find any document about this though. And if the rule doesn&#39;t apply,

**Question 2**: Do I need to change my query to the following to enhance performance?

    db.coll.aggregate([
        ...,
        {$sort: ...},
        {$limit: skip + limit},
        {$skip: skip},
        {$limit: limit},
        ...
    ]);

**EDIT**: I think explains my use case would make the question above makes more sense. I&#39;m using the text search feature provided by MongoDB 2.6 to look for products. I&#39;m worried if the user inputs a very common key word like &quot;red&quot;, there will be too many results returned. Thus I&#39;m looking for better ways to generate this result.

**EDIT2**: It turns out that the last code above equals to 

    db.coll.aggregate([
        ...,
        {$sort: ...},
        {$limit: skip + limit},
        {$skip: skip},
        ...
    ]);
Thus I we can always use this form to make the **top n** rule apply.

$skip and $limit in aggregation framework

I need to retrieve the entire single object hierarchy from the database as a JSON. Actually, the proposal about any other solution to achieve this result would be highly appreciated. I decided to use MongoDB with its `$lookup` support.

So I have three collections:

**party**

    { &quot;_id&quot; : &quot;2&quot;, &quot;name&quot; : &quot;party2&quot; }
    { &quot;_id&quot; : &quot;5&quot;, &quot;name&quot; : &quot;party5&quot; }
    { &quot;_id&quot; : &quot;4&quot;, &quot;name&quot; : &quot;party4&quot; }
    { &quot;_id&quot; : &quot;1&quot;, &quot;name&quot; : &quot;party1&quot; }
    { &quot;_id&quot; : &quot;3&quot;, &quot;name&quot; : &quot;party3&quot; }    
**address**

    { &quot;_id&quot; : &quot;a3&quot;, &quot;street&quot; : &quot;Address3&quot;, &quot;party_id&quot; : &quot;2&quot; }
    { &quot;_id&quot; : &quot;a6&quot;, &quot;street&quot; : &quot;Address6&quot;, &quot;party_id&quot; : &quot;5&quot; }
    { &quot;_id&quot; : &quot;a1&quot;, &quot;street&quot; : &quot;Address1&quot;, &quot;party_id&quot; : &quot;1&quot; }
    { &quot;_id&quot; : &quot;a5&quot;, &quot;street&quot; : &quot;Address5&quot;, &quot;party_id&quot; : &quot;5&quot; }
    { &quot;_id&quot; : &quot;a2&quot;, &quot;street&quot; : &quot;Address2&quot;, &quot;party_id&quot; : &quot;1&quot; }
    { &quot;_id&quot; : &quot;a4&quot;, &quot;street&quot; : &quot;Address4&quot;, &quot;party_id&quot; : &quot;3&quot; }
**addressComment**

    { &quot;_id&quot; : &quot;ac2&quot;, &quot;address_id&quot; : &quot;a1&quot;, &quot;comment&quot; : &quot;Comment2&quot; }
    { &quot;_id&quot; : &quot;ac1&quot;, &quot;address_id&quot; : &quot;a1&quot;, &quot;comment&quot; : &quot;Comment1&quot; }
    { &quot;_id&quot; : &quot;ac5&quot;, &quot;address_id&quot; : &quot;a5&quot;, &quot;comment&quot; : &quot;Comment6&quot; }
    { &quot;_id&quot; : &quot;ac4&quot;, &quot;address_id&quot; : &quot;a3&quot;, &quot;comment&quot; : &quot;Comment4&quot; }
    { &quot;_id&quot; : &quot;ac3&quot;, &quot;address_id&quot; : &quot;a2&quot;, &quot;comment&quot; : &quot;Comment3&quot; }

I need to retrieve all parties with all corresponding addresses and address comments as part of the record. My aggregation:

    db.party.aggregate([{
    	$lookup: {
    		from: &quot;address&quot;,
    		localField: &quot;_id&quot;,
    		foreignField: &quot;party_id&quot;,
    		as: &quot;address&quot;
    	}
    },
    {
    	$unwind: &quot;$address&quot;
    },
    {
    	$lookup: {
    		from: &quot;addressComment&quot;,
    		localField: &quot;address._id&quot;,
    		foreignField: &quot;address_id&quot;,
    		as: &quot;address.addressComment&quot;
    	}
    }])

The result is pretty weird. Some records are ok. But Party with `_id: 4` is missing (there is no address for it). Also, there are two Party `_id: 1` in the result set (but with different addresses):

    {
    	&quot;_id&quot;: &quot;1&quot;,
    	&quot;name&quot;: &quot;party1&quot;,
    	&quot;address&quot;: {
    		&quot;_id&quot;: &quot;2&quot;,
    		&quot;street&quot;: &quot;Address2&quot;,
    		&quot;party_id&quot;: &quot;1&quot;,
    		&quot;addressComment&quot;: [{
    			&quot;_id&quot;: &quot;3&quot;,
    			&quot;address_id&quot;: &quot;2&quot;,
    			&quot;comment&quot;: &quot;Comment3&quot;
    		}]
    	}
    }{
    	&quot;_id&quot;: &quot;1&quot;,
    	&quot;name&quot;: &quot;party1&quot;,
    	&quot;address&quot;: {
    		&quot;_id&quot;: &quot;1&quot;,
    		&quot;street&quot;: &quot;Address1&quot;,
    		&quot;party_id&quot;: &quot;1&quot;,
    		&quot;addressComment&quot;: [{
    			&quot;_id&quot;: &quot;1&quot;,
    			&quot;address_id&quot;: &quot;1&quot;,
    			&quot;comment&quot;: &quot;Comment1&quot;
    		},
    		{
    			&quot;_id&quot;: &quot;2&quot;,
    			&quot;address_id&quot;: &quot;1&quot;,
    			&quot;comment&quot;: &quot;Comment2&quot;
    		}]
    	}
    }{
    	&quot;_id&quot;: &quot;3&quot;,
    	&quot;name&quot;: &quot;party3&quot;,
    	&quot;address&quot;: {
    		&quot;_id&quot;: &quot;4&quot;,
    		&quot;street&quot;: &quot;Address4&quot;,
    		&quot;party_id&quot;: &quot;3&quot;,
    		&quot;addressComment&quot;: []
    	}
    }{
    	&quot;_id&quot;: &quot;5&quot;,
    	&quot;name&quot;: &quot;party5&quot;,
    	&quot;address&quot;: {
    		&quot;_id&quot;: &quot;5&quot;,
    		&quot;street&quot;: &quot;Address5&quot;,
    		&quot;party_id&quot;: &quot;5&quot;,
    		&quot;addressComment&quot;: [{
    			&quot;_id&quot;: &quot;5&quot;,
    			&quot;address_id&quot;: &quot;5&quot;,
    			&quot;comment&quot;: &quot;Comment5&quot;
    		}]
    	}
    }{
    	&quot;_id&quot;: &quot;2&quot;,
    	&quot;name&quot;: &quot;party2&quot;,
    	&quot;address&quot;: {
    		&quot;_id&quot;: &quot;3&quot;,
    		&quot;street&quot;: &quot;Address3&quot;,
    		&quot;party_id&quot;: &quot;2&quot;,
    		&quot;addressComment&quot;: [{
    			&quot;_id&quot;: &quot;4&quot;,
    			&quot;address_id&quot;: &quot;3&quot;,
    			&quot;comment&quot;: &quot;Comment4&quot;
    		}]
    	}
    }
Please help me with this. I&#39;m pretty new to MongoDB but I feel it can do what I need from it.

MongoDB nested lookup with 3 levels

My collection in mongodb is similar to the following table in SQL:

Sentiments(Company,Sentiment)

Now, I need to execute a query like this:

    SELECT
      Company, 
      SUM(CASE WHEN Sentiment &gt;0 THEN Sentiment ELSE 0 END) AS SumPosSenti, 
      SUM(CASE WHEN Sentiment &lt;0 THEN Sentiment ELSE 0 END) AS SumNegSenti
    FROM Sentiments
    GROUP BY Company
    
What should I do to write this query in Mongo? I am stuck at the following query:

    db.Sentiments.aggregate(
    { $project: {_id:0, Company:1, Sentiment: 1} },
    { $group: {_id: &quot;$Company&quot;, SumPosSenti: {$sum: ? }, SumNegSenti: {$sum: ? } } }
    );

 

Conditional $sum in MongoDB

In mongo, after doing an `aggregation` with `$lookup`, I would like the request to return only some fields and not the whole document.

I have the following query :

    db.somecollection.aggregate([{
        $lookup: {
            from: &quot;campaigns&quot;,
            localField: &quot;campId&quot;,
            foreignField: &quot;_id&quot;,
            as: &quot;campaign&quot;
        }
    }, {
        $unwind: &quot;$campaign&quot;
    }, {
        $lookup: {
            from: &quot;entities&quot;,
            localField: &quot;campaign.clientid&quot;,
            foreignField: &quot;_id&quot;,
            as: &quot;campaign.client&quot;
        }
    }]);

This request will return me this :

    {
	&quot;_id&quot; : ObjectId(&quot;56cc7cd1cc2cf62803ebfdc7&quot;),
	&quot;campId&quot; : ObjectId(&quot;56c740e4479f46e402efda84&quot;),
	&quot;articleId&quot; : ObjectId(&quot;56c742c06094640103ba3843&quot;),
	&quot;campaign&quot; : {
		&quot;_id&quot; : ObjectId(&quot;56c740e4479f46e402efda84&quot;),
		&quot;clientid&quot; : ObjectId(&quot;56c740b8479f46e402efda83&quot;),
		&quot;client&quot; : [
			{
				&quot;_id&quot; : ObjectId(&quot;56c740b8479f46e402efda83&quot;),
				&quot;username&quot; : &quot;someusername&quot;,
				&quot;shhh&quot; : &quot;somehashedpassword&quot;,
				&quot;email&quot; : &quot;mail@mail.com&quot;,
			}
		]
	}

The request works well, but I would like to filter the fields in `campaign.client` to only get for example `_id` and `username`. Is there a way to do this in a MongoDB `aggregate` request?



MongoDB aggregation with $lookup only include (or project) some fields to return from query

Suppose I have a collection with some set of documents. something like this.

    { &quot;_id&quot; : ObjectId(&quot;4f127fa55e7242718200002d&quot;), &quot;id&quot;:1, &quot;name&quot; : &quot;foo&quot;}
    { &quot;_id&quot; : ObjectId(&quot;4f127fa55e7242718200002d&quot;), &quot;id&quot;:2, &quot;name&quot; : &quot;bar&quot;}
    { &quot;_id&quot; : ObjectId(&quot;4f127fa55e7242718200002d&quot;), &quot;id&quot;:3, &quot;name&quot; : &quot;baz&quot;}
    { &quot;_id&quot; : ObjectId(&quot;4f127fa55e7242718200002d&quot;), &quot;id&quot;:4, &quot;name&quot; : &quot;foo&quot;}
    { &quot;_id&quot; : ObjectId(&quot;4f127fa55e7242718200002d&quot;), &quot;id&quot;:5, &quot;name&quot; : &quot;bar&quot;}
    { &quot;_id&quot; : ObjectId(&quot;4f127fa55e7242718200002d&quot;), &quot;id&quot;:6, &quot;name&quot; : &quot;bar&quot;}

I want to find all the duplicated entries in this collection by the &quot;name&quot; field. E.g. &quot;foo&quot; appears twice and &quot;bar&quot; appears 3 times.

Find all duplicate documents in a MongoDB collection by a key field

Is it possible to rename the name of fields returned in a find query? I would like to use something like `$rename`, however I wouldn&#39;t like to change the documents I&#39;m accessing. I want just to retrieve them differently, something that works like `SELECT COORINATES AS COORDS` in SQL.
 
What I do now:

    db.tweets.findOne({}, {&#39;level1.level2.coordinates&#39;: 1, _id:0})
    {&#39;level1&#39;: {&#39;level2&#39;: {&#39;coordinates&#39;: [10, 20]}}}

What I would like to be returned is:
`{&#39;coords&#39;: [10, 20]}`

All Aggregation Framework Solutions on Gang of Coders

Total of 38 Aggregation Framework Solutions