How to compare field of the last item in an embedded array with a know value using PyMongo

Working with Data
1

I have records in a collection with single record like this:`` rau_record = { "_id" : ObjectId("646fdefa2601c85dd7092337"), "BOX-SN" : "3-6213905", "RAUs" : [ { "contents" : "some content", "date" : datetime.strptime("2024-01-15T22:19:38.679", "%Y-%m-%dT%H:%M:%S.%f") }, { "contents" : "...", "date" : datetime.strptime("2024-01-26T23:00:33.802", "%Y-%m-%dT%H:%M:%S.%f") } ] }

I am trying to find out whether any record in the collection having the ordered RAUs array’s last item’s date after a known date.

I have tried follow 2 methods, but neither works. Can someone help? Thank you very much!

``query = {‘$expr’: {‘$gte’: [{‘$arrayElemAt’: [“$RAUs.date”, -1]}, src_last_change_date]}}

query = {‘RAUs.date’: {‘$gte’: src_last_change_date}}, {‘RAUs’: {‘$slice’: -1}}

count = change_collection.count_documents(query, limit=1)
return count > 0
`

2

Hello,

I am a beginner, just started learning MongoDB. As I can try at the moment, please see one of the ways of doing it. Request you may please wait for a better solution by someone else.

Sample data:
db.test.find();
[
{ _id: ‘1’, dates: [ 1, 2 ] },
{ _id: ‘2’, dates: [ 1, 2, 3 ] },
{ _id: ‘3’, dates: [ 1, 2, 4 ] }
]

Query 1:
Explanation: Accessing the last array item and then checking it with the given date.
Step 1. $slice : -1 gives the last array item with respect each document.
Step 2. map : It maps the eligible items in the result in step 1 as per given condition.

db.test.find({},{“dates”:{“$slice”:-1}}).map(function(r){ return r.dates > 2 && r}).toArray();

Output:
[ false, { _id: ‘2’, dates: [ 3 ] }, { _id: ‘3’, dates: [ 4 ] } ]

Query 2:
Explanation : Firstly querying array items with the given date, and then accessing the last item in the resulting array with respect to each document.

test> db.test.find({ “dates” : { “$gt” : 2 }},{“_id”:1,“dates”:{“$slice”:-1}});

Output:
[ { _id: ‘2’, dates: [ 3 ] }, { _id: ‘3’, dates: [ 4 ] } ]

Thanks
WeDoTheBest4You

3

Hi,

Thank you very much for your help.
For your query 1, what I am trying to do is to get the result by query only w/o processing returned intermediate data.
For your query 2, it’s similar to my 2nd query too except that the date is a field of the array item and I am trying to get the count directly with limit of 1 and so it stops once one found. It gives me an ValueError: ‘session’ argument must be a ClientSession or None which I had no clue. I tried find() to return cursor and then call cursor.count(), it returns error too.

Thanks again!

Mary

4

Looks like count_documents doesn’t work well with $slice and so the exception about Session. And cursor.count() got depreciated, using find() to return a cursor, and then len(list(cursor) will work though not handy.
BTW, the $slice executes after the query match, not executed before the match and so it’s different from getting last item of the array and match against the date.

I got this working, but it checks items in the array from the beginning instead of only last one with the items in time order already.

`count = collection.count_documents({‘RAUs’: {“$elemMatch”: {‘date’: {‘$gte’: src_last_change_date}}}}, limit=1)``

Will appreciate if someone can tell the most efficient way to do it.

Thank you!

Mary

5

Hello,

You can try using the “$last” operator. Here is the sample query to achieve your use case -

{
  "$expr": {
    "$gte": [
      {
        "$last": "$RAUs.date"
      },
      yourInputDate
    ]
  }
}
1 Like
6

Thank you. It requires MongoDB 4.4+? I may need upgrade my version. Let me try.

7

Yes, “$last” operator is supported in version 5.0 or later.

8

Good, it works! Thank you.

1 Like