CODEWITHSUNDEEP
mongodbmongodb-query
harry-potter
harry-potter

Reputation: 2059

Two queries on different collections - MongoDB

I have two collections in MongoDB. The first contains information about some football coaches and the second contains data about teams. For example, this is a document of coach collection:

 {
         "_id" : ObjectId("556caaac9262ab4f14165fca"),
         "name" : "Luis",
         "surname" : "Enrique Martinez Garcia",
         "age" : 45,
         "date_Of_birth" : {
                 "day" : 8,
                 "month" : 5,
                 "year" : 1970
         },
         "place_Of_birth" : "Gijòn",
         "nationality" : "Spanish",
         "preferred_formation" : "4-3-3 off",
         "coached_Team" : [
                 {
                         "team_id" : "Bar.43",
                         "in_charge" : {
                                 "from" : "01/july/2014"
                         },
                         "matches" : 59
                 },
                 {
                         "team_id" : "Cel.00",
                         "in_charge" : {
                                 "from" : "9/june/2013",
                                 "to" : "30/june/2014"
                         },
                         "matches" : 40
                 },
                 {
                         "team_id" : "Rom.01",
                         "in_charge" : {
                                 "from" : "7/june/2011",
                                 "to" : "10/may/2012"
                         },
                         "matches" : 41
                 }

Here is a document of team collection:

   {
           "_id" : "Bar.43",
           "official_name" : "Futbol Club Barcelona",
           "country" : "Spain",
           "started_by" : {
                   "day" : 28,
                   "month" : 11,
                   "year" : 1899
           },
           "stadium" : {
                   "name" : "Camp Nou",
                   "capacity" : 99354
           },
           "palmarès" : {
                   "La Liga" : 23,
                   "Copa del Rey" : 27,
                   "Supercopa de Espana" : 11,
                   "UEFA Champions League" : 4,
                   "UEFA Cup Winners Cup" : 4,
                   "UEFA Super Cup" : 4,
                   "FIFA Club World cup" : 2
           },
           "uniform" : "blue and dark red"
   }

Well, I know mongo does not support join between collections. Now suppose I saved the return of a query on team collection in an array called x. For example:

var x = db.team.find({_id:"Bar.43"}).toArray()

Now I want to use this array x to query coach collection and find coaches that coached the team with that id. I tried in some ways, but they don't work:

[1]

db.coach.aggregate([{$unwind:"$coached_Team"},{$match:{"coached_Team.team_id:"x[0]._id"}}])

[2]
db.team.find({"x[0]._id":{$in:coached_Team}})

P.S. I looked for similar questions in the forum, and the answers don't reply to mine.
This, for example, does not work.

Upvotes: 3

Views: 1253

Answers (3)

Neo-coder
Neo-coder

Reputation: 7840

First you find all distinct team id as 

var teamId = db.team.distinct("_id")

teamId contains array of team id . An use this aggregation for coach collection 

db.coach.aggregate({"$unwind":"$coached_Team"},{"$match":{"coached_Team.team_id":{"$in":teamId}}}).pretty()

without aggregation use this 

db.coach.find({"coached_Team":{"$elemMatch":{"team_id":{"$in":teamId}}}},{"coached_Team.$.team_id":1})

Or

db.coach.find({"coached_Team.team_id":{"$in":teamId}},{"coached_Team.$.team_id":1})

or if you want only specific team id change above distinct as :

var  teamId = db.team.distinct("_id",{"_id":"Bar.43"})

Upvotes: 0

Simulant
Simulant

Reputation: 20150

you need to remove the quotes " around your variable x[0]._id. Otherwise this is encoded as a String and the content of the variable will not be looked up and filled in.

var x = db.team.find({_id:"Bar.43"}).toArray();
db.coach.find({"coached_Team.team_id":x[0]._id});

Upvotes: 1

mnemosyn
mnemosyn

Reputation: 46341

That is a bit simpler actually:

var x = db.team.find({_id:"Bar.43"}).toArray();

var coaches = db.coach.find( { "coached_Team.team_id" : x[0]._id } );

A slightly cleaner approach (that is required when you want multiple criteria) is using $elemMatch:

var coaches = db.coach.find({ 'coached_Team' : { 
              '$elemMatch' : { 'team_id': x[0]._id /*, optionally more criteria */ } } })

Upvotes: 2

Related Questions