我需要使用$ cond合并differnetnet列,而我需要编写的一个$ cond如下:
I need to use $cond to combine differenet column, and one $cond I need to write is as following:
create_widget: { $sum:{ $cond:[{$and: [ {$eq: ['$Method', 'POST']}, {Url:{$regex: /.*\/widgets$/}} ]}, 1, 0] } }这段代码似乎不正确,正则表达式不能放在这里.还有其他方法吗?我想匹配Url和正则表达式,并将代码放在$ cond下.
and this code is not right, it seems, regular expression can not be put here.Is there any other way to do this? I want to match Url and regular expression and put the code under $cond.
样本数据显示为
{"BrandId":"a","SessionId":"a1","Method":"POST","Url":"/sample/widgets"} {"BrandId":"a","SessionId":"a2","Method":"POST","Url":"/sample/blog"} {"BrandId":"b","SessionId":"b1","Method":"PUT","Url":"/sample/widgets"}我编写的整个代码如下:
The whole code I wrote is as following:
db.tmpAll.aggregate([ {$group: { _id: {BrandId:'$BrandId'}, SessionId: {$addToSet: '$SessionId'}, create_widget: { $sum:{ $cond:[{$and: [ {$eq: ['$Method', 'POST']}, {} ]}, 1, 0] } } }}, {$group: { _id: '$_id.BrandId', distinct_session: {$sum: {$size: '$SessionId'}}, create_widget: {$sum: '$create_widget'} }} ]);示例代码的预期结果是
{ "_id" : "a", "distinct_session" : 2, "create_widget" : 1 } { "_id" : "b", "distinct_session" : 1, "create_widget" : 0 }推荐答案
对于 MongoDB 4.2 和较新的生产版本,以及在4.1.11和较新的开发版本中,使用 $regexMatch ,它是 $regexFind ,可用于正则表达式匹配和捕获.
For MongoDB 4.2 and newer production releases, and in the 4.1.11 and newer development versions, use $regexMatch which is a syntactic sugar on top of $regexFind which can be used for regex matching and capturing.
db.tmpAll.aggregate([ { "$group": { "_id": { "BrandId": "$BrandId", "SessionId": "$SessionId" }, "widget_count": { "$sum": { "$cond": [ { "$and": [ { "$eq": ["$Method", "POST"] }, { "$regexMatch": { "input": "$Url", "regex": /widget/ } } ] }, 1, 0 ] } }, "session_count": { "$sum": 1 } } }, { "$group": { "_id": "$_id.BrandId", "create_widget": { "$sum": "$widget_count" }, "distinct_session": { "$sum": "$session_count" } } } ]);
此 SERVER-8892-使用中,JIRA尚未解决$regex作为$cond 中的表达式.但是,作为解决方法,对于不具有上述功能的MongoDB较早版本,请在聚合管道中使用以下解决方法.
There is an open JIRA issue for this SERVER-8892 - Use $regex as the expression in a $cond. However, as a workaround, For older MongoDB versions which do not have the above features, use the following workaround in your aggregation pipeline.
它使用> $substr 运算符,位于 $project 运算符阶段提取URL的一部分,并作为正则表达式的变通方法. :
It uses the $substr operator in the $project operator stage to extract the part of the URL and acts as a workaround for the regex. :
db.tmpAll.aggregate([ { "$group": { "_id": { "BrandId": "$BrandId", "SessionId": "$SessionId" }, "widget_count": { "$sum": { "$cond": [ { "$and": [ { "$eq": ["$Method", "POST"] }, { "$eq": [ { "$substr": [ "$Url", 8, -1 ] }, "widget"] } ] }, 1, 0 ] } }, "session_count": { "$sum": 1 } } }, { "$group": { "_id": "$_id.BrandId", "create_widget": { "$sum": "$widget_count" }, "distinct_session": { "$sum": "$session_count" } } } ]);输出
/* 1 */ { "result" : [ { "_id" : "a", "create_widget" : 1, "distinct_session" : 2 }, { "_id" : "b", "create_widget" : 0, "distinct_session" : 1 } ], "ok" : 1 }更多推荐
是否可以在MongoDB中的$ cond中编写正则表达式
发布评论