MongoDB Dates in Date and String format for the same column

user_10786674 0
Viewed 191

I have a collection where the same column has Date and String format dates for different records, with the same ISO pattern like 2021-04-22T14:10:48.751779Z.

And I need to build a unified query for search, with such =, >, < options for search. The second problem is an input search query has a different pattern ("2021-04-22")

So the question is:

  • can I write some kind of transformation of existing table data before the search, for example from Date to String, or in case I have a string value this transformation would not cause any exception. In this case, I would be able to perform a search by $eq, $lt, $gt, and $regex query options. I`m not considering casting strings to dates even if this is possible because my input did not have "14:10:48.751779Z" this part of the date and query would not find anything because of dates would not match. For this case, $regex looks like the only solution.

Or your any other suggestions would be considered.

Current query which not satisfies case when DB column is String type:

{
   "aggregate":"collection_name",
   "pipeline":[
      {
         "$match":{
            "$and":[
               {
                  "some_column":{
                     "$eq":"some_value"
                  }
               },
               {
                  "date_column_with_string_or_date_type":{
                     "$gt":{
                        "$date":"1980-01-01T00:00:00Z"
                     }
                  }
               }
            ]
         }
      },
      {
         "$project":{
            "_id":1
         }
      }
   ]
}

aggregation-framework mongodb
1 Answers

Solved by using $expr and $toString This works with Date And String types of "date_column"

$match: {
    $expr: {
        $gt : [
        { $toString:"$date_column" },
        "2021-04-22"
        ]
    }
}

operator:

user_107866740
Related