CODEWITHSUNDEEP
mongodbmongodb-querymongodb-atlas
Tnimni
Tnimni

Reputation: 53

mongodb atlas online archive custom criteria

I'm trying to create a custom criteria query, the issue is that mongo requires it to be a json document and it then translate it to a query

i want this to be the query db.reports.find({ "time_lo_res": { $lte: NumberLong(ISODate().getTime() - 1000 * 3600 * 24 * 30)}}, { "category": "user" }).sort({ "time_lo_res": 1 }) i.e. get all the docs that have time_lo_res older then 30 days and the category is user, then sort them ascending.

i managed to get this

  "time_lo_res": {
    "$lte": "NumberLong(ISODate().getTime() - 1000 * 3600 * 24 * 30)"
  },
  "category": {
    "$eq": "user"
  }
}

which is missing the sort part, can't figure out how to add it, also not sure if the quotes around the arguments doesn't break the query, they are required since it require a valid json

will really appreciate any help

Upvotes: 0

Views: 880

Answers (2)

velika12
velika12

Reputation: 151

Custom Criteria for MongoDB Online Archive is just a filter (so Mongo could understand what documents must be archived). It is not meant to hold any sorting logic.

In case time_lo_res field is Unix time (in seconds), the correct JSON object for Custom Criteria will be:

{
    "category": "user",
    "$expr": {
        "$lte": [
            "$time_lo_res",
            {
                "$toLong": {
                    "$divide": [
                        {
                            "$toDecimal": {
                                "$dateSubtract": {
                                    "startDate": "$$NOW",
                                    "unit": "day",
                                    "amount": 30
                                }
                            }
                        },
                        1000
                    ]
                }
            }
        ]
    }
}

Upvotes: 0

rickhg12hs
rickhg12hs

Reputation: 11942

Here's one way you could do it using an aggregation pipeline.

db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$lte": [
          "$time_lo_res",
          {
            "$dateSubtract": {
              "startDate": "$$NOW",
              "unit": "day",
              "amount": 30
            }
          }
        ]
      },
      "category": "user"
    }
  },
  {
    "$sort": {
      "time_lo_res": 1
    }
  }
])

Try it on mongoplayground.net.

Upvotes: 1

Related Questions