mongodbscalaaggregation-frameworkreactivemongo

How do I specify multiple Join Conditions with $lookup in ReactiveMongo aggregation framework?


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)))))))
  }

Solution

  • 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)
      }