Find all subdocuments which meet a criteria

Question

I would like to get only those subdocuments from my two object arrays file_history and uploaded_files which meet a criteira. Even though I've found this stackoverflow answer #2 (Mongoose - finding subdocuments by criteria) it doesn't work as desired for me or I am missing something.

var projectId = req.params.projectId
var uploadId = req.body.uploadId

let populateQuery = [
    {path: 'owner'},
    {path: 'uploaded_files.file', match: {upload_id: uploadId}},
    {path: 'file_history.file', match: {upload_id: uploadId}}
]

Project.findOne({ project_id: projectId }).populate(populateQuery).then(project => {
    console.log(project)
})

My document looks like this:

{
    "_id" : ObjectId("5935a41f12f3fac949a5f925"),
    "project_id" : 13,
    "updated_at" : ISODate("2017-07-05T21:45:46.754Z"),
    "created_at" : ISODate("2017-06-05T18:34:07.150Z"),
    "owner" : ObjectId("591eea4439e1ce33b47e73c3"),
    "name" : "RDemo project",
    "uploaded_files" : [ 
        {
            "display_name" : "Log_28-6-2017_14-17-53-562.txt",
            "file" : ObjectId("595c4c4f3ae2470700ea07e2"),
            "upload_id" : ObjectId("595c0f7ea1d20247285be5f5"),
            "created_at" : ISODate("2017-07-05T02:17:51.000Z")
        }, 
        {
            "display_name" : "Coon.png",
            "file" : ObjectId("595c4c553ae2470700ea07e4"),
            "upload_id" : ObjectId("595c4c553ae2470700ea07e5"),
            "created_at" : ISODate("2017-07-05T02:17:57.000Z")
        }
    ],
    "file_history" : [ 
        {
            "display_name" : "account working.txt",
            "file" : ObjectId("595c0f7ea1d20247285be5f4"),
            "upload_id" : ObjectId("595c0f7ea1d20247285be5f5"),
            "created_at" : ISODate("2017-07-04T21:58:22.000Z")
        }, 
        {
            "display_name" : "Log_28-6-2017_14-17-53-562.txt",
            "file" : ObjectId("595c4c4f3ae2470700ea07e2"),
            "upload_id" : ObjectId("595c0f7ea1d20247285be5f5"),
            "created_at" : ISODate("2017-07-05T02:17:51.000Z")
        }
    ]
}

It will print the project with all it's subdocuments in file_history and uploaded_files. However I only want to get those subdocuments which match the given upload_id. What am I doing wrong?

Edit: This question refers to solving it using mongoose. I wasn't able to use the aggregate statement from this question (Retrieve only the queried element in an object array in MongoDB collection) in Mongoose as it returned an empty array, however I was able to run it via MongoDB and it returned indeed the desired result. That's the code I've tried:

    let projectId = req.params.projectId // Project ID is an integer and no objectId
    let uploadId = new mongoose.Types.ObjectId(req.body.uploadId)
    console.log(`ProjectID: ${projectId}, uploadId: ${uploadId}, params: ${req.body.uploadId}`)

    let populateQuery = [
        {path: 'owner'},
        {path: 'uploaded_files.file'},
        {path: 'file_history.file'}
    ]

    Project.aggregate(
        {$match: {"project_id": projectId}},
        {$project: {
            uploaded_files: {$filter: {
                    input: '$uploaded_files',
                    as: 'uploadedFile',
                    cond: {$eq: ['$$uploadedFile.upload_id', uploadId]}
                }}
            }
        }
    ).then(project => {
        console.log(project)
})

console.log returns: ProjectID: 13, uploadId: 595c0f7ea1d20247285be5f5, params: 595c0f7ea1d20247285be5f5 which is correct (ids are existent)

