MongoDB - MySQL SUM (CASE WHEN) Equivalent?

问题:

I'm trying to do some testing with Mongo, I have figured some of the simpler MySQL queries Mongo equivalent.

I have this query that is a little more complex that I need help with...

SELECT DISTINCT dims_user,
    COUNT(DISTINCT asset_name) AS asset_count,
    COUNT(DISTINCT system_name) AS station_count,
    SUM(CASE WHEN details ='viewed' then 1 Else 0 end) AS viewed_count,
    SUM(CASE WHEN details Like 'Viewed Web%' then 1 Else 0 end) AS Web_count,
    SUM(CASE WHEN details = 'ThumbView' then 1 Else 0 end) AS ThumbView_count,
    SUM(CASE WHEN details Like 'Exported%' then 1 Else 0 end) AS Exported_count,
    SUM(CASE WHEN details Like '%Print%' then 1 Else 0 end) AS Printed_count
FROM asset_log GROUP BY dims_user;

回答1:

I would agree with Rudu here as you should try to break the details into keys of a mongo document.

The document might contain an object like this:

details:
{
  viewed: true
  thumb_view: true
  web_viewed: false
  exported: true
  ...
}

If you don't restructure the data, the query will require non-rooted regular expressions which are unable to use the indexing features of MongoDB.

However, regardless of if you decide to do that or not you will want to use map reduce for this. You could emit items that include the details during the map (either by processing them with regular expressions or simply emiting the keys in restructured form) and sum them in the reduce phase.

You can read more about it in the docs

回答2:

Checkout this link, it may help:

http://www.kchodorow.com/blog/2011/12/09/sql-to-mongodb-an-updated-mapping/

It explains how to break up a SQL CASE WHEN statement and replace each piece of SQL with the new aggregation pipeline syntax. An example is below for the first case when in your statement:

{
  $project : {
    'viewed': {
        '$cond': [ 
            {'$eq': ['$details', 'viewed'] },
            1, // if true
            0] // else
    }
  }
}

回答3:

May be below example useful for you.

Let's say you have following sample data.

db.sample.insert([
{
  "_id" : 27001,
  "assigned_to" : "[e-mail]",
  "priority" : "High"
}
,{
  "_id" : 27002,
  "assigned_to" : "[e-mail]",
  "priority" : "Low"
},
{
  "_id" : 27003,
  "assigned_to" : "[e-mail]",
  "priority" : "High"
}
]);

Use below aggregate query to get

1.Total Count

2.Count where assigend_to is "[e-mail]"

3.Count where priority is "High"

db.sample.aggregate({$group:{
                                 _id: 0,total:{$sum:1},
                                'assigned_to' : { $sum : { $cond: [ {  $eq: [ '$assigned_to' , '[e-mail]' ] }, 1, 0 ] } },
                                'hight_priority' : { $sum : { $cond: [ {  $eq: [ '$priority' , 'High' ] }, 1, 0 ] } }
                              } 
                        } 
                      );

And You will get Expected result

{
    "result" : [
        {
            "_id" : 0,
            "total" : 3,
            "assigned_to" : 2,
            "hight_priority" : 2
        }
    ],
    "ok" : 1
}