CODEWITHSUNDEEP
mongodbpymongo
b2d2
b2d2

Reputation: 79

How to filter a command cursor in mongodb

I am fairly new to NoSQL databases. I am having difficulties with a simple problem

I have a database with dates which are strings. I want to filter my collection so that I can get the elements which date is greater than a specific date. To solve the problem I converted my string dates into date objects and added that as a field using aggregate $addFields method.

collection.aggregate([{
"$addFields": {
  "CA": {
    "$convert": {
      "input": {
        "$substr": ["$headers.Date", 5, 20]
      },
      "to": "date"
    }
  }
}}])

I first tried the code above and it works. There is "CA" field with date objects but I wanted to filter them using a find and filter but since it was a command cursor the methods were not available. That's why I tried the code below and didn't work as well. It is basically a modified version of the code above which I added $filter

collection.aggregate([{
  "$filter": {
     "input": {
        "$addFields": {
           "CA": {
              "$convert": {
                 "input": {
                    "$substr": ["$headers.Date", 5, 20]
                 },
                 #"$headers.Date"
                 "to": "date"
              }
           }
        }
     },
     'as': 'db',
     "cond": {
        "$lt": ["$db.CA", datetime.now()]
     }
  }}])

Edit: As requested I added sample element below

{'_id': ObjectId('e27'),
 'body': "Body",
 'subFolder': 'notes_inbox',
 'mailbox': 'bass-e',
 'filename': '450.',
 'headers': {'X-cc': '',
  'From': '[email protected]',
  'Subject': 'Re: information',
  'X-Folder': '\\Notes inbox',
  'Content-Transfer-Encoding': '7bit',
  'X-bcc': '',
  'To': '[email protected]',
  'X-Origin': 'Bass-E',
  'X-FileName': 'ebass.nsf',
  'X-From': 'Miel Sim',
  'Date': 'Tue, 14 Nov 2000 08:22:00 -0800 (PST)',
  'X-To': 'Eric Bass',
  'Message-ID': '<6884142.1075854677416.JavaMail.evans@thyme>',
  'Content-Type': 'text/plain; charset=us-ascii',
  'Mime-Version': '1.0'}}

Upvotes: 2

Views: 1665

Answers (1)

mickl
mickl

Reputation: 49945

You can use $filter when you need to filter nested array, in your case since you have a single value you can simply use $match, try:

db.collection.aggregate([
    {
        "$addFields": {
            "CA": {
                "$convert": {
                "input": {
                    "$substr": ["$headers.Date", 5, 20]
                },
                    "to": "date"
                }
            }
        }
    },
    {
        $match: {
            CA: { $lt: ISODate() } // or datetime.now()
        }
    }
])

If you don't want to add new field you can use $expr operator:

db.collection.aggregate([
    {
        $match: {
            $expr: {
                $lt: [ { $convert: { input: { "$substr": ["$headers.Date", 5, 20] }, to: "date" } }, ISODate() ]
            }
        }
    }
])

Upvotes: 1

Related Questions