I have a MongoDB collection in the form of:

users: {
    {
        user_id: 1000,
        activities: [
           {id: 1, activity: 'swimming'},
           {id: 2, activity: 'running'},
           {id: 3, activity: 'biking'},...
        ]
    },...
}

and I want to get the activity document that matches a specific ID. For example, if I query using {id: 1}, I want an output of {id: 1, activity: 'swimming'}. Currenlty, I'm trying to use findOne({activities: {$elemMatch: {id: 1}}}), but it returns the entire user document (the user id, and all the activities).

The code I'm using is:

id = req.body.queryID;
db.collection('users').findOne({activities: {$elemMatch: {id}}})
    .then((document) => {
        console.log(document);
        // more code after this
    });

I've also tried to query using aggregate() and findOne({}, {activities: {$elemMatch: {id}}}), but I haven't been able to figure it out.

I am on MongoDB version 4.4.8 according to db.version(). Any help is appreciated!

CodePudding user response：

You can use the aggregate method with the unwind, match and project pipelines

db.users.aggregate([
    {$unwind: "$activities"},
    {$match: {"activities.id": id}},
    {$project: {id: "$activities.id", activity: "$activities.activity", _id: 0}}
])

NOTE: This code is written using the mongo shell syntax.

The query does the following

1. starts with the unwind pipeline which first pulls out all the items in the activities array.
2. The match pipeline finds the array element that has the id we're looking for
3. The project pipeline returns the output as desired

Hope this helps

CodePudding user response：

Please try as follow:

db.users.aggregate([
      {
        '$unwind': '$activities'
      }, {
        '$match': {
          'id': id
        }
      }, {
        '$project': {
          'activity': '$activities.activity', 
          'id': '$activities.id',
          '_id':0
        }
      }
    ]);

CodePudding user response：

Your attempts look close, I think you just need to put them together. findOne takes two optional arguments, a query and a projection. The query tells MongoDB what document to look for and return. The projection document tells MongoDB what field(s) to include in the document returned by the query.

What you have here:

db.collection('users').findOne({ activities: { $elemMatch: { id }}})

Passes one argument, the query. It will look for a document where there is one element in the activities array with an id equal to the value of the variable id. You could also write this as:

db.collection('users').findOne({ "activities.id": id })

You'd also like to only return documents in the activities array with a matching ID. This is when you'd do something like your other attempt, where $elemMatch is in the second argument, the projection:

db.collection('users').findOne({}, {activities: {$elemMatch: {id}}})

But because you passed an empty query, MongoDB will return any document, not necessarily one that contains an activity with a matching ID.

So if you pass both a query and the projection, I think you should get what you're looking for. It might look like this:

db.collection('users').findOne({ "activities.id": id }, { activities: { $elemMatch: { id }}})

This will include only the _id field and the matching activities document if one exists. If you want to include other fields of the document, they need to be explicitly included. See this documentation about projection.