Unique count (cardinality agg query) from two fields


#1

Hello,

how can I do unique count based on two fields with cardinality aggregation query? Let’s imagine from_user and to_user are telephone numbers and I need unique count of tel.numbers.

Example:
1st document
"_index": "index1",
...
"_source": {
"creation_date": "2018-08-10T00:20:05",
"from_user": "+441234",
"to_user": "+445678"
}

2nd document
"_index": "index1",
...
"_source": {
"creation_date": "2018-08-10T00:21:47",
"from_user": "+447890",
"to_user": "+441234"
}

I need to execute cardinality aggregation query on two fields (from_user and to_user). Because I need to get number 3 - in my two doucuments are 3 unique tel. numbers.

Request:
GET /index1/_search
{
"aggs" : {
"unique_tel_numbers" : {
"cardinality" : {
"field" : "from_user"
}
}
}
}

Response:
...
"aggregations": {
"unique_tel_numbers": {
"value": 2
}
}
...


(Mark Harwood) #2

Two options:

  1. Index time: Use copy_to to create an "all phone numbers" physical field
  2. query time: use a script to combine the fields (see below)

Query-time example:

DELETE test
POST test/_doc/1
{
  "from":1,
  "to":2
}
POST test/_doc/2
{
  "from":2,
  "to":3
}
GET test/_doc/_search
{
  "size":0,
  "aggs":{
	"phones":{
	  "cardinality":{
		"script":{
		  "source":"return [doc['from'], doc['to']]"
		}
	  }
	}

  }
}

(system) #3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.