Based on your previous questions your schema is "embedded", yet `.populate()` is for "referenced" schemas where the actual data is in other collections. Show your actual document and what you expect to achieve. — Neil Lunn, Jul 05 '17 at 21:55
I think the main point here is that it's likely "embedded" and that is the distinction you need to learn — Neil Lunn, Jul 05 '17 at 21:58
There is no difference between MongoDB and Mongoose. The query syntax is identical. Mongoose is just a driver implementation and not it's own thing. You get an "empty array" because you did not cast your "string" into an `ObjectId`. Which is [actually another "duplicate" question.](https://stackoverflow.com/questions/36193289/moongoose-aggregate-match-does-not-match-ids) — Neil Lunn, Jul 06 '17 at 23:09
I did that but that wasn't the issue @NeilLunn. I also ensured the variables are correct. When I perform the query in MongoDB I don't have any issues. — kentor, Jul 06 '17 at 23:34
In MongoDB you are supplying the `ObjectId` values. Your inputs are "strings". With an operation like `.find()`, mongoose looks at the schema and "casts" the supplied arguments to the correct defined type for you. It cannot do this in aggregation pipelines, since there is "no schema on the server". You need to "cast" the values yourself. That is your "new" issue, and fully explained in the given linked answer. If you "did that" then show what you actually did. Otherwise it looks like you are doing it incorrectly. Read the links and learn from them. That's why the question is linked. — Neil Lunn, Jul 06 '17 at 23:42
@NeilLunn Okay I have edited into the thread. Isn't `let uploadId = mongoose.Types.ObjectId(req.body.uploadId)` enough or do I really need such a map function as suggested in the linked answer? — kentor, Jul 06 '17 at 23:51
Well it either works or it does not. If you get no result then actually use the import from the core driver as is in fact suggested. If you still get no results your inputs are wrong and you should check by hard-coding the values and then debugging why you are getting different inputs. The `$filter` statement is correct, so this is all about the actual values you are supplying. — Neil Lunn, Jul 06 '17 at 23:56
@NeilLunn I edited it once again and I ensured that my parameters (uploadId and projectId are correct). Do you have any ideas what is still wrong there? — kentor, Jul 07 '17 at 01:13

score 1 · Accepted Answer · answered Jul 07 '17 at 02:16

You are doing something horribly wrong and it unclear what that is. The best way to show you is therefore by example.

Also note that $filter is not even needed for a "single match" and a standard positional projection will simply just do here:

const async = require('async'),
      mongoose = require('mongoose'),
      Schema = mongoose.Schema,
      ObjectId = require('mongodb').ObjectID

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

mongoose.connect('mongodb://localhost/test');

const doc =
{
    "_id" : ObjectId("5935a41f12f3fac949a5f925"),
    "project_id" : 13,
    "updated_at" : new Date("2017-07-05T21:45:46.754Z"),
    "created_at" : new Date("2017-06-05T18:34:07.150Z"),
    "owner" : ObjectId("591eea4439e1ce33b47e73c3"),
    "name" : "RDemo project",
    "uploaded_files" : [
        {
            "display_name" : "Log_28-6-2017_14-17-53-562.txt",
            "file" : ObjectId("595c4c4f3ae2470700ea07e2"),
            "upload_id" : ObjectId("595c0f7ea1d20247285be5f5"),
            "created_at" : new Date("2017-07-05T02:17:51.000Z")
        },
        {
            "display_name" : "Coon.png",
            "file" : ObjectId("595c4c553ae2470700ea07e4"),
            "upload_id" : ObjectId("595c4c553ae2470700ea07e5"),
            "created_at" : new Date("2017-07-05T02:17:57.000Z")
        }
    ],
    "file_history" : [
        {
            "display_name" : "account working.txt",
            "file" : ObjectId("595c0f7ea1d20247285be5f4"),
            "upload_id" : ObjectId("595c0f7ea1d20247285be5f5"),
            "created_at" : new Date("2017-07-04T21:58:22.000Z")
        },
        {
            "display_name" : "Log_28-6-2017_14-17-53-562.txt",
            "file" : ObjectId("595c4c4f3ae2470700ea07e2"),
            "upload_id" : ObjectId("595c0f7ea1d20247285be5f5"),
            "created_at" : new Date("2017-07-05T02:17:51.000Z")
        },
        {
            "display_name" : "Coon.png",
            "file" : ObjectId("595c4c553ae2470700ea07e4"),
            "upload_id" : ObjectId("595c4c553ae2470700ea07e5"),
            "created_at" : new Date("2017-07-05T02:17:57.000Z")
        },
        {
            "display_name" : "Coon.png",
            "file" : ObjectId("595c4c553ae2470700ea07e4"),
            "upload_id" : ObjectId("595c4c553ae2470700ea07e5"),
            "created_at" : new Date("2017-07-05T02:17:57.000Z")
        }
    ]
};

