By jschr


2012-07-03 01:19:42 8 Comments

I'm pretty new to Mongoose and MongoDB in general so I'm having a difficult time figuring out if something like this is possible:

Item = new Schema({
    id: Schema.ObjectId,
    dateCreated: { type: Date, default: Date.now },
    title: { type: String, default: 'No Title' },
    description: { type: String, default: 'No Description' },
    tags: [ { type: Schema.ObjectId, ref: 'ItemTag' }]
});

ItemTag = new Schema({
    id: Schema.ObjectId,
    tagId: { type: Schema.ObjectId, ref: 'Tag' },
    tagName: { type: String }
});



var query = Models.Item.find({});

query
    .desc('dateCreated')
    .populate('tags')
    .where('tags.tagName').in(['funny', 'politics'])
    .run(function(err, docs){
       // docs is always empty
    });

Is there a better way do this?

Edit

Apologies for any confusion. What I'm trying to do is get all Items that contain either the funny tag or politics tag.

Edit

Document without where clause:

[{ 
    _id: 4fe90264e5caa33f04000012,
    dislikes: 0,
    likes: 0,
    source: '/uploads/loldog.jpg',
    comments: [],
    tags: [{
        itemId: 4fe90264e5caa33f04000012,
        tagName: 'movies',
        tagId: 4fe64219007e20e644000007,
        _id: 4fe90270e5caa33f04000015,
        dateCreated: Tue, 26 Jun 2012 00:29:36 GMT,
        rating: 0,
        dislikes: 0,
        likes: 0 
    },
    { 
        itemId: 4fe90264e5caa33f04000012,
        tagName: 'funny',
        tagId: 4fe64219007e20e644000002,
        _id: 4fe90270e5caa33f04000017,
        dateCreated: Tue, 26 Jun 2012 00:29:36 GMT,
        rating: 0,
        dislikes: 0,
        likes: 0 
    }],
    viewCount: 0,
    rating: 0,
    type: 'image',
    description: null,
    title: 'dogggg',
    dateCreated: Tue, 26 Jun 2012 00:29:24 GMT 
 }, ... ]

With the where clause, I get an empty array.

6 comments

@Neil Lunn 2017-06-22 00:51:24

With a modern MongoDB greater than 3.2 you can use $lookup as an alternate to .populate() in most cases. This also has the advantage of actually doing the join "on the server" as opposed to what .populate() does which is actually "multiple queries" to "emulate" a join.

So .populate() is not really a "join" in the sense of how a relational database does it. The $lookup operator on the other hand, actually does the work on the server, and is more or less analogous to a "LEFT JOIN":

Item.aggregate(
  [
    { "$lookup": {
      "from": ItemTags.collection.name,
      "localField": "tags",
      "foreignField": "_id",
      "as": "tags"
    }},
    { "$unwind": "$tags" },
    { "$match": { "tags.tagName": { "$in": [ "funny", "politics" ] } } },
    { "$group": {
      "_id": "$_id",
      "dateCreated": { "$first": "$dateCreated" },
      "title": { "$first": "$title" },
      "description": { "$first": "$description" },
      "tags": { "$push": "$tags" }
    }}
  ],
  function(err, result) {
    // "tags" is now filtered by condition and "joined"
  }
)

