CODEWITHSUNDEEP
node.jsmongodbmongooseaggregation-framework
André
André

Reputation: 783

Mongoose - Aggregation of two queries with condition

I've two different collections that are connected by the id of the garden. I've a list of gardens and I've a list of allocations where it will be stored the start and the end date of the allocation. I can check if a garden is allocated by verifying if today is between both dates in the allocation table.

Garden

{
    "_id": "5b98df3c9275f2291c0d7dc3",
    "id": "h1",
    "size": 43
}

Allocation

{
    "_id": "5b9bcb8ecb9dee0015150549",
    "user": "5b9a2cd21eb58700141a3449",
    "garden": "5b98df5c9275f2291c0d7dc6",
    "start_date":"2018-09-14T00:00:00.000Z",
    "end_date": "2018-11-14T00:00:00.000Z"
}

How can I return all the existing gardens with an aditional field 'ocupied' with true or false depending on if they exist on the allocation document between start_date and end_date?

I'd like to get an array of gardens with the following data

{
    "_id": "5b98df3c9275f2291c0d7dc3",
    "id": "h1",
    "size": 43,
    "occupied": true
}

Upvotes: 0

Views: 557

Answers (1)

s7vr
s7vr

Reputation: 75934

You can do it one of two ways. 

var today = ISODate();

Using $lookup

db.garden.aggregate([
{"$lookup":{
  "from":"allocation",
  "localField":"_id",
  "foreignField":"garden",
  "as":"garden"
}},
{"$unwind":"$garden"},
{"$addFields":{
  "occupied":{
    "$and":[
      {"$gte":["$garden.start_date",today]},
      {"$lt":["$garden.end_date",today]}
    ]
  }
}},
{"$project":{"garden":0}}
])

Using $lookup with pipeline

db.garden.aggregate([
  {"$lookup":{
    "from":"allocation",
    "let":{"garden_id":"$_id"},
    "pipeline":[
      {"$match":{"$expr":{"$eq":["$$garden_id","$garden"]},"start_date":{"$gte":today},"end_date":{"$lt":today}}}
    ],
    "as":"garden"
  }},
  {"$addFields":{
    "occupied":{"$gt":[{"$size":"$garden"},0]}
  }},
  {"$project":{"garden":0}}
])

Upvotes: 1

Related Questions