0

My main schema is an event, with some steps embedded in an array. I want to query the events, and filter the steps it has with some conditions. So the event is returned with not all the steps it has, with just the ones that match the condition. I can do it using aggregation, but I need the documents returned as mongoose documents so I can use the save() function on each, so it has to be done using just mongoose I suppose.

a sample event:

{
  "_id" : ObjectId("5d57b6ad754e9d1ec0c123e6"),
  "title" : "wedding",
  "user" : ObjectId("5d4fedfd63dc55207cf128ff"),
  "date" : ISODate("2019-09-24T20:00:00.000Z"),
  "steps" : [
  {
    "_id" : ObjectId("5d57b6ad754e9d1ec0c123e7"),
    "title" : "home",
    "startDate" : ISODate("2019-06-27T20:00:00.000Z"),
    "endDate" : ISODate("2019-07-21T20:00:00.000Z"),
    "isDone" : false,
    "state" : 2
  },
  {
    "_id" : ObjectId("5d57b6ad754e9d1ec0c123e8"),
    "title" : "guests",
    "startDate" : ISODate("2019-07-07T20:00:00.000Z"),
    "endDate" : ISODate("2019-07-30T20:00:00.000Z"),
    "isDone" : false,
    "state" : 2
  },
  {
    "_id" : ObjectId("5d57b6ad754e9d1ec0c123e9"),
    "title" : "car",
    "startDate" : ISODate("2019-07-11T20:00:00.000Z"),
    "endDate" : ISODate("2019-07-20T20:00:00.000Z"),
    "isDone" : false,
    "state" : 2
  }
]
};

the aggregation I have so far: 

const now = new Date();
Event.aggregate([
    {
      $match: { date: { $gte: now } },
    },
    {
      $unwind: '$steps',
    },
    {
      $match: { 'steps.startDate': { $lte: now }, 'steps.endDate': { $gte: now }, 'steps.state': { $ne: 1 } },
    },
  ])

I have searched the docs but I did not find anything.

Arootin Aghazaryan
  • 779
  • 7
  • 15
  • do you want to "find events that some steps of those events meet conditions" or "find events and only steps of those events that meet conditions"... i mean, you can just find events based on steps conditions, and then filter events of that events manualy (by javascript `Array.filter`). is it the case? – yaya Sep 01 '19 at 16:19
  • something like this: https://stackoverflow.com/q/57745539/4718434 (first get events that meets critica, and then filter their events again as well.) – yaya Sep 01 '19 at 16:23
  • @yayapro "find events and only steps of those events that meet conditions" is the case. the problem is with performance, I simply can not get all events and then loop through all of them. – Arootin Aghazaryan Sep 01 '19 at 16:28
  • you get it wrong. i didn't say that get all events. i said "get matched events first, and then filter their steps" – yaya Sep 01 '19 at 16:30
  • I need to update the state of each step according to start and end date. I am using agenda for creating a cron job to update the states at midnight. so it basically searches for every event that the date is not passed yet and update step states accordingly. that causes the events that match the condition to be a large count. – Arootin Aghazaryan Sep 01 '19 at 16:36
  • noooo, not only `{ date: { $gte: now } },`, you can also apply conditions for subdocuments, like: `Event.find({ $and: [ date: { $gte: now }, 'steps.startDate': { $lte: now }, 'steps.endDate': { $gte: now }, 'steps.state': { $ne: 1 } ]}` (i may have syntax error or etc, but you can do something similar.) – yaya Sep 01 '19 at 17:10
  • I have tried that and it returns the whole event with all the steps included. it does not filter the steps array. – Arootin Aghazaryan Sep 01 '19 at 17:25
  • then use https://stackoverflow.com/a/12241930/4718434 to filter steps array in events – yaya Sep 01 '19 at 17:27
  • i just checked it in real sample project, my last suggetion didn't work (it only find 1 subdocument, not all). i just submitted an answer and it worked, and i added the full sample project that i tested in. but i'm not sure about performance , perhapse there is a better way with better performance. – yaya Sep 01 '19 at 19:50

1 Answers1

0

For update sub documents based on some critica, both in main document and sub document:

  var now = new Date()
  Events.find({
       date: { $gte: now }, 'steps.startDate': { $lte: now }, 'steps.endDate': { $gte: now }, 'steps.state': { $ne: 1 } 
    },
    (err, events) => {
        events.forEach(event => {
            var steps = event.steps.filter(step => (step.startDate < now && step.endDate > now && step.state != 1));
            steps.forEach(step => {step.state = 1;})
            event.save((err, event_)=>{console.log('event updated: ', event_)})
        })
    }
  )

Full sample project that i tested it in (its not live): https://codepen.io/ya3ya6/pen/xxKLNNG?editors=0010

(Help : you should run this code 2 time, first time set init to true, to create some sample data. and second time, set init to false to run the actual query. then you can check your database datas in mongodb, to validate the results.)

yaya
  • 7,675
  • 1
  • 39
  • 38
  • Thanks for your answer. This is pretty much what I am doing now, but with the database growing larger this is gonna hurt with the performance. That's why I am trying to at least do it using just a query. – Arootin Aghazaryan Sep 02 '19 at 07:45
  • @ArootinAghazaryan no problem. aggree that its not performance wise. a workaround would be deleting that events (by their ids and using `$in`) in 1 query and insert them again with `insertMany` with 1 query. so it will be just 2 queries. but actually don't know a better way. – yaya Sep 02 '19 at 07:50
  • @ArootinAghazaryan also you did mention in your question `so I can use the save() function on each`, so i assumed that you are ok with multiple queries (note that the things like `events.forEach` and `event.steps.filter` and `steps.forEach` execute in `ram` and they are not queries(while mongoose queries run on filesystem wich is lot lot more slower)) (suppose your database have 10 mathing events, this answer has 1 query at first to find them, and 10 query to save them. so it would be 11) – yaya Sep 02 '19 at 07:54