The mongodb table has userid, and the status field has 0, 1 and 2 values.
{userId:1, status: 0}
{userId:1, status: 2}
{userId:1, status: 2}
{userId:2, status: 0}
{userId:2, status: 1}
{userId:2, status: 2}
I want to group queries according to users, and then filter out the occurrence times of different status values for each user. How do I write mongodb query statements?
For example, the final expected result above is:
[
{userId:1, status_a: 1, status_b: 0, status_c: 2},
{userId:2, status_a: 1, status_b: 1, status_c: 1}
]
Similar to mysql
SELECT `userid`, COUNT( CASE WHEN `status` = '0' THEN 1 ELSE NULL END ), COUNT( CASE WHEN `status` = '1' THEN 1 ELSE NULL END ), COUNT( CASE WHEN `status` = '2' THEN 1 ELSE NULL END ) GROUP BY `userid`
I think what you mean should be
SUM
NoCOUNT
Right:SELECT `userId`, SUM( CASE WHEN `status` = '0' THEN 1 ELSE 0 END ), SUM( CASE WHEN `status` = '1' THEN 1 ELSE 0 END ), SUM( CASE WHEN `status` = '2' THEN 1 ELSE 0 END ) GROUP BY `userId`
In fact, the equivalent form is:
SELECT `userId`, `status`, COUNT(1) GROUP BY `userId`, `status`
But the result is in a different form. In MongoDB and
GROUP BY
The corresponding operator is$group
. Therefore, if the second form is acceptable, it is simpler to write it in:db.foo.aggregate([ {$group: {_id: {userId: "$userId", status: "$status"}, count: {$sum: 1}}}, ]);
If you insist on the first form, it will be more complicated:
db.foo.aggregate([ { $project: { userId: 1, status_0: {$cond: {if: {$eq: [0, "$status"]}, then: 1, else: 0}}, status_1: {$cond: {if: {$eq: [1, "$status"]}, then: 1, else: 0}}, status_2: {$cond: {if: {$eq: [2, "$status"]}, then: 1, else: 0}}, } }, { $group: { _id: "$userId", status_0: {$sum: "$status_0"}, status_1: {$sum: "$status_1"}, status_2: {$sum: "$status_2"} } } ]);
Reference usage:
$project
:https://docs.mongodb.com/manu …$cond
:https://docs.mongodb.com/manu …$group
:https://docs.mongodb.com/manu …