cloudant groupby and count number of times a value appears

Question

First time working with a nosql DB and having trouble writing a query that can look in my DB and for a key count the number of time it appears by another key.

For instance if my DB contains

{
   "person": "user1",
   "status": "good"
},
{
   "person": "user1",
   "status": "good"
},
{
   "person": "user1",
   "status": "bad"
},
{
   "person": "user2",
   "status": "good"
}

would like to know that person1 was good 2 and bad 1 and person2 was only good 1 in sql would do

select person, status, count(*)
from mydb
groupby person, status

or to get it by a user in the db

select person, status, count(*)
from mydb 
groupby person, status
where person = "user1"  

Answer 1

You can achieve this with Cloudant's MapReduce views and suitably chosen query parameters. I created a view where the map is

function (doc) {
    emit([doc.person, doc.status], null);
}

and the reduce the built-in _count. That gives us an index where the key is a vector, and we can then group at different levels. Using groupby=true with group_level=2 gives us the desired result:

curl 'https://A.cloudant.com/D/_design/so/_view/by-status?groupby=true&group_level=2'
{
  "rows": [
    {
      "key": [
        "user1",
        "bad"
      ],
      "value": 1
   }, 
   {
     "key": [
       "user1",
       "good"
     ],
     "value": 2
   },
   {
     "key": [
       "user2",
       "good"
     ],
     "value": 1
   }
  ]
}