How do I specify multiple Join Conditions with $lookup in ReactiveMongo (0.17.1) with the following in mind? - as per the mongoDB documentation.
users collection
[
{
"name": "dogfrey",
"field": "cowboy"
},
{
"name": "catsville",
"field": "spaceman"
}
]
roles collection
[
{
"id": 0,
"userType": "cowboy",
"spaceman": "fiver",
"num": 2
},
{
"id": 1,
"userType": "joker",
"spaceman": "tenner",
"num": 3
},
{
"id": 2,
"userType": "cowboy",
"spaceman": "tenner",
"num": 1
}
]
MongoDb query
db.users.aggregate([
{
"$match": {
"name": "dogfrey"
}
},
{
"$lookup": {
"from": "roles",
let: {
"users_field": "$field"
},
pipeline: [
{
$match: {
$expr: {
$eq: [
"$userType",
"$$users_field"
]
},
}
},
{
$project: {
_id: 0
}
}
],
"as": "dogs"
}
}
])
See here for the MongoPlayground example
The ReactiveMongo documentation doesn't include any such examples suggesting that it may not be possible. Any help appreciated!
Also just to add something that I have tried (unsuccessfully):
def getResults(aColl: JSONCollection, bColl: JSONCollection)
(id: BSONObjectID)(implicit request: Request[AnyContent]) = aColl.aggregateWith[JsObject]() {
framework => import framework.{Match, Lookup, AddFields, Project, Sort, Ascending, Descending, Filter, Limit, Group, Sum, Push, Slice}
...
val lookupJso = Lookup(
from = bColl.name,
let = Json.obj("fromDate" -> "$varData_e.plan_e.when_e.fromDate", "toDate" -> "$varData_e.plan_e.when_e.toDate"),
pipeline = Json.arr(
"$match" -> Json.obj("$expr" ->
Json.obj("$and" -> Json.arr(
Json.obj("$gte" -> Json.arr("$varData_e.dateTime", f"$$fromDate")),
Json.obj("$lt" -> Json.arr("$varData_e.dateTime", f"$$toDate"))
)),
),
"$project" -> Json.obj("_id" -> 0)
),
"temp_e.lookupTest1_e"
)
...
}.collect[List](Int.MaxValue, Cursor.FailOnError[List[JsObject]]())
I can see that the Lookup case class
looks like this:
case class Lookup(
from: String,
localField: String,
foreignField: String,
as: String) extends PipelineOperator {
import builder.{ document, elementProducer => element, string }
val makePipe: pack.Document = document(Seq(
element(f"$$lookup", document(Seq(
element("from", string(from)),
element("localField", string(localField)),
element("foreignField", string(foreignField)),
element("as", string(as)))))))
}
Release 0.17.1 is more than one year old (latest version being the major 1.0).
You can see in the documentation it's possible to define raw aggregation operator for stage not provided convenient factory in the API.
import scala.concurrent.ExecutionContext
import reactivemongo.bson._
import reactivemongo.api.collections.bson.BSONCollection
def customAgg(coll: BSONCollection)(implicit ec: ExecutionContext) =
coll.aggregateWith[BSONDocument]() { framework =>
import framework.{ Match, PipelineOperator, Project }
val lookup = PipelineOperator(BSONDocument(f"$$lookup" -> BSONDocument(
"from" -> "roles",
"let" -> BSONDocument("users_field" -> f"$$field"),
"pipeline" -> Seq(
Match(BSONDocument(f"$$expr" ->
BSONDocument(f"$$eq" -> Seq(f"$$userType", f"$$$$users_field")))).
makePipe,
Project(BSONDocument("_id" -> 0)).makePipe),
"as" -> "dogs")))
Match(BSONDocument("name" -> "dogfrey")) -> List(lookup)
}