Mongoose: find() - match two values and return only the documents matched

Question

I've looked around and I can't find an exact solution to this problem. I've looked through the Mongoose docs and tried to find a way. I'm new to MongoDB and wanted to aim to make my own API after following a lot of tutorials.

The Aim:

GET request to http://localhost:3000/seasons/19/1 and return the series details 19 and the episode selected 1

Something like:

{
        "_id": "5b893aff76c12c166c4e75ae",
        "season_number": 19,
        "name": "Season 19",
        "number_of_episodes": 24,
        "episode": [
            {
                "_id": "5b895e56089a9a152c16af1f",
                "episode_number": 1,
                "name": "19.01",
                "length": "1:23:33",
                "author": "Aaron Wright",
                "release": "2018-01-13T00:00:00.000Z",
                "description": "We kick off our 2018 season with the worst of CES and the future of smart toilets before Aaron unravels the controversial “Dear David” saga that has sparked controversy. We then look at state sponsored mind control programs and the hypnotic experiments performed on unwitting Princeton students in the 1960s."
            },

}

The Problem:

I can't seem to figure out how to return the object as above with just the one selected episode. As all the data is in episodes but I can return it if I manually use episodes[0] etc.

Here is the repo file so not to make this post too long: https://github.com/DannnB/mysterious-universe-api/blob/master/api/controllers/seasons.js

Line: 63
Name: seasons_get_episode_number

Thanks for any help!

The database - hosted on MongoDB Atlas - free tier so can't use $where and don't want to use $where as its a heavy command.

 "data": [
    {
        "_id": "5b893aff76c12c166c4e75ae",
        "season_number": 19,
        "name": "Season 19",
        "number_of_episodes": 24,
        "episodes": [
            {
                "_id": "5b895e56089a9a152c16af1f",
                "episode_number": 1,
                "name": "19.01",
                "length": "1:23:33",
                "author": "Aaron Wright",
                "release": "2018-01-13T00:00:00.000Z",
                "description": "We kick off our 2018 season with the worst of CES and the future of smart toilets before Aaron unravels the controversial “Dear David” saga that has sparked controversy. We then look at state sponsored mind control programs and the hypnotic experiments performed on unwitting Princeton students in the 1960s."
            },
            {
                "_id": "5b895fee089a9a152c16af20",
                "episode_number": 2,
                "name": "19.02",
                "length": "1:22:11",
                "author": "Benjamin Grundy",
                "release": "2018-01-20T00:00:00.000Z",
                "description": "This week we unravel the cosmic serpent to find the origins of life and the link between DNA and Ayahuasca visions. Could the building blocks of all life also be a conscious force that is capable of direct communication with our altered states of mind?"
            }
        ]
    },
    {
        "_id": "5b893b2276c12c166c4e75b0",
        "season_number": 20,
        "name": "Season 20",
        "number_of_episodes": 9
    }, *and so on...*
]

Possible dupe of https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection — s7vr, Sep 03 '18 at 14:46

Sai Teja · Accepted Answer · 2018-09-03T17:56:15.837

1

You can user $filter operator available on Aggregration. Here is the query that matches your requirement:

db.seasons.aggregate([
  { "$match": { "season_number": 19 } },
  { "$project": {
       "season_number": 1,
       "name": 1,
       "number_of_episodes": 1,
       "episodes": {
          "$filter": {
             "input":  "$episodes",
             "as": "episode",
             "cond": { "$eq": [ "$$episode.episode_number", 1 ] }
          }
       }
    } 
  }])

$filter is available from version 3.2

edited Sep 03 '18 at 17:56

answered Sep 03 '18 at 15:04

Sai Teja

365
3
6

Thanks for this, this was the most up to date solution. It works fine as I'm using MongoDB 3.6.7. One more questions to help me learn, why do we need a 1 on the end? `"season_number": 1,` – DannnnB Sep 03 '18 at 16:23
Here we are using "$project" operator, which is similar to select in mySql. So "season_number": 1 means selecting the field for the final output and you can exclude a field from output by saying "season_number": 0 – Sai Teja Sep 03 '18 at 17:54
happy to help :) – Sai Teja Sep 04 '18 at 06:11

grodzi · Answer 2 · 2018-09-04T08:24:30.113

edit: by using $filter

found solution originally here