N.B. The .collection.name here actually evaluates to the "string" that is the actual name of the MongoDB collection as assigned to the model. Since mongoose "pluralizes" collection names by default and $lookup needs the actual MongoDB collection name as an argument ( since it's a server operation ), then this is a handy trick to use in mongoose code, as opposed to "hard coding" the collection name directly.

Whilst we could also use $filter on arrays to remove the unwanted items, this is actually the most efficient form due to Aggregation Pipeline Optimization for the special condition of as $lookup followed by both an $unwind and a $match condition.

This actually results in the three pipeline stages being rolled into one:

   { "$lookup" : {
     "from" : "itemtags",
     "as" : "tags",
     "localField" : "tags",
     "foreignField" : "_id",
     "unwinding" : {
       "preserveNullAndEmptyArrays" : false
     },
     "matching" : {
       "tagName" : {
         "$in" : [
           "funny",
           "politics"
         ]
       }
     }
   }}

This is highly optimal as the actual operation "filters the collection to join first", then it returns the results and "unwinds" the array. Both methods are employed so the results do not break the BSON limit of 16MB, which is a constraint that the client does not have.

The only problem is that it seems "counter-intuitive" in some ways, particularly when you want the results in an array, but that is what the $group is for here, as it reconstructs to the original document form.

It's also unfortunate that we simply cannot at this time actually write $lookup in the same eventual syntax the server uses. IMHO, this is an oversight to be corrected. But for now, simply using the sequence will work and is the most viable option with the best performance and scalability.

Addendum - MongoDB 3.6 and upwards

Though the pattern shown here is fairly optimized due to how the other stages get rolled into the $lookup, it does have one failing in that the "LEFT JOIN" which is normally inherent to both $lookup and the actions of populate() is negated by the "optimal" usage of $unwind here which does not preserve empty arrays. You can add the preserveNullAndEmptyArrays option, but this negates the "optimized" sequence described above and essentially leaves all three stages intact which would normally be combined in the optimization.

MongoDB 3.6 expands with a "more expressive" form of $lookup allowing a "sub-pipeline" expression. Which not only meets the goal of retaining the "LEFT JOIN" but still allows an optimal query to reduce results returned and with a much simplified syntax:

Item.aggregate([
  { "$lookup": {
    "from": ItemTags.collection.name,
    "let": { "tags": "$tags" },
    "pipeline": [
      { "$match": {
        "tags": { "$in": [ "politics", "funny" ] },
        "$expr": { "$in": [ "$_id", "$$tags" ] }
      }}
    ]
  }}
])

The $expr used in order to match the declared "local" value with the "foreign" value is actually what MongoDB does "internally" now with the original $lookup syntax. By expressing in this form we can tailor the initial $match expression within the "sub-pipeline" ourselves.

In fact, as a true "aggregation pipeline" you can do just about anything you can do with an aggregation pipeline within this "sub-pipeline" expression, including "nesting" the levels of $lookup to other related collections.

Further usage is a bit beyond the scope of what the question here asks, but in relation to even "nested population" then the new usage pattern of $lookup allows this to be much the same, and a "lot" more powerful in it's full usage.


Working Example

The following gives an example using a static method on the model. Once that static method is implemented the call simply becomes:

  Item.lookup(
    {
      path: 'tags',
      query: { 'tags.tagName' : { '$in': [ 'funny', 'politics' ] } }
    },
    callback
  )

Or enhancing to be a bit more modern even becomes:

  let results = await Item.lookup({
    path: 'tags',
    query: { 'tagName' : { '$in': [ 'funny', 'politics' ] } }
  })

Making it very similar to .populate() in structure, but it's actually doing the join on the server instead. For completeness, the usage here casts the returned data back to mongoose document instances at according to both the parent and child cases.

It's fairly trivial and easy to adapt or just use as is for most common cases.

N.B The use of async here is just for brevity of running the enclosed example. The actual implementation is free of this dependency.

const async = require('async'),
      mongoose = require('mongoose'),
      Schema = mongoose.Schema;

mongoose.Promise = global.Promise;
mongoose.set('debug', true);
mongoose.connect('mongodb://localhost/looktest');

const itemTagSchema = new Schema({
  tagName: String
});

const itemSchema = new Schema({
  dateCreated: { type: Date, default: Date.now },
  title: String,
  description: String,
  tags: [{ type: Schema.Types.ObjectId, ref: 'ItemTag' }]
});

itemSchema.statics.lookup = function(opt,callback) {
  let rel =
    mongoose.model(this.schema.path(opt.path).caster.options.ref);

  let group = { "$group": { } };
  this.schema.eachPath(p =>
    group.$group[p] = (p === "_id") ? "$_id" :
      (p === opt.path) ? { "$push": `$${p}` } : { "$first": `$${p}` });

  let pipeline = [
    { "$lookup": {
      "from": rel.collection.name,
      "as": opt.path,
      "localField": opt.path,
      "foreignField": "_id"
    }},
    { "$unwind": `$${opt.path}` },
    { "$match": opt.query },
    group
  ];

  this.aggregate(pipeline,(err,result) => {
    if (err) callback(err);
    result = result.map(m => {
      m[opt.path] = m[opt.path].map(r => rel(r));
      return this(m);
    });
    callback(err,result);
  });
}

const Item = mongoose.model('Item', itemSchema);
const ItemTag = mongoose.model('ItemTag', itemTagSchema);

function log(body) {
  console.log(JSON.stringify(body, undefined, 2))
}
async.series(
  [
    // Clean data
    (callback) => async.each(mongoose.models,(model,callback) =>
      model.remove({},callback),callback),

    // Create tags and items
    (callback) =>
      async.waterfall(
        [
          (callback) =>
            ItemTag.create([{ "tagName": "movies" }, { "tagName": "funny" }],
              callback),

          (tags, callback) =>
            Item.create({ "title": "Something","description": "An item",
              "tags": tags },callback)
        ],
        callback
      ),

    // Query with our static
    (callback) =>
      Item.lookup(
        {
          path: 'tags',
          query: { 'tags.tagName' : { '$in': [ 'funny', 'politics' ] } }
        },
        callback
      )
  ],
  (err,results) => {
    if (err) throw err;
    let result = results.pop();
    log(result);
    mongoose.disconnect();
  }
)

Or a little more modern for Node 8.x and above with async/await and no additional dependencies:

const { Schema } = mongoose = require('mongoose');
const uri = 'mongodb://localhost/looktest';

mongoose.Promise = global.Promise;
mongoose.set('debug', true);

const itemTagSchema = new Schema({
  tagName: String
});

const itemSchema = new Schema({
  dateCreated: { type: Date, default: Date.now },
  title: String,
  description: String,
  tags: [{ type: Schema.Types.ObjectId, ref: 'ItemTag' }]
});

itemSchema.statics.lookup = function(opt) {
  let rel =
    mongoose.model(this.schema.path(opt.path).caster.options.ref);

  let group = { "$group": { } };
  this.schema.eachPath(p =>
    group.$group[p] = (p === "_id") ? "$_id" :
      (p === opt.path) ? { "$push": `$${p}` } : { "$first": `$${p}` });

  let pipeline = [
    { "$lookup": {
      "from": rel.collection.name,
      "as": opt.path,
      "localField": opt.path,
      "foreignField": "_id"
    }},
    { "$unwind": `$${opt.path}` },
    { "$match": opt.query },
    group
  ];

  return this.aggregate(pipeline).exec().then(r => r.map(m => 
    this({ ...m, [opt.path]: m[opt.path].map(r => rel(r)) })
  ));
}

const Item = mongoose.model('Item', itemSchema);
const ItemTag = mongoose.model('ItemTag', itemTagSchema);

const log = body => console.log(JSON.stringify(body, undefined, 2));

(async function() {
  try {

    const conn = await mongoose.connect(uri);

    // Clean data
    await Promise.all(Object.entries(conn.models).map(([k,m]) => m.remove()));

    // Create tags and items
    const tags = await ItemTag.create(
      ["movies", "funny"].map(tagName =>({ tagName }))
    );
    const item = await Item.create({ 
      "title": "Something",
      "description": "An item",
      tags 
    });

    // Query with our static
    const result = (await Item.lookup({
      path: 'tags',
      query: { 'tags.tagName' : { '$in': [ 'funny', 'politics' ] } }
    })).pop();
    log(result);

    mongoose.disconnect();

  } catch (e) {
    console.error(e);
  } finally {
    process.exit()
  }
})()

And from MongoDB 3.6 and upward, even without the $unwind and $group building:

const { Schema, Types: { ObjectId } } = mongoose = require('mongoose');

const uri = 'mongodb://localhost/looktest';

mongoose.Promise = global.Promise;
mongoose.set('debug', true);

const itemTagSchema = new Schema({
  tagName: String
});

const itemSchema = new Schema({
  title: String,
  description: String,
  tags: [{ type: Schema.Types.ObjectId, ref: 'ItemTag' }]
},{ timestamps: true });

itemSchema.statics.lookup = function({ path, query }) {
  let rel =
    mongoose.model(this.schema.path(path).caster.options.ref);

  // MongoDB 3.6 and up $lookup with sub-pipeline
  let pipeline = [
    { "$lookup": {
      "from": rel.collection.name,
      "as": path,
      "let": { [path]: `$${path}` },
      "pipeline": [
        { "$match": {
          ...query,
          "$expr": { "$in": [ "$_id", `$$${path}` ] }
        }}
      ]
    }}
  ];

  return this.aggregate(pipeline).exec().then(r => r.map(m =>
    this({ ...m, [path]: m[path].map(r => rel(r)) })
  ));
};

const Item = mongoose.model('Item', itemSchema);
const ItemTag = mongoose.model('ItemTag', itemTagSchema);

const log = body => console.log(JSON.stringify(body, undefined, 2));

(async function() {

  try {

    const conn = await mongoose.connect(uri);

    // Clean data
    await Promise.all(Object.entries(conn.models).map(([k,m]) => m.remove()));

    // Create tags and items
    const tags = await ItemTag.insertMany(
      ["movies", "funny"].map(tagName => ({ tagName }))
    );

    const item = await Item.create({
      "title": "Something",
      "description": "An item",
      tags
    });

    // Query with our static
    let result = (await Item.lookup({
      path: 'tags',
      query: { 'tagName': { '$in': [ 'funny', 'politics' ] } }
    })).pop();
    log(result);


    await mongoose.disconnect();

  } catch(e) {
    console.error(e)
  } finally {
    process.exit()
  }

})()

@jschr 2018-05-12 02:27:03

I'm no longer using Mongo / Mongoose but I've accepted your answer since this is a popular question and looks like this has been helpful to others. Glad to see this problem has a more scalable solution now. Thanks for providing an updated answer.

@Fabian 2016-09-16 18:26:55

Update: Please take a look at the comments - this answer does not correctly match to the question, but maybe it answers other questions of users which came across (I think that because of the upvotes) so I will not delete this "answer":

First: I know this question is really outdated, but I searched for exactly this problem and this SO post was the Google entry #1. So I implemented the docs.filter version (accepted answer) but as I read in the mongoose v4.6.0 docs we can now simply use:

Item.find({}).populate({
    path: 'tags',
    match: { tagName: { $in: ['funny', 'politics'] }}
}).exec((err, items) => {
  console.log(items.tags) 
  // contains only tags where tagName is 'funny' or 'politics'
})

Hope this helps future search machine users.

@OllyBarca 2016-12-01 00:10:10

But this will only filter the items.tags array surely? Items will be returned regardless of the tagName...

@andreimarinescu 2016-12-07 11:52:06

That is correct, @OllyBarca. According to the docs, match affects the population query only.

@Z.Alpha 2016-12-16 07:27:12

I think this doesn't answer the question

@Fabian 2017-01-12 20:44:57

Hm ... Ok but then there is an error in the docs -> mongoosejs.com/docs/populate.html .. If you scroll down to "Query conditions and other options" you'll see that the match condition "age: { $gte: 21 } " is related to the populated "fans" array (ref to person) of the stories returned by the previous find(...).

@EnKrypt 2018-05-08 15:15:44

@Fabian that is not an error. Only the population query (in this case fans) gets filtered. The actual document returned (which is Story, contains fans as a property) is not affected or filtered.

@EnKrypt 2018-05-08 15:17:38

This answer is thus not correct, for the reasons mentioned in the comments. Anyone looking at this in the future should be careful.

@Fabian 2018-05-08 17:07:03

@EnKrypt I added an Update to preevent a false future usage. Thx for the explanation!

@HernanFila 2017-09-27 21:47:01

@aaronheckmann 's answer worked for me but I had to replace return doc.tags.length; to return doc.tags != null; because that field contain null if it doesn't match with the conditions written inside populate. So the final code:

query....
.exec(function(err, docs){
   docs = docs.filter(function(doc){
     return doc.tags != null;
   })
   // do stuff with docs
});

@OllyBarca 2016-12-07 20:43:48

After having the same problem myself recently, I've come up with the following solution:

First, find all ItemTags where tagName is either 'funny' or 'politics' and return an array of ItemTag _ids.

Then, find Items which contain all ItemTag _ids in the tags array

ItemTag
  .find({ tagName : { $in : ['funny','politics'] } })
  .lean()
  .distinct('_id')
  .exec((err, itemTagIds) => {
     if (err) { console.error(err); }
     Item.find({ tag: { $all: itemTagIds} }, (err, items) => {
        console.log(items); // Items filtered by tagName
     });
  });

@Dragos Lupei 2019-07-15 14:56:32

How i did it const tagsIds = await this.tagModel .find({ name: { $in: tags } }) .lean() .distinct('_id'); return this.adviceModel.find({ tags: { $all: tagsIds } });

@aaronheckmann 2012-07-10 17:16:46

what you are asking for isn't directly supported but can be achieved by adding another filter step after the query returns.

first, .populate( 'tags', null, { tagName: { $in: ['funny', 'politics'] } } ) is definitely what you need to do to filter the tags documents. then, after the query returns you'll need to manually filter out documents that don't have any tags docs that matched the populate criteria. something like:

query....
.exec(function(err, docs){
   docs = docs.filter(function(doc){
     return doc.tags.length;
   })
   // do stuff with docs
});

@jschr 2012-07-10 18:35:07

Hey Aaron, thanks for the reply. I may be wrong but won't the $in on populate() only populate the matched tags? So any additional tags on the item will get filtered out. It sounds like I'll have to populate all items and have the second filter step reduce it based on tag name then.

@Aqib Mumtaz 2015-11-11 08:58:03

@aaronheckmann I have implemented your suggested solution, you are right about to do filter after .exec, because though populate query is populating only required objects but still its returning entire data set. Do you think in Newer version of Mongoose there is some option to return only populated data set so we don't need to go for another filtering?

@Aqib Mumtaz 2015-11-11 09:04:49

Im also curious to know about the performance, If query is returning entire dataset at the end then there is no purpose of going for population filtering? What do you say? Im adapting population query for performance optimisation but this way performance wont get better for large dataset?

@samazi 2016-03-07 03:51:55

mongoosejs.com/docs/api.html#query_Query-populate has all the details if anyone else is interested

@nicogaldo 2018-07-26 22:09:52

how match in different fields when populated ?

@Aafreen Sheikh 2012-07-03 06:57:59

Try replacing

.populate('tags').where('tags.tagName').in(['funny', 'politics']) 

by

.populate( 'tags', null, { tagName: { $in: ['funny', 'politics'] } } )

@jschr 2012-07-03 13:27:01

Thanks for the reply. I believe what this does is only populate each item with funny or politics, which wouldn't reduce the parent list. What I would actually like is only items that have funny or politics in their tag.

@Aafreen Sheikh 2012-07-03 14:13:51

Can you show how your document looks like? Coz a 'where' inside the tags array seems like a valid operation to me..Are we just getting the syntax wrong..Have you tried removing that 'where' clause completely and checked if anything is returned? Alternatively, just to test if writing 'tags.tagName' is syntactically ok, you might forget the ref thing for a while and try out your query with an embedded array inside the 'Item' document.

@jschr 2012-07-03 22:59:21

Edited my original post with the document. I was able to test it with the model as an embedded array inside Item with success but unfortunately I require it to be a DBRef as ItemTag is frequently updated. Thanks again for the help.

@Tigin 2018-11-27 17:00:49

Thank you its working and it is very useful.

Related Questions

Sponsored Content

11 Answered Questions

[SOLVED] Populate nested array in mongoose

26 Answered Questions

[SOLVED] How to get GET (query string) variables in Express.js on Node.js?

22 Answered Questions

[SOLVED] How do I update/upsert a document in Mongoose?

34 Answered Questions

[SOLVED] How to query MongoDB with "like"?

3 Answered Questions

[SOLVED] What is the "__v" field in Mongoose

9 Answered Questions

[SOLVED] Mongoose: findOneAndUpdate doesn't return updated document

24 Answered Questions

[SOLVED] How to paginate with Mongoose in Node.js?

10 Answered Questions

[SOLVED] Mongoose populate after save

2 Answered Questions

[SOLVED] Mongoose sort by populated field

1 Answered Questions

How to achieve DBRef on virtual

  • 2012-02-22 06:46:53
  • Samal
  • 197 View
  • 1 Score
  • 1 Answer
  • Tags:   node.js mongoose

Sponsored Content