Error using $match to a $substr

[Shivaji Huttler]

MongoDB query language code as follows:

db.transactions.aggregate( [
  { $match : { { $concat: [ { $substr : ["$mqt_data.user_transaction_time",0 , 4] }, " - ", { $substr : ["$mqt_data.user_transaction_time",5 , 2] }  ] }  :  { "2018 - 02" } } },
  { $project : {"mqt_data.gpa.available_balance": 1, "mqt_data.user_transaction_time": 1, "company_id": 1, "_id": 0 } },
  { $sort : {"mqt_data.user_transaction_time": 1} },
  { $limit : 1 }
] )

The query runs fine if I exclude the $match line which looks in the field mqt_data.user_transaction_time of type string for the literal "2018 - 02". I have tried to determine where the error occurs by simplifying the $match line such as removing the $concat and just using one $substr against "2018" but it still fails. If I exclude the $substr all together it works : { $match : { "mqt_data.user_transaction_time" : "2017-11-14T18:36:45Z" } } // works !!!

Any ideas?

[Wan Bachtiar]

which looks in the field mqt_data.user_transaction_time of type string for the literal “2018 - 02”

Hi Shivaji,

Could you elaborate on what you’re trying to achieve with :

• MongoDB server version
• Example document(s)
• Desired output
• The error message that you’re getting

If you’re just trying to find documents where mqt_data.user_transaction_time field has value of “2018 - 02” then you can just use:

{ "$match" : { "mqt_data.user_transaction_time" : "2018 - 02" } }

Note that the format of $match equality condition is { <field1>: <value1>, ... }, and you’re example is missing the field name.

Regards,
Wan.

​

[Shivaji Huttler]

Wan,

Using MongoDB 3.2. The error message is from Studio 3T client tool and is convoluted. The field, mqt_data.user_transaction_time, is of type string but has the same format as an ISODate. So that is why I need the substring function along with the concatenation. How can I correct the $match syntax since the "field" that I want to compare with requires just the date component parts of YY - MM (string type and not date type). 

The literal that I am trying to match to,  { "2018 - 02" }, will eventually become a report parameter.

My goal for this aggregate query is to find the earliest available balance figure for a specified month. For example, if the first transaction done in February occurs on 3 February 2018, then I would want the associated balance for that date.

[Wan Bachtiar]

The field, mqt_data.user_transaction_time, is of type string but has the same format as an ISODate.

Hi Shivaji,

You should consider converting the values to Date. Store the values in the format the data is supposed to represent. If the field is stored in Date then you could use $redact, example:

db.coll.aggregate([{"$redact":{
                        "$cond":[
                            {"$and":[
                                {"$eq":[ {"$year" :"$mqt_data.user_transaction_time"}, 2018] },                  
                                {"$eq":[ {"$month":"$mqt_data.user_transaction_time"}, 2] },              
                            ]},
                            "$$KEEP", "$$PRUNE"]
                        }} 
]);

How can I correct the $match syntax since the “field” that I want to compare with requires just the date component parts of YY - MM (string type and not date type).

If it’s String i.e. {"user_transaction_time": "2018-08-20T08:52:55.393Z"}, you can use $regex:

db.coll.aggregate([{"$match":{"mqt_data.user_transaction_time": {"$regex": "^2018-02"}}}]);

In MongoDB v3.6+, you can use $dateFromString to convert the value from string into Date then use the $redact example above:

db.coll.aggregate([
        {"$addFields":{
            "newdate": {
                "$dateFromString": {"dateString":"$mqt_data.user_transaction_time"}
        }}},      
        {"$redact":{
            "$cond":[
                {"$and":[
                    {"$eq":[ {"$year":"$mqt_data.user_transaction_time"}, 2018] },                  
                    {"$eq":[ {"$month":"$mqt_data.user_transaction_time"}, 2] },
                ]}, 
                "$$KEEP", "$$PRUNE"]          
        }} 
]);

You may also find Aggregation Pipeline Optimization a useful reference.

Using MongoDB 3.2

Please note that MongoDB v3.2 has reached it’s end of life last month (September 2018). If this is a new deployment please see current stable MongoDB version.

Regards,
Wan.

​