db.dumb.aggregate(
    {
        $match:{a:'ok', eps:{$elemMatch:{id:1}}}
    },
    {
        $project:{
            a:1, 
            eps:{
                $filter:{
                    input:'$eps', 
                    as:'ep', 
                    cond:{$eq:['$$ep.id',1]}
                }
            }
        }
    }
)

output

{ "_id" : ObjectId("5b8e3eb8e5ab5ef9a9b9b673"), "a" : "ok", "eps" : [ { "id" : 1, "comment" : "ok" } ] }
{ "_id" : ObjectId("5b8e3ec3e5ab5ef9a9b9b676"), "a" : "ok", "eps" : [ { "id" : 1, "comment" : "ok too" } ] }

maybe use unwind?

building a dataset

> db.dumb.insert({a:'ok', eps:[{id:1, comment('ok')}]})
WriteResult({ "nInserted" : 1 })
> db.dumb.insert({a:'ko', eps:[{id:1, comment:'ok'}]})
WriteResult({ "nInserted" : 1 })
> db.dumb.insert({a:'ok', eps:[{id:2, comment:'ko'}]})
WriteResult({ "nInserted" : 1 })
> db.dumb.insert({a:'ok', eps:[{id:2, comment:'ko'}, {id:1, comment:'ok too'}]})
WriteResult({ "nInserted" : 1 })

matching the serie (here named 'ok')

> db.dumb.find({a:'ok', eps:{$elemMatch:{id:1}}})
{ "_id" : ObjectId("5b8d447b21edbcdd7f3b3d24"), "a" : "ok", "eps" : [ { "id" : 1, "comment" : "ok" } ] }
{ "_id" : ObjectId("5b8d449d21edbcdd7f3b3d27"), "a" : "ok", "eps" : [ { "id" : 2, "comment" : "ko" }, { "id" : 1, "comment" : "ok too" } ] }

matching the movie of id 1 of the serie named 'ok' using unwind, every item of the array is "spread". Note the "dupplicate objectIds" (you are keeping track of your unwinded doc)

> db.dumb.aggregate({$match:{a:'ok'}},{$unwind:'$eps'})
{ "_id" : ObjectId("5b8d447b21edbcdd7f3b3d24"), "a" : "ok", "eps" : { "id" : 1, "comment" : "ok" } }
{ "_id" : ObjectId("5b8d449121edbcdd7f3b3d26"), "a" : "ok", "eps" : { "id" : 2, "comment" : "ko" } }
{ "_id" : ObjectId("5b8d449d21edbcdd7f3b3d27"), "a" : "ok", "eps" : { "id" : 2, "comment" : "ko" } }
{ "_id" : ObjectId("5b8d449d21edbcdd7f3b3d27"), "a" : "ok", "eps" : { "id" : 1, "comment" : "ok too" } }

standard match after

> db.dumb.aggregate({$match:{a:'ok'}},{$unwind:'$eps'},{$match:{'eps.id':1}})
{ "_id" : ObjectId("5b8d447b21edbcdd7f3b3d24"), "a" : "ok", "eps" : { "id" : 1, "comment" : "ok" } }
{ "_id" : ObjectId("5b8d449d21edbcdd7f3b3d27"), "a" : "ok", "eps" : { "id" : 1, "comment" : "ok too" } }

Thanks for the comment, This would also work but I didn't want to use $unwind as I read it can be resource consuming on large datasets. +1 for the solution though ! — DannnnB, Sep 03 '18 at 16:25
not so sure except if you have a lot of eps for a particular season (in which case you may want to just denormalize your eps? Anyway edited the post to propose the $filter approach — grodzi, Sep 04 '18 at 08:25

score 0 · Answer 3 · answered Sep 03 '18 at 15:38

db.users.aggregate(

// Pipeline
[
    // Stage 1
    {
        $unwind: {
            path : "$episodes",
            preserveNullAndEmptyArrays : false // optional
        }
    },

    // Stage 2
    {
        $match: {
            "season_number" : 19.0,
            "episodes.episode_number" : 1
        }
    },

    // Stage 3
    {
        $group: {
            "_id":"$_id",
            "season_number" :{$last:"season_number"}, 
            "name" :{$last:"name"}, 
            "number_of_episodes" :{$last:"number_of_episodes"},
            "episodes":{$push:"$episodes"}
        }
    },
]);

Thanks for the comment, another one that would work but I didn't want to use $unwind due to resources. — DannnnB, Sep 03 '18 at 16:26

Mongoose: find() - match two values and return only the documents matched

3 Answers3