Clever Geek Handbook

Two queries in different collections - MongoDB

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

 {
         "_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 the collective collection document:

   {
           "_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 that mango does not support connection between collections. Suppose now that I have saved the return of the request to the command collection in an array named x. For example:

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

Now I want to use this x array to query the collection of coaches and find the coaches who have trained the team with this ID. I've tried in some way, 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}})

PS I have searched for similar questions on the forum and the answers did not answer my questions.
This doesn't work, for example.

+3


source to share


3 answers


you need to remove quotes "

around your variable x[0]._id

. Otherwise, it is encoded as String and the contents of the variable will not be displayed and filled.



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


source


It's actually a little simpler:

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

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


A somewhat cleaner approach (i.e. required when you want multiple criteria ) uses $elemMatch

:

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


source


First you will find all distinct

the command id

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

teamId

contains an array of the command identifier. Use this aggregate for a collection of trainers

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 only want to change the specific command id change above:

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


source






More articles:


All Articles