MongoDB query criterias and projections

Question

I'm bulding an app using Node/Express/MongoDB/Mongoskin.

I the database I have a collection namned "clients", where documents containing information about client account. Each client document contains an array that contains invoice-objects, like this:

doc.invoices = [
  {year: 2012,
  quarter: 1, 
  daily: 912.00, 
  sms: 42.00, 
  paid: true},

  {year: 2012,
  quarter: 2, 
  daily: 913.00, 
  sms: 55.00, 
  paid: true}, 

  {year: 2012,
  quarter: 3, 
  daily: 876.00, 
  sms: 82.00, 
  paid: true}, 

  {year: 2012,
  quarter: 4, 
  daily: 903.00, 
  sms: 93.00, 
  paid: false},

  {year: 2013,
  quarter: 1, 
  daily: 915.00, 
  sms: 67.00, 
  paid: true},

  {year: 2013,
  quarter: 2, 
  daily: 920.00, 
  sms: 35.00, 
  paid: true}, 

  {year: 2013,
  quarter: 3, 
  daily: 880.00, 
  sms: 92.00, 
  paid: true}, 

  {year: 2013,
  quarter: 4, 
  daily: 900.00, 
  sms: 85.00, 
  paid: false}
]

Question: Lets say i want to query ALL documents from this collection, like in a Superadmin-view showing all clients, but i want to limit the information returned from the invoice-array to objects where "year" is equal to a certain value, for example 2013, current year.

I guess projections is what I need, but the problem is that the projection is only returning the first result it finds...

Answer 1

Remember that projections allows us to explicitly include or exclude fields in a MongoDB query. We use 1 to indicate that we want to include a field and 0 to indicate that we wish to exclude the field. Remember that _id field is special -

• _id field gets included by default, unless we explicitly exclude it
• All other fields are excluded, until we explicitly include them

Also, since we're working in javascript, we can construct our project documents and documents to be inserted into our collections in a way that is very similar to the way we do this in the mongo shell. What's different is the driver provides one set of classes and methods we use to interact with MongoDB and the mongo shell provides it's own API.

W. r. t. CRUD as of MongoDB 3.2 the driver and the mongo shell adhere to the same spec. How you access these methods and how they're implemented varies of course, from the mongo shell.

var MongoClient = require('mongodb').MongoClient,
    assert = require('assert');


MongoClient.connect('mongodb://localhost:27017/crunchbase', function(err, db) {

    assert.equal(err, null);
    console.log("Successfully connected to MongoDB.");

    var query = {"category_code": "biotech"};
    var projection = {"name": 1, "category_code": 1, "_id": 0};

    var cursor = db.collection('companies').find(query);
    cursor.project(projection);

    cursor.forEach(
        function(doc) {
            console.log(doc.name + " is a " + doc.category_code + " company.");
            console.log(doc);
        },
        function(err) {
            assert.equal(err, null);
            return db.close();
        }
    );

});

The current best practice in the node.js driver, is to chain a call to project onto our cursor i.e. cursor.project. This call to project sets a field projection for the query. This call does not force a request to retrieve documents from the database, as does the foreach method. Rather it adds some additional details to the query representation maintained by our cursor. There're a number of cursor methods, we can chain together to fully express the operation we wish to execute against our MongoDB database. The call to db.collection is synchronous. We're going to modify that cursor with a field projection here using the project method on the cursor.

Answer 2

first of all, projection does not return the first result it finds it tells mongo what to return.

.findOne(query) will LIMIT result to one or find(query).limit(1) will also do the same.

You say you are trying to "get all" your data.

a standard find type query will get your started...

find({mongo:query},{mongo:projection})

but limits results to a cursor from the mongo shell*

since you said your using express.js you can use a javascript function to "return all" the results of a simple find like this one...

db.sales.find({"year":2013},{_id:0,"year":1,"quarter":1,"daily":1,"sms":1,"paid":1}) 

by first setting up a route to a function

app.get('/sales/yr/:yr', sales.getAllResults);

and then a function to handle your simple mongo query and projection.

/**
 *  get ALL results given YEAR ONLY
 *  extend jsonQuery to modify results
 *  extend or reduce jsonProjection to control data returned
 */
exports.getAllResults= function(req, res) {
    var yr = req.params.yr ;    
    var jsonQuery = {"year":yr} ;  //add or remove comma seperated "key":values given your JSON collection
    var jsonProjection = {_id:0,"year":1,"quarter":1,"daily":1,"sms":1,"paid":1} ; //leave year out since that's specified in the query anyhow
    var jsort = {"some-thing-else":-1} ; //-1 descending or 1 ascending
    db.collection("YOUR-COLLECTION_NAME", function(err, collection) {
        collection.find( jsonQuery, jsonProjection).sort(jsort).toArray( function(err, items) {
            res.send(items);
        });
    });
}

finally you might want to follow a tutorial for express and mongo there are some good ones, I put this answer together based on an excellent starter from Chris Coenraets see:-

http://coenraets.org/blog/2012/10/creating-a-rest-api-using-node-js-express-and-mongodb/

• with a standard mongo query shell you are limited to paged result set by default, where typing "it" will iterate through the results.

Answer 3

As @JohnnyK mentioned in his comment, you should use MongoDB aggregation framework:

db.clients.aggregate([
  { $unwind: "$invoices" },
  { $match: { "invoices.year": 2013 } },
  { $group: { _id: "$_id", /* other client fields */, invoices: { $push: "$invoices" } } }
]);

At-first you unwind initial invoices array, then keep only invoices with specified year and group matched invoices into array by client fields in the end.

Answer 4

not sure if i understood :

find({year:2013},function(err,clients){})

this returns you all clients that year equals 2013, it returns you all document field

The projection you're talking about is used to limit fields returned by the query, i mean :

find({year:2013},{year:1,paid:1},function(err,clients){})

this returns you an array of objects where year is 2013 AND each object gets 2 fields (paid and year)

the "{year:1,paid:1}" object is your projector.