MongoDB implements queries, and each user has a different number of status values

  mongodb, question

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 beSUMNoCOUNTRight:

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 andGROUP BYThe 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:

Related Posts: