Use $Redact with a regular expression

Question

In an aggregation pipeline, I am trying to filter some elements of an array of objects, based on the value of a field in this object.

Let's say that I have this entry:

 {
   "_id": "5b8911d346d19645f8a66bf4",
   "title": "test task",
   "creation_date": "2018-08-31T10:00:51.598Z",
   "logs": [
    {
       "_id": "5b89126c46d19645f8a66bfb",
       "content": "Running"
    },
    {
       "_id": "5b89128646d19645f8a66bfd",
       "content": "Stopping"
    },
    {
       "_id": "5b89128646d19645f8a66bfd",
       "content": "Stopped"
    }
   ]
 }

My objectif is to filter only the logs containing the stop word in their content:

 {
   "_id": "5b8911d346d19645f8a66bf4",
   "title": "test task",
   "creation_date": "2018-08-31T10:00:51.598Z",
   "logs": [
    {
       "_id": "5b89128646d19645f8a66bfd",
       "content": "Stopping"
    },
    {
       "_id": "5b89128646d19645f8a66bfd",
       "content": "Stopped"
    }
   ]
 }

I tried to use $redact to eliminate all the logs that does not contain the stop word:

$redact: {
   $cond: {
      if: { $match: { "logs.content": { $regex: "stop", $options: 'i' }}},
      then: "$$KEEP",
      else: "$$PRUNE"
   }
}

but I keep getting the error:

Unrecognized expression '$match'

`$regex` support in aggregation expression won't be available until 4.1 release. You can look at the [jira issue](https://jira.mongodb.org/browse/SERVER-11947). You can use the answer provided https://stackoverflow.com/questions/43016813/search-array-of-nested-objects-by-given-field/43018079#43018079. — s7vr, Aug 31 '18 at 13:23
Possible dupe of https://stackoverflow.com/questions/35750920/regex-as-filter-in-projection — s7vr, Aug 31 '18 at 13:25

Ashh · Accepted Answer · 2018-08-31T17:59:25.720

You can try below aggregation

db.collection.aggregate([
  { "$addFields": {
    "logs": {
      "$filter": {
        "input": "$logs",
        "cond": {
          "$ne": [
            { "$indexOfBytes": [
              { "$toUpper": "$$this.content" },
              { "$toUpper": "stop" }
            ]},
            -1
          ]
        }
      }
    }
  }}
])

Output

[
  {
    "_id": "5b8911d346d19645f8a66bf4",
    "creation_date": "2018-08-31T10:00:51.598Z",
    "logs": [
      {
        "_id": "5b89128646d19645f8a66bfd",
        "content": "Stopping"
      },
      {
        "_id": "5b89128646d19645f8a66bfd",
        "content": "Stopped"
      }
    ],
    "title": "test task"
  }
]

Mohit Kumar Bordia · Answer 2 · 2018-08-31T17:47:09.553

As per your requirement below query is working and it is properly tested

db.users.aggregate(

// Pipeline
[
    // Stage 1
    {
        $unwind: {
            path : "$logs",
            preserveNullAndEmptyArrays : true // optional
        }
    },

    // Stage 2
    {
        $group: {
                  _id: "$_id",
                  "title" :{$last:"$title"} , 
                      "creation_date" :{$last:"$creation_date"},
                  logs: {
                    $push: {
                      $cond: [ {$or:[{"$eq":[{ "$substr": [ "$logs.content", 0, 4 ] }, "Stop"]},{"$eq":[{ "$substr": [ "$logs.content", 0, 4 ] }, "stop"]}]},{"_id":"$logs._id","content":"$logs.content"},null]
                      }
                    }
                  }


    },

    // Stage 3
    {
        $project: {
            logs: {
                    $filter: {
                       input: "$logs",
                       as: "log",
                       cond: { $ne: [ "$$log", null ] }
                    }
                 }
        }
    },

]

// Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/

);

Thanks for the answer. I expect one problem with this solution: what if Stage 2 does not return any result? In this case the aggregation won't return the parent document neither — Strider, Aug 31 '18 at 13:53
The 'stop' keyword and all the values were given for the example sake. The `content` field could contain any value, and the search value could be anything else other than 'stop'. I believe that the `substr` with a length of `4` cannot be applied to any case of the filter — Strider, Sep 01 '18 at 19:19
Check the query which i have update i think it works in all scenario — Mohit Kumar Bordia, Sep 01 '18 at 21:00

Use $Redact with a regular expression

2 Answers2