mongodb aggregate - match $nin array regex values

Question

Must work in mongo version 3.4
Hi, As part of aggregating relevant tags, I would like to return tags that have script_url that is not contained in the whiteList array.
The thing is, i want to compare script_url to the regex of the array values.
I have this projection:

{
    "script_url" : "www.analytics.com/path/file-7.js",
    "whiteList" : [ 
        null, 
        "www.analytics.com/path/*", 
        "www.analytics.com/path/.*", 
        "www.analytics.com/path/file-6.js", 
        "www.maps.com/*", 
        "www.maps.com/.*"
    ]
}

This $match compares script_url to exact whiteList values. So the document given above passes when it shouldn't since it has www.analytics.com/path/.* in whiteList

{
    "$match": {
        "script_url": {"$nin": ["$whiteList"]}
    }
}

How do i match script_url with regex values of whiteList?

update

I was able to reach this stage in my aggregation:

{
    "script_url" : "www.asaf-test.com/path/file-1.js",
    "whiteList" : [ 
        "http://sd.bla.com/bla/878/676.js", 
        "www.asaf-test.com/path/*"
    ],
    "whiteListRegex" : [ 
        "/http:\/\/sd\.bla\.com\/bla\/878\/676\.js/", 
        "/www\.asaf-test\.com\/path\/.*/"
    ]
}

But $match is not filtering out this script_url as it suppose to because its comparing literal strings and not casting the array values to regex values. Is there a way to convert array values to Regex values in $map using v3.4?

Pass the value as a regular expression, you are passing it as a string. — D. SM
– D. SM, Commented Jul 13, 2020 at 15:54
This is the way the values are saved in the db. It's a projection. The regex values are in the array as strings. If I was able to convert them to regex values in the aggregation the match would work. Do you know how to do that? — asafg
– asafg, Commented Jul 14, 2020 at 4:47
Your question shows a document. Which projection are you referring to? — D. SM
– D. SM, Commented Jul 14, 2020 at 16:17
@D.SM this document is a result of an aggregation block. My intention is to add a $match block to filter out script_url's that exists in the whitelist array - but comparing to regex values. Saving whitelist values as regex values is not an option at the moment. — asafg
– asafg, Commented Jul 15, 2020 at 8:38

thammada.ts · Accepted Answer · 2020-07-15 16:08:18Z

4

I know you specifically mentioned v3.4, but I can't find a solution to make it work using v3.4.

So for others who have less restrictions and are able to use v4.2 this is one solution.

For version 4.2 or later only

The trick is to use $filter on whitelist using $regexMatch (available from v4.2) and if the filtered array is empty, that means script_url doesn't match anything in whitelist

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $eq: [
          {
            $filter: {
              input: "$whiteList",
              cond: {
                $regexMatch: { input: "$script_url", regex: "$$this" }
              }
            }
          },
          []
        ]
      }
    }
  }
])

Mongo Playground

It's also possible to use $reduce instead of $filter

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $not: {
          $reduce: {
            input: "$whiteList",
            initialValue: false,
            in: {
              $or: [
                {
                  $regexMatch: { input: "$script_url", regex: "$$this" }
                },
                "$$value"
              ]
            }
          }
        }
      }
    }
  }
])

Mongo Playground

answered Jul 15, 2020 at 16:08

thammada.ts

5,2653 gold badges26 silver badges35 bronze badges

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

6 Comments

asafg Over a year ago

Thanks @thammada.ts but unfortunately my system is on v3.4 and we are not planning on upgrading at the moment. It has to work on v3.4

thammada.ts Over a year ago

@asafg just a little heads-up. From mongodb.com/support-policy you can see that the support of v3.4 has ended in January 2020

thammada.ts Over a year ago

From what I can find in the documentation I would say it's not possible using v3.4. If there were some way to convert a value to regex (which there are not), you would still need $expr, to be able to use in aggregation, which is available in v3.6. The other possibility is using $where, which runs javascript on all your documents, but it doesn't support aggregation.

asafg Over a year ago

Thanks @thammada.ts. I ended up making a copy of the whiteList - regexWhiteList which holds regex values of the original. When we'll migrate to v4 we will be able to remove this unnecessarily data. Thanks for the efforts!

thammada.ts Over a year ago

@asafg great! I didn't know we could store regex in MongoDB

|

Collectives™ on Stack Overflow

mongodb aggregate - match $nin array regex values

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related