Retrieve a specific element in an object array in MongoDB collection

Question

I have the following document:

{ 
  "_id" : 257655, 
  "name" : "Arrow", 
  "airsDayOfWeek" : "Wednesday", 
  "airsTime" : "8:00 PM", 
  "firstAired" : ISODate("2012-10-10T00:00:00.000+0000"), 
  "runtime" : 45, 
  "imdbId" : "tt2193021",
  "seasons" : [
      {
          "number" : 1, 
          "episodes" : [
              {
                  "_id" : 4599381, 
                  "number" : 1, 
                  "overview" : "Diggle and Felicity  ...", 
              }, 
              {
                  "_id" : 4365918, 
                  "number" : 2, 
                  "overview" : "Oliver heads ...", 
              }
           ]
      },
      {
          "number" : 2, 
          "episodes" : [
              {
                  "_id" : 4599398, 
                  "number" : 1, 
                  "overview" : "With crime in Starling Cit  ...", 
              }, 
              {
                  "_id" : 4365989, 
                  "number" : 2, 
                  "overview" : "Oliver finds out the medicine ...", 
              }
           ]
      }
  ]
}

The following query

db.shows.find({ 'seasons.episodes.number': 1, 'seasons.number': 1, _id: 257655 })

returns the document with all records in seasons.episodes

I tried this one too

db.shows.aggregate([
{$match: { '$and': [ {'seasons.number': 1}, { 'seasons.episodes.number': 1}, {_id: 257655}] }},
{$project: {
    seasons: { 
      $filter: {
        input: '$seasons',
        as: 'seasons',
        cond: {$eq: ['$$seasons.episodes.number', 1]}
      }
    }
}}
])

but i got an error :exception: invalid operator '$filter'

However, I'd like to get the document only with the array that contains seasons.episodes.number = 1 and seasons.number = 1

{
seasons" : [
        {
            "number" : 1, 
            "episodes" : 
                {
                    "_id" : 4599381, 
                    "number" : 1, 
                    "overview" : "Diggle and Felicity  ...", 
                }
        }
]
}

How can I do this?

Answer 1

it seems you want to extract only the first episode of the first season of the show (or each show). If this is the case, after using $filter, you may still need $unwind to normalize the array because $filter returns an array either.

db.test.aggregate([
{$match: { '$and': [ {'seasons.number': 1}, { 'seasons.episodes.number': 1}, {_id: 257655}] }},
{$project: {
    season1: { 
      $filter: {
        input: '$seasons',
        as: 'season',
        cond: {$eq: ['$$season.number', 1]}
      }
    }
}},
{$unwind : '$season1'},
{$project: {
    episode1: { 
      $filter: {
        input: '$season1.episodes',
        as: 'episode',
        cond: {$eq: ['$$episode.number', 1]}
      }
    }
}}
]).pretty()

$unwind gives you the array of episodes in the first season, with which you can use $filter again to show only the first episode (from the array).

Answer 2

If you limit the results to just a single entry with the first $match, you can unwind the array and regroup it against the season and episode, limiting your match to the series and episode you're interested in.

Query

db.shows.aggregate([
{$match: { _id: 257655 }},
{$unwind: "$seasons" },
{$unwind: "$seasons.episodes" },
{$group: { _id: { series: "$seasons.number", ep: "$seasons.episodes.number" }, seasons: { $push: "$seasons" } } },
{$match: { "_id.series": 1, "_id.ep": 1} }
]).pretty()

Result

{
        "_id" : {
                "series" : 1,
                "ep" : 1
        },
        "seasons" : [
                {
                        "number" : 1,
                        "episodes" : {
                                "_id" : 4599381,
                                "number" : 1,
                                "overview" : "Diggle and Felicity  ..."
                        }
                }
        ]
}