Use $lookup inside aggregate mongodb

Question

EDITED

I have now tried to go back to my original question. Here is what I am doing:

var sortedList = Spark.runtimeCollection('matchResults').aggregate(
    {$match: { "playerId": jsonObject.playerId, "leadId": jsonObject.leadId}},
    {$lookup:
       {
         from: "matches",
         localField: "group",
         foreignField: "groupId",
         as: "matchDocs"
       }
    },
    {$project:{ 
        type: "$SortBoard",
        points: { $sum: { $add : [ '$data.leadTotals.stfBonus', '$data.leadTotals.placePoints', '$data.leadTotals.startPoints' ] }},
        matchId: "$matchId",
        matchDocuments: "$matchDocs"
    }},
    {$sort:{"points":-1}}
);

This creates no errors, but the $lookup does not return anything!?

Here is my return:

{
  "@class": ".LogEventResponse",
  "scriptData": {
    "@seasonScoresReturn": {
      "scorecards": [
        {
          "_id": {
            "$oid": "5b166c87031f5bc44d50f0ed"
          },
          "matchId": "5b165cba5399c020e3f18dbd",
          "points": 24,
          "matchDocuments": []
        },
        {
          "_id": {
            "$oid": "5b1687b3f798b10f36abae92"
          },
          "matchId": "5b165cba5399c020e3f18dbd",
          "points": 16,
          "matchDocuments": []
        }
      ]
    }
  }
}

My matches document looks like this:

{
  "_id": {
    "$oid": "5b165cba5399c020e3f18dbd"
  },
  "groupId": "5b15958aa2f56b04fe84db2d",
  "updated": {
    "$date": {
      "$numberLong": "1528192186776"
    }
  },
  "data": {
    "type": "MatchData",
    "matchId": "5b165cba5399c020e3f18dbd",
    "groupId": "5b15958aa2f56b04fe84db2d",
    "leaderboardId": "5b165ca15399c020e3f17a75",
    "matchTitle": "",
    "rounds": [],
    "joined": [],
    "finished": true,
    "year": 2018,
    "tieMethod": 0,
    "methodType": 2,
    "pointValue": 1,
    "placeValue": 12,
    "startValue": 0,
    "pointLimit": 25,
    "bosLimit": 25
  }
}

My matchResults document looks like this:

{
  "_id": {
    "$oid": "5b18e4f7031f5bc44d513fc6"
  },
  "matchId": "5b18e4ada2f56b04fea71fac",
  "playerId": "5b18e305031f5bc44d513fb0",
  "groupId": "5b18e3ada2f56b04fea62139",
  "leadId": "5b18e44ea2f56b04fea6ca45",
  "data": {
    "type": "MatchResult",
    "playerId": "5b18e305031f5bc44d513fb0",
    "rounds": 1,
    "leadTotals": {
      "type": "TotalLeadPoints",
      "stfBonus": 11,
      "placePoints": 12,
      "startPoints": 0,
      "bosPoints": 0
    },
    "scoreTotals": {
      "type": "TotalHoleData",
      "strokes": 76,
      "strokesNet": 72,
      "points": 36,
      "modPoints": 0,
      "toPar": 0,
      "puts": 0,
      "shorts": 0,
      "bx2": 0,
      "gir": 0,
      "hit": 0,
      "sand": 0,
      "outs": 0,
      "ladies": 0,
      "aces": 0,
      "albatros": 0,
      "eagles": 0,
      "birdies": 0,
      "pars": 14,
      "bogeys": 4,
      "bogeysx2": 0,
      "bogeysx3": 0,
      "put0": 18,
      "put1": 0,
      "put2": 0,
      "put3": 0,
      "put4": 0,
      "scramples": 18,
      "last9": 0,
      "last6": 0,
      "last3": 0,
      "last2": 0,
      "last1": 0
    }
  }
}

My question is now... How do I get only the specific document from matches ($match: matchid). I do not see in the documentation an example on how to accomplish that.

Also... Why am I getting a empty array?

Hope someone can help me with this issue and thanks in advance :-)

There's a different thing for that. In modern releases there is [`$lookup`](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/) which you would add as a stage to do what you want here. — Neil Lunn, Jun 06 '18 at 21:44
@Neil Lunn - I have looked at the duplicate question and answers and tested and edited this question. — Mansa, Jun 06 '18 at 22:26
You're using the wrong form of `$lookup` for your supported MongoDB. Using a "sub-pipeline" requires MongoDB 3,6. You can still do what you wanted to do with the ["original question content"](https://stackoverflow.com/revisions/50730036/1) with the standard form of `$lookup` using `localField` and `foreighField`. It just means you need to manipulate the returned "array", and "after" it is returned from the `$lookup` result. At any rate it's not clear what the "two" collection structures are. If you still have problems then include sample documents from each collection you expect to "join". — Neil Lunn, Jun 06 '18 at 22:30
Inside the pipeline under lookup, change the expression { $match: { "data.matchid": "$matchId" } }, to following: { $match: { $expr:{ $eq: [ "data.matchid", "$matchId ] }}}, — shmit, Jun 06 '18 at 22:34
Always post some sample collection when doing such type of aggregations — Ashh, Jun 07 '18 at 02:36
@shmit It's `{ $expr:{ $eq: [ "$data.matchid", "$matchId ] }` But the error suggests the MongoDB is not a 3.6 version anyway. The question is marked in duplicate of existing syntax examples and there is a `$lookup` documentation link in the very first comment, so there is ample opportunity for the OP to actually look at how to do it properly. What is more is the OP has been prompted several times to provide reproducible data for their question. If they are prepared to do that, then there is little more to do than point to documentation. — Neil Lunn, Jun 07 '18 at 07:15
@Neil Lunn - I have updated my initial question. Hope this makes it easier to understand, and thanks for all your help. — Mansa, Jun 07 '18 at 07:22
I see "two" collections in your attempt. `matches` and `matchResults` You're only showing "one" document from "one" collection. Show at least one document you expect to match from the other collection. — Neil Lunn, Jun 07 '18 at 07:42
@Neil Lunn - Just added an example of matchResults document. Sorry :-/ — Mansa, Jun 07 '18 at 07:58
`{ $lookup: { from: "matches", localField: "groupId", foreignField: "data.groupId", "as": "matchDocs" } }` You also have "strings" that look like `ObjectId` values that you really need to fix as being `ObjectId` values. This will work where those are both strings, but the moment you try to compare to other fields where both are not then things will break. An `ObjectId` is actually a 12-byte structure internally and very different to a string. Convert back to the correct types and save a lot of space as well as future problems. There is also a "nested field" example in the documentation. — Neil Lunn, Jun 07 '18 at 08:09
@Neil Lunn - Thanks for your suggestion and advice, but I still get empty array :-/ I guess i have to work around another way. — Mansa, Jun 07 '18 at 08:24
You need to read the documentation and pay attention to what has already been said. I've shown the "correct paths". The only reason that remains is "mismatched data" and mostly "mismatched data types". Like the material on the answers on the duplicate say. These need to be "equal'. Works for everybody else in the world. — Neil Lunn, Jun 07 '18 at 08:26

Use $lookup inside aggregate mongodb

0 Answers0