0

I'm very new to MongoDb so I'm used to SQL. Right now I have two collections in my database:

1) Series (which has nested subdocuments)

2) Review (decided to reference to episode subdocument because there will be a lot of reviews)

See this picture for a better understanding.

enter image description here

Now I want to achieve te following. For every review (two in this case), I want to get the episode name.

I tried the following:

db.review.aggregate([  
   {  
      $lookup:{  
         from:"series",
         localField:"episode",
         foreignField:"seasons.episodes._id",
         as:"episode_entry"
      }
   }
]).pretty()

The problem is that this returns (ofcourse) not only the title of the referenced episode, but it returns the whole season document.

See the picture below for my current output.

enter image description here

I don't know how to achieve it. Please help me. I'm using Mongo 3.4.9

Mike Evers
  • 185
  • 1
  • 4
  • 15
  • Could you please post your sample data in text format instead of using images so we can copy and paste it? Also, what's your exact desired output structure? – dnickless Oct 25 '17 at 18:17

1 Answers1

1

I would recommend the following series structure which unwinds the season array into multiple documents one for each season.

This will help you with inserting/updating the episodes directly.

Something like 

db.series.insertMany([
  {
    "title": "Sherlock Holmes",
    "nr": 1,
    "episodes": [
      {
        "title": "A Study in Pink",
        "nr": 1
      },
      {
        "title": "The Blind Banker",
        "nr": 2
      }
    ]
  },
  {
    "title": "Sherlock Holmes",
    "nr": 2,
    "episodes": [
      {
        "title": "A Scandal in Belgravia",
        "nr": 1
      },
      {
        "title": "The Hounds of Baskerville",
        "nr": 2
      }
    ]
  }
])

The lookup query will do something like this 

episode: { $in: [ episodes._id1, episodes._id2, ... ] }

From the docs

If the field holds an array, then the $in operator selects the documents whose field holds an array that contains at least one element that matches a value in the specified array (e.g. , , etc.)

So lookup will return all episodes when there is a match. You can then filter to keep only the one matching your review's episode.

So the query will look like

db.review.aggregate([
  {
    "$lookup": {
      "from": "series",
      "localField": "episode",
      "foreignField": "episodes._id",
      "as": "episode_entry"
    }
  },
  {
    "$addFields": {
      "episode_entry": [
        {
          "$arrayElemAt": {
            "$filter": {
              "input": {
                "$let": {
                  "vars": {
                    "season": {
                      "$arrayElemAt": [
                        "$episode_entry",
                        0
                      ]
                    }
                  },
                  "in": "$$season.episodes"
                }
              },
              "as": "result",
              "cond": {
                "$eq": [
                  "$$result._id",
                  "$episode"
                ]
              }
            }
          }
        },
        0
      ]
    }
  }
])
s7vr
  • 73,656
  • 11
  • 106
  • 127
  • Big thank you for your answer. I also see people just using one collection for this usecase. How do you think about my idea of separating the reviews from the rest? Is it good practice because of it's volume or do you prefer placing it as an subdocument inside an episode entry. – Mike Evers Oct 25 '17 at 18:55
  • Np. There is 16 MB limit on the storage of single document. If you think you will hit the limit with reviews embedded inside of episodes then I would keep reviews in its own collection. You can also model the document where you would store season, episode and it's reviews in a single document. Try not embedding arrays inside arrays as it becomes difficult to update/ insert values. There are many ways to model a document but pick one that is good fit based on your use case. – s7vr Oct 25 '17 at 19:18
  • Your $$result._id is wrong because i need the id's of the episode. Currently it is the _id of the season. How do I get the _id of one level deeper? – Mike Evers Oct 26 '17 at 10:46
  • Sorry, I should have noticed that. Updated answer to include `$let` expression to project episodes of a season to `$filter` aggregation. – s7vr Oct 26 '17 at 12:26
  • 1
    Wow, thnx! I think MongoDb is a lot overcomplicated compared to just sql. – Mike Evers Oct 26 '17 at 12:32