const Test = mongoose.model('Test', new Schema({}, { strict: false }) );

function log(data) {
  console.log(JSON.stringify(data, undefined, 2))
}

async.series(
  [
    // Clean data
    (callback) =>
      async.each(mongoose.models,(model,callback) =>
        model.remove({},callback),callback),

    // Insert data
    (callback) => Test.insertMany(doc,callback),

    // Correct usage of $filter
    (callback) =>
      Test.aggregate(
        [
          { "$match": { "project_id": 13 } },
          { "$addFields": {
            "uploaded_files": {
              "$filter": {
                "input": "$uploaded_files",
                "as": "f",
                "cond": {
                  "$eq": [
                    "$$f.upload_id", ObjectId("595c0f7ea1d20247285be5f5")
                  ]
                }
              }
            },
            "file_history": {
              "$filter": {
                "input": "$file_history",
                "as": "f",
                "cond": {
                  "$eq": [
                    "$$f.upload_id", ObjectId("595c0f7ea1d20247285be5f5")
                  ]
                }
              }
            }

          }}
        ],
        (err,results) => {
          if (err) callback(err);
          log(results);
          callback();
        }
      ),

      // Just a normal positional project for 1 match
      (callback) => Test.findOne(
        { "uploaded_files.upload_id": ObjectId("595c0f7ea1d20247285be5f5") },
        { "uploaded_files.$": 1 },
        (err,result) => {
          if (err) callback(err);
          log(result);
          callback();
        }
      )

  ],
  (err) => {
    if (err) throw err;
    mongoose.disconnect();
  }
)

Produces the desired output:

[
  {
    "_id": "5935a41f12f3fac949a5f925",
    "__v": 0,
    "project_id": 13,
    "updated_at": "2017-07-05T21:45:46.754Z",
    "created_at": "2017-06-05T18:34:07.150Z",
    "owner": "591eea4439e1ce33b47e73c3",
    "name": "RDemo project",
    "uploaded_files": [
      {
        "created_at": "2017-07-05T02:17:51.000Z",
        "upload_id": "595c0f7ea1d20247285be5f5",
        "file": "595c4c4f3ae2470700ea07e2",
        "display_name": "Log_28-6-2017_14-17-53-562.txt"
      }
    ],
    "file_history": [
      {
        "created_at": "2017-07-04T21:58:22.000Z",
        "upload_id": "595c0f7ea1d20247285be5f5",
        "file": "595c0f7ea1d20247285be5f4",
        "display_name": "account working.txt"
      },
      {
        "created_at": "2017-07-05T02:17:51.000Z",
        "upload_id": "595c0f7ea1d20247285be5f5",
        "file": "595c4c4f3ae2470700ea07e2",
        "display_name": "Log_28-6-2017_14-17-53-562.txt"
      }
    ]
  }
]
Mongoose: tests.findOne({ 'uploaded_files.upload_id': ObjectId("595c0f7ea1d20247285be5f5") }, { fields: { 'uploaded_files.$': 1 } })
{
  "_id": "5935a41f12f3fac949a5f925",
  "uploaded_files": [
    {
      "display_name": "Log_28-6-2017_14-17-53-562.txt",
      "file": "595c4c4f3ae2470700ea07e2",
      "upload_id": "595c0f7ea1d20247285be5f5",
      "created_at": "2017-07-05T02:17:51.000Z"
    }
  ]
}

I figured that the projectId has been considered as "string" and not as integer. After parsing it as integer it worked as intended. That's odd because for find/findOne it was working without parsing. Thanks for all your help — kentor, Jul 07 '17 at 17:22

Find all subdocuments which meet a criteria

1 Answers1