MongoDB query to find text in third level array of objects-CodePudding

I have a Mongo collection that contains data on saved searches in a Vue/Laravel app, and it contains records like the following:

{ 
    "_id" : ObjectId("6202f3357a02e8740039f343"), 
    "q" : null, 
    "name" : "FCA last 3 years", 
    "frequency" : "Daily", 
    "scope" : "FederalContractAwardModel", 
    "filters" : {
        "condition" : "AND", 
        "rules" : [
            {
                "id" : "awardDate", 
                "operator" : "between_relative_backward", 
                "value" : [
                    "now-3.5y/d", 
                    "now/d"
                ]
            }, 
            {
                "id" : "subtypes.extentCompeted", 
                "operator" : "in", 
                "value" : [
                    "Full and Open Competition"
                ]
            }
        ]
    },

The problem is the value in the item in the rules array that has the decimal.

"value" : [
    "now-3.5y/d", 
    "now/d"
]

in particular the decimal. Because of a UI error, the user was allowed to enter a decimal value, and so this needs to be fixed to remove the decimal like so.

"value" : [
    "now-3y/d", 
    "now/d"
]

My problem is writing a Mongo query to identify these records (I'm a Mongo noob). What I need is to identify records in this collection that have an item in the filters.rules array with an item in the 'value` array that contains a decimal.

Piece of cake, right?

Here's as far as I've gotten.

myCollection.find({"filters.rules": })

but I'm not sure where to go from here.

UPDATE: After running the regex provided by @R2D2, I found that it also brings up records with a valid date string , e.g.

        "rules" : [
            {
                "id" : "dueDate", 
                "operator" : "between", 
                "value" : [
                    "2018-09-10T19:04:00.000Z", 
                    null
                ]
            },

so what I need to do is filter out cases where the period has a double 0 on either side (i.e. 00.00). If I read the regex correctly, this part

[^\.]

is excluding characters, so I would want something like

[^00\.00]

but running this query

db.collection.find( {
         "filters.rules.value": { $regex:  /\.[^00\.00]*/ } 
         } )

still returns the same records, even though it works as expected in a regex tester. What am I missing?

CodePudding user response：

To find all documents containing at least one value string with (.) , try:

    db.collection.find( {
         "filters.rules.value": { $regex:  /\.[^\.]*/ } 
         } )

Or you can filter only the fields that need fix via aggregation as follow:

 [direct: mongos]> db.tes.aggregate([ {$unwind:"$filters.rules"}, {$unwind:"$filters.rules.value"}, {$match:{ "filters.rules.value": {$regex:  /\.[^\.]*/    } }}  ,{$project:{_id:1,oldValue:"$filters.rules.value"}}       ])
[
 { _id: ObjectId("6202f3357a02e8740039f343"), oldValue: 'now-3.5y/d' }
]
[direct: mongos]>

Later to update those values:

 db.collection.update({
   "filters.rules.value": "now-3.5y/d"
   },
   {
     $set: {
      "filters.rules.$[x].value.$": "now-3,5y/d-CORRECTED"
    }
   },
   {
     arrayFilters: [
   {
     "x.value": "now-3.5y/d"
   }
  ]
  })

playground