0

For example, i have 2 collections:

First one is named as "Songs" and documents in this collection store all metadata about song like title, duration, release_date and other. Beside this, there are also artist and producer fields, these fields stores ids of relational documents from "Stars" collection.

Example of document in "Songs" collection:

{ 
   id: SONG_ID
   title: TITLE, 
   duration: DURATION, 
   release_date: ISO_DATE,
   ...
   artist: STAR_ID
   producer: STAR_ID
}

Second one as you guess is "Stars" collection and documents in this collection store information about stars like fullname, date_of_birth and other.

Example of document in "Stars" collection:

{ 
   id: STAR_ID
   fullname: FULLNAME, 
   date_of_birth: DATE_OF_BIRTH
   ...
}

Suppose i want to find songs that (1 condition) have "love" word in their titles, (2 condition) released in 2017 year and (3 condition) from artists like Rihanna or Zara Larsson. Please note that my problem is different from simple left outer join. Note that in this query 3rd condition relates to information that stored in second collection "Stars", so i dont want just pollute artist and producer fields with information from "Stars" collection but also i want to choose songs with stars just like Rihanna or Zara Larsson.

What is the best and fastest way to do this?
Thanks for your help!

Community
  • 1
  • 1
Emil
  • 671
  • 1
  • 6
  • 22
  • Possible duplicate of [How do I perform the SQL Join equivalent in MongoDB?](https://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb) – Adam Harrison Jul 03 '17 at 18:21
  • @AdamHarrison please note that my problem is different from simple left outer join. Note that in this query 3rd condition relates to information that stored in second collection "Stars", so i dont want just pollute artist and producer fields with information from "Stars" collection but also i want to choose songs with stars just like Rihanna or Zara Larsson. – Emil Jul 03 '17 at 18:35
  • Are you looking for a specific part of implementation that you have trouble with ? Its hard to know what you've done and what kind of answer you are expecting. – s7vr Jul 03 '17 at 19:09
  • @Veeram i am open to any answer that could explain me how to build query for this case in best manner. – Emil Jul 03 '17 at 19:13

1 Answers1

0

You can use below aggregation query.

$match on release_date and $regex match on title.

$lookup to look up artist collection followed by $match on fullname for input values. 

db.Songs.aggregate([
 { "$match":{ "release_date": { "$gte": ISODate("2017-01-01T00:00:00.000Z"), "$lt": ISODate("2018-01-01T00:00:00.000Z")}, title: { $regex: /love$/ } } },
 { "$lookup": {
       "from" : "Stars",
       "localField" : "artist",
       "foreignField" : "_id",
       "as" : "star"
    }
 },
 { "$match" : { "star.fullname" : { "$in": [ "Rihanna", "Zara Larsson" ] } } }
])

Regex can make use of index when anchored. Compare with text search.

s7vr
  • 73,656
  • 11
  • 106
  • 127
  • Thanks! I think aggregation pipeline and $match operator is exactly what i was looking for. But as i know second $match wont be able to take advantage of indexes and this is a disappointing point. – Emil Jul 05 '17 at 09:20
  • [>> You can read about this here <<](https://docs.mongodb.com/manual/reference/operator/aggregation/match/#pipeline-optimization) – Emil Jul 05 '17 at 09:26
  • You are welcome. The second match can use index. Add `$unwind` between `$lookup` and `$match` and see if it can use index. More here https://jira.mongodb.org/browse/SERVER-28140 – s7vr Jul 05 '17 at 12:39
  • in this specific case i think better solution is to do it in 2 steps. In firs step find ids of stars by name from Stars collection. And then in second step find needed songs using ids from first step. What do you think about this solution? – Emil Jul 07 '17 at 17:14
  • There is only one way to find out which is test both approaches for your application. – s7vr Jul 08 '17 at 05:34