MongoDB Dates in Date and String format for the same column

Question

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
         }
      }
   ]
}

@bimjhi there is no such pattern, I need to check if records with a specified date exist. — Taras Martsyniak
– Taras Martsyniak, Commented May 4, 2021 at 18:57
Are you looking for $toDate to convert string to date and searc? docs.mongodb.com/manual/reference/operator/aggregation/toDate — Deepak Singh
– Deepak Singh, Commented May 4, 2021 at 19:01
Irrespective of the field with different data types and a query filter, you can only compare data of the same type. That means you need to convert your data to a type (one type only within the query) and perform your match. — prasad_
– prasad_, Commented May 5, 2021 at 5:22
You can. Make sure the format is something like 'yyyy-mm-dd' (the string format need to be with year, month, day, hour, ...). — prasad_
– prasad_, Commented May 5, 2021 at 7:02

Taras Martsyniak · Accepted Answer · 2021-05-06 07:27:45Z

2

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:

answered May 6, 2021 at 7:27

Taras Martsyniak

915 bronze badges

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

Collectives™ on Stack Overflow

MongoDB Dates in Date and String format for the same column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related