How to connect to collections by nested fields in MongoDB

Question

I am struggling with some query in MongoDB. Let's say I have standings collection which looks like

{
   "competitions: {id: "1", name:"someLeague"},
   "standings": [
      {
         "type": "TOTAL",
         "table": [
            {
               "position": "1",
               "team": {
                  "id": "123",
                  "name": "XYZ"
               },  
               won: "1",
               draw: "2",
               lost: "3",
               points: "4",
            },
            {
               "position": "2",
               "team": {
                  "id": "321",
                  "name": "ABC"
               }
                      ...

And the fixtures collection which looks like

{
   matchDay: "YYYY-MM-DD",
   homeTeam: {id:  "123", name:"ABC"}, 
   awayTeam: {id:  "321", name:"XYZ"},
}

Is it possible to connect this two collection this way that field "homeTeam" in fixtures collection will contain all information including points, won games etc. from standings where type would be total? And same thing with the field awayTeam, with the proviso that information of team would be from array where standings type is away.

score 0 · Answer 1 · answered Feb 12 '19 at 12:30

There is no means in MongoDB to reference a document of collection A in collection B so that find queries on collection B automatically provide attributes of the referenced document. However, as of MongoDB 3.2 it is possible to use $lookup command as part of an aggregation (see https://stackoverflow.com/a/33511166/3976662) to JOIN (similar to standard SQL) over multiple collections during the query. In your case, you can consider using $lookup in conjunction with $unwind - similar to the example in the MongoDB docs. Spring Data Mongo supports $lookup since 1.10.

How to connect to collections by nested fields in MongoDB

1 Answers1