How to get a MongoDB BsonDocument from a collection based on last timestamp and some other filters using C#

Question

For a building security system I want to get the last swiped card time for a particular student based on their id serial number. I am using this code

var _collection = database.GetCollection<BsonDocument>("entrycard_swipetimes");
var filter = Builders<BsonDocument>.Filter.Eq("studentid", "stu-1234") & Builders<BsonDocument>.Filter.Eq("Carddetails.LastSwipeTimestamp", "2021-11-25T10:50:00.5230694Z");
var doc = _collection.Find(filter).FirstOrDefault();

This is a snippet of the json document

{
  "studentid"; "stu-1234",
  "Carddetails": 
   { 
      "LastSwipeTimestamp": "2021-11-25T10:50:00.5230694Z"
    }
}

The code above works and I get a record, but I need to know the exact time string in advance. How can get only the last time a particular student swiped their card? How do I get the last timestamp of all the students in the system and have it paged by say 20 students at a time for the last 30 days?

Buzz Moschetti · Accepted Answer · 2021-11-30 15:44:24Z

1

Modify Filter and add a sort thusly:

var filter = Builders<BsonDocument>.Filter.Eq("studentid", "stu-1234");

var doc = _collection.Find(filter).Sort("{\"Carddetails.LastSwipeTimestamp\":-1}").FirstOrDefault();

Highly recommend that you change Carddetails.LastSwipeTimestamp to be a real datetime type instead of an ISO8601 string.

edited Nov 30, 2021 at 15:44

answered Nov 29, 2021 at 20:34

Buzz Moschetti

7,6433 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user20358 Over a year ago

I am currently storing them as a document using the incoming json string data like so var bsonDoc = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<BsonDocument>(data); var document = new BsonDocument().AddRange(bsonDoc); _collection.InsertOneAsync(document); How do I store the date in the json string data as Date type?

Buzz Moschetti Over a year ago

Check out stackoverflow.com/questions/2188585/… After you create document, navigate through the document (a Dictionary type) to where LastSwipeTimestamp is and replace it with the parsed datetime version before calling InsertOneAsync.

Buzz Moschetti Over a year ago

NOTE: MongoDB will only store milliseconds, not microseconds, so a successful parse of 2021-11-25T10:50:00.5230694Z will end up in MongoDB as ISODate(2021-11-25T10:50:00.523Z)

user20358 Over a year ago

Thanks. Can you add to your answer how to get the last timestamp for every student ? that would make your answer complete.

Buzz Moschetti Over a year ago

? The Sort after the Find in my answer above coupled with FirstOrDefault will return the last (most recent) timestamp.

|

Yonatan · Accepted Answer · 2021-11-29 21:31:01Z

1

To get the last time a particular student (studentId) swiped their card:

var filter = Builders<BsonDocument>.Filter.Eq("studentid", studentId);
var doc = collection
    .Find(filter)
    .SortByDescending(bson => bson["Carddetails.LastSwipeTimestamp"])
    .FirstOrDefault();

To get the last timestamp of all the students in the system and have it paged by pageSize students at a time for the last lastDaysCount days:

var doc = collection
    .Aggregate()
    .Group(new BsonDocument
    {
        { "_id", "$studentid" },
        {
            "LastSwipeTimestamp", new BsonDocument
            {
                { "$max", "$Carddetails.LastSwipeTimestamp" }
            }
        },
    })
    .Match(bson => bson["LastSwipeTimestamp"] > DateTime.Now.AddDays(-lastDaysCount))
    .Skip((page - 1) * pageSize)
    .Limit(pageSize)
    .ToList();

But for this to work, you should store your dates as Date type.

answered Nov 29, 2021 at 21:31

Yonatan

6407 silver badges15 bronze badges

1 Comment

user20358 Over a year ago

I am currently storing them as a document using the incoming json string data like so var bsonDoc = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<BsonDocument>(data); var document = new BsonDocument().AddRange(bsonDoc); _collection.InsertOneAsync(document); How do I store the date in the json string data as Date type?

Collectives™ on Stack Overflow

How to get a MongoDB BsonDocument from a collection based on last timestamp and some other filters using C#

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related