[SERVER-13902] Reverse regex functionality for queries Created: 11/May/14  Updated: 06/Dec/22  Resolved: 23/Jun/17

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Fermín Galán Márquez Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 22
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Microsoft PowerPoint Why reverse regex is needed.pptx    
Issue Links:
Duplicate
duplicates SERVER-11947 Add a regex expression to the aggrega... Closed
Related
is related to SERVER-11947 Add a regex expression to the aggrega... Closed
Assigned Teams:
Query
Participants:

 Description   

The $regex operator allows to search documents having a field which match a given regular expresion passed in the query. However, it would be interesting to have also the opossite: search documents passing a text in the query which is evaluated for matching to a field in the collection storing a regular expresión.

I wonder if this feature/improvement is already in MongoDB roadmap as it seems it would be useful for several use cases. See for example:



 Comments   
Comment by Asya Kamsky [ 28/Jan/19 ]

As of 3.6 we allow using any aggregation expression in find using $expr so once SERVER-11947 is done I believe this ticket would be done as well hence closing it as a duplicate.

Comment by Sebastian Maier [ 25/Jan/19 ]

SERVER-11947 is about providing such a feature for aggregation. I'd like argue it makes sense to have it in the query interface as well.

Thomas described it quite good. Naming wise it could be `$regexApply` or maybe more explicit: `$regexReverse`.

 

Comment by Charlie Swanson [ 23/Jun/17 ]

I think the proposal in SERVER-11947 would allow for this, so I'm closing this ticket as a duplicate. Please re-open if that is not the case.

Comment by Michal Idzikowski [ 11/Jun/14 ]

Next example that show it's useful:

I have telephony system (on SIP protocol) and I look into database for route where call should be dispatched. If someone calls number 77656 it should go via gateway1, if 195959 then through gateway2. I have a lot of regex'es and 12 gateways so the routing table in MySQL have now about 8k records. I cache result for particular number, but it doesn't help, becuase it rarely happens that two people call same number.

MongoDB is much faster and such reverse regex functionality will help people like me lowering latency and resources usage.

Example data:

ID 	gateway		regex
1	gateway1	^77[0-9]{7}$
2	gateway2	^77[0-9]{3,6}$
3	gateway2	^1[79][0-9]+

Example query:

SELECT gateway FROM routes WHERE 195959 REGEXP regex

sqlfiddle

Comment by Fermín Galán Márquez [ 06/Jun/14 ]

I'm attaching some slides explaing why reverse regex is useful (use case)

Comment by Fermín Galán Márquez [ 12/May/14 ]

Yes, it does. You have described precisely the functionality I refer to.

Thanks!

Comment by Thomas Rueckstiess [ 12/May/14 ]

Hi Fermin,

Just to clarify. You mean documents store regular expressions (here in the url field):

db.docs.insert({category: "sport - general", url: /^sport/})
db.docs.insert({category: "sport - football", url: /^sport\/football/})

And you want to pass in a string as a query and only want the documents whose regular expression matches the string. Perhaps with a special operator (let's call it $regexApply for this example):

> db.docs.find( { url: { $regexApply: "sport/football/nfl" } } )
{ "_id" : ObjectId("537102dd5a032601a820adb4"), "category" : "sport - general", "url" : /^sport/ }
{ "_id" : ObjectId("537102dd5a032601a820adb5"), "category" : "sport - football", "url" : /^sport\/football/ }
 
> db.docs.find( { url: { $regexApply: "sport/basketball/ncaa"} } )
{ "_id" : ObjectId("537102dd5a032601a820adb4"), "category" : "sport - general", "url" : /^sport/ }

Does that describe the feature you are requesting?

Thanks
Thomas

Generated at Thu Feb 08 03:33:15 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.