How to agregate over multiple key/values

Couchbase Server
1

Say I have the following documents

{
“id”:"…",
“account”:“1234567890”,
“name”:“John Smith”,
“email”:"foo@bar.com",
}

{
“id”:"…",
“account”:“1234567891”,
“name”:“Jane Doe”,
“email”:"bar@foo.com",
}

{
“id”:"…",
“account”:“1234567891”,
“name”:“Jane Doe”,
“email”:"none@foo.com",
}

My standard relation query looks like so…

select * from SomeTable where account = ? or name = ? or email = ?

Given above resultset:

For account 1234567890
Total names: 1
Total emails: 1

For account 1234567891
Total names: 1
Total emails: 2

For name John Smith
Total accounts: 1
totals emails: 1

For name Jane Doe
Total accounts: 1
Total emails: 2

For email foo@bar.com
Total accounts: 1
Total names: 1

For email bar@foo.com
Total accounts: 1
Total names: 1

For email none@foo.com
Total accounts: 1
Total names: 1

Basically I would like to perform a cross reference type logic.

Thanks

2

So I created the following map()

function (doc, meta) {
emit(doc.account, [doc.phone, doc.email]);
}

So now how can I count how many phones and emails for this key. Note I do not want to create list of phones per document. Bassicaly each request in my system creates a new entry and then I want to check if someone who is using this account has for instance tried entering a different phone number or email… So in most request the phone count and email count should be always 1 but if it’s higher then 1 then I know it’s problem. This is basically for fraud analytics.

3

Try playing around with the built in reduces.

For example, if you have the map that emits (doc.account, doc.email), the count will give you the emails per account.

You should also look at N1QL, which will allow you to do more group bys etc across documents.
query.couchbase.com

It is currently in Dev preview 2.

4

That didn’t seem to work… I have the 4 following docs:

{“a”:“12345”, “p”:“555-1111”} {“a”:“12345”, “p”:“555-1111”} {“a”:“12345”, “p”:“555-2222”} {“a”:“12345”, “p”:“555-333”}

The following map()

function (doc, meta) {
emit(doc.a, doc.p);
}

And reduce

_count

The answer returned is 4 when it should be 3… Does that make more sense?

5

Well the way the reduce works is, it aggregates the count of the unique keys.

For example, the emit function is always of the form emit (key, value)

In your example, you had only 1 key that got emitted 4 times, so the count is 4.

If you want to do unique combinations, you could use an array and then the group_level.

emit ([doc.a, doc.b], doc.value)

In this case, _count will count all unique doc.a’s for group level 1. or count all unique combinations of [doc.a,doc.b] values if you use group level 2.

hope this helps explain how grouping and the reduces work. If the value portion is a number, you can use _sum and _stats.