Question 117664 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to check only top 1 in nested mongo array

at the moment my notification documents has an events property which is an array of event. Each event has a status and a date. When querying notifications, it needs to check if the top status is opened.

Valid object where most recent event status is opened –

{
    "subject" : "Hello there",
    "events" : [
        {
            "status" : "opened",
            "date" : 2020-01-02 17:35:31.229Z 
        },
        {
            "status" : "clicked",
            "date" : 2020-01-01 17:35:31.229Z 
        },
   ]
}

Invalid object where status isn’t most recent

{
    "subject" : "Hello there",
    "events" : [
        {
            "status" : "opened",
            "date" : 2020-01-01 17:35:31.229Z 
        },
        {
            "status" : "clicked",
            "date" : 2020-01-02 17:35:31.229Z 
        },
   ]
}

At the moment I have the query that can check if any event has the status opened, but I’m unsure how to query only the top 1 and sorted by the dates of a nested query. Any help would be greatly appreciated.

var filter = Builders<Notification>.Filter.Empty;

filter &= Builders<Notification>.Filter.Regex("events.event", new BsonRegularExpression(searchString, "i"));

var results = await collection.FindSync(filter, findOptions).ToListAsync();
Tags:

3

Answers


  1. 0

    In order to get only the latest event you can use $reduce to iterate over the events and compare each one to the temporarily latest:

    db.collection.aggregate([
  {
    $addFields: {
      latestEvent: {
        $reduce: {
          input: "$events",
          initialValue: {status: null, date: 0},
          in: {
            $mergeObjects: [
              "$$value",
              {
                $cond: [
                  {
                    $gt: [{$toDate: "$$this.date"}, {$toDate: "$$this.value"}]
                  },
                  "$$this",
                  "$$value"
                ]
              }
            ]
          }
        }
      }
    }
  }
])

    See how it works on the playground example

    Login or Signup to reply.
  2. 0

    for multiple documents, the result return only correct documents
    example

    db.collection.aggregate([{
    $addFields: {
        lastevent: {
            $filter: {
                input: '$events',
                as: 'element',
                cond: {$eq: ['$$element.date',{$max: '$events.date'}]}
            }
        }
    }
}, {
    $match: {
        'lastevent.status': 'opened'
    }
}])
    Login or Signup to reply.
  3. 0

    I am a fan of not using an axe for everything, even if it is a good one 🙂

    So i take it the events being disorderly is a rare thing, so we don’t need to spend a lot of resources to weed out those up front as they will be few.

    So my take is to get all the opened ones and use simple .net iteration to remove the few that may be, leaving a nice and orderly and easily maintainable method.

    public List<Notification> GetValidSubjectStatusList(IMongoCollection<Notification> mongoCollection){
    
    var builder = Builders<Notification>.Filter;
    var filter = builder.Eq(x => x.Events.FirstOrDefault().Status, "opened");
    var listOf = mongoCollection.Find(filter).ToList();
    var reducedList = new List<Notification>();
    foreach(var hit in listOf){
        if(hit.Events.Any() 
                        && hit.Events.First()
                                        .Date.Equals(hit.Events
                                            .OrderByDescending(x => x.Date)
                                            .FirstOrDefault()
                                        ))
            {
                reducedList.Add(hit);
            }
    }
    return reducedList;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search