Count number of array element for each document


(Niv Penso) #1

Hey Guys,

I am trying to count the element's number of specific field (array) for
each document.

I have looked it here:
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-metrics-valuecount-aggregation.html
and it seems that I have to use value_count aggregation and script to count
the number of the array element's.

this is my mapping:

{
"ckdocuments": {
"mappings": {
"msv": {
"properties": {
"MSV": {
"type": "nested",
"properties": {
"country": {
"type": "string",
"index": "not_analyzed"
},
"date": {
"type": "date",
"format": "YYYY-MM-dd HH:mm:ss"
},
"hits": {
"type": "nested",
"properties": {
"click_type": {
"type": "string",
"index": "not_analyzed"
}
}
}
}
},
"c": {
"type": "string",
"index": "not_analyzed"
},
"doc_creation_time": {
"type": "date",
"format": "YYYY-MM-dd HH:mm:ss"
},
"views": {
"type": "nested",
"properties": {
"country": {
"type": "string"
},
"date": {
"type": "date",
"format": "YYYY-MM-dd HH:mm:ss"
},
"hits": {
"properties": {
"click_type": {
"type": "string"
}
}
}
}
}
}
}
}
}
}

These are the existing documents:

{
"_index": "ckdocuments",
"_type": "msv",
"_id": "1",
"_version": 1,
"found": true,
"_source": {
"c": "a",
"MSV": [
{
"country": "US",
"date": "2013-01-01 00:00:00",
"hits": [
{
"click_type": "click"
}
]
}
],
"views": [
{
"country": "US",
"date": "2013-01-01 00:00:00",
"hits": [
{
"click_type": "click"
}
]
},
{
"country": "IL",
"date": "2013-01-01 00:00:00",
"hits": [
{
"click_type": "click"
}
]
}
]
}
}

and:

{
"_index": "ckdocuments",
"_type": "msv",
"_id": "2",
"_version": 1,
"found": true,
"_source": {
"doc_creation_time": "2013-01-01 00:00:00",
"MSV": [
{
"country": "IL",
"date": "2013-01-01 00:00:00",
"hits": [
{
"click_type": "pixel"
}
]
},
{
"country": "US",
"date": "2013-01-02 00:00:00",
"hits": [
{
"click_type": "click"
}
]
}
],
"views": [
{
"country": "US",
"date": "2013-01-01 00:00:00",
"hits": []
},
{
"country": "US",
"date": "2013-01-01 00:00:00",
"hits": [
{
"click_type": "pixel"
},
{
"click_type": "pixel"
}
]
},
{
"country": "US",
"date": "2013-01-02 00:00:00",
"hits": [
{
"click_type": "click"
}
]
}
]
}
}

and this is the qeury I use to retrieve views count (I have to say I use
Marvel to run the request)
GET ckdocuments/msv/_search
{
"aggregations":
{
"views_count":
{
"value_count": {
"script": "doc['views'].value.length"
}
}
},
"size": 0
}

and this is the result:
{
"took": 5,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 3,
"failed": 2,
"failures": [
{
"index": "ckdocuments",
"shard": 3,
"status": 500,
"reason": "QueryPhaseExecutionException[[ckdocuments][3]:
query[ConstantScore(cache(_type:msv))],from[0],size[0]: Query Failed
[Failed to execute main query]]; nested: CompileException[[Error: No field
found for [views] in mapping with types [msv]]\n[Near : {...
doc['views'].value.length ....}]\n ^\n[Line: 1, Column: 1]];
nested: ElasticsearchIllegalArgumentException[No field found for [views] in
mapping with types [msv]]; "
},
{
"index": "ckdocuments",
"shard": 2,
"status": 500,
"reason": "QueryPhaseExecutionException[[ckdocuments][2]:
query[ConstantScore(cache(_type:msv))],from[0],size[0]: Query Failed
[Failed to execute main query]]; nested: CompileException[[Error: No field
found for [views] in mapping with types [msv]]\n[Near : {...
doc['views'].value.length ....}]\n ^\n[Line: 1, Column: 1]];
nested: ElasticsearchIllegalArgumentException[No field found for [views] in
mapping with types [msv]]; "
}
]
},
"hits": {
"total": 0,
"max_score": 0,
"hits": []
},
"aggregations": {
"views_count": {
"value": 0
}
}
}

Is the syntax right?

Thnx Niv

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/e5e8c6ef-702b-4c21-a429-f67eca00d920%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(Niv Penso) #2

No one?

On Monday, May 19, 2014 6:20:18 PM UTC+3, Niv Penso wrote:

Hey Guys,

I am trying to count the element's number of specific field (array) for
each document.

I have looked it here:
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-metrics-valuecount-aggregation.html
and it seems that I have to use value_count aggregation and script to
count the number of the array element's.

this is my mapping:

{
"ckdocuments": {
"mappings": {
"msv": {
"properties": {
"MSV": {
"type": "nested",
"properties": {
"country": {
"type": "string",
"index": "not_analyzed"
},
"date": {
"type": "date",
"format": "YYYY-MM-dd HH:mm:ss"
},
"hits": {
"type": "nested",
"properties": {
"click_type": {
"type": "string",
"index": "not_analyzed"
}
}
}
}
},
"c": {
"type": "string",
"index": "not_analyzed"
},
"doc_creation_time": {
"type": "date",
"format": "YYYY-MM-dd HH:mm:ss"
},
"views": {
"type": "nested",
"properties": {
"country": {
"type": "string"
},
"date": {
"type": "date",
"format": "YYYY-MM-dd HH:mm:ss"
},
"hits": {
"properties": {
"click_type": {
"type": "string"
}
}
}
}
}
}
}
}
}
}

These are the existing documents:

{
"_index": "ckdocuments",
"_type": "msv",
"_id": "1",
"_version": 1,
"found": true,
"_source": {
"c": "a",
"MSV": [
{
"country": "US",
"date": "2013-01-01 00:00:00",
"hits": [
{
"click_type": "click"
}
]
}
],
"views": [
{
"country": "US",
"date": "2013-01-01 00:00:00",
"hits": [
{
"click_type": "click"
}
]
},
{
"country": "IL",
"date": "2013-01-01 00:00:00",
"hits": [
{
"click_type": "click"
}
]
}
]
}
}

and:

{
"_index": "ckdocuments",
"_type": "msv",
"_id": "2",
"_version": 1,
"found": true,
"_source": {
"doc_creation_time": "2013-01-01 00:00:00",
"MSV": [
{
"country": "IL",
"date": "2013-01-01 00:00:00",
"hits": [
{
"click_type": "pixel"
}
]
},
{
"country": "US",
"date": "2013-01-02 00:00:00",
"hits": [
{
"click_type": "click"
}
]
}
],
"views": [
{
"country": "US",
"date": "2013-01-01 00:00:00",
"hits": []
},
{
"country": "US",
"date": "2013-01-01 00:00:00",
"hits": [
{
"click_type": "pixel"
},
{
"click_type": "pixel"
}
]
},
{
"country": "US",
"date": "2013-01-02 00:00:00",
"hits": [
{
"click_type": "click"
}
]
}
]
}
}

and this is the qeury I use to retrieve views count (I have to say I use
Marvel to run the request)
GET ckdocuments/msv/_search
{
"aggregations":
{
"views_count":
{
"value_count": {
"script": "doc['views'].value.length"
}
}
},
"size": 0
}

and this is the result:
{
"took": 5,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 3,
"failed": 2,
"failures": [
{
"index": "ckdocuments",
"shard": 3,
"status": 500,
"reason": "QueryPhaseExecutionException[[ckdocuments][3]:
query[ConstantScore(cache(_type:msv))],from[0],size[0]: Query Failed
[Failed to execute main query]]; nested: CompileException[[Error: No field
found for [views] in mapping with types [msv]]\n[Near : {...
doc['views'].value.length ....}]\n ^\n[Line: 1, Column: 1]];
nested: ElasticsearchIllegalArgumentException[No field found for [views] in
mapping with types [msv]]; "
},
{
"index": "ckdocuments",
"shard": 2,
"status": 500,
"reason": "QueryPhaseExecutionException[[ckdocuments][2]:
query[ConstantScore(cache(_type:msv))],from[0],size[0]: Query Failed
[Failed to execute main query]]; nested: CompileException[[Error: No field
found for [views] in mapping with types [msv]]\n[Near : {...
doc['views'].value.length ....}]\n ^\n[Line: 1, Column: 1]];
nested: ElasticsearchIllegalArgumentException[No field found for [views] in
mapping with types [msv]]; "
}
]
},
"hits": {
"total": 0,
"max_score": 0,
"hits": []
},
"aggregations": {
"views_count": {
"value": 0
}
}
}

Is the syntax right?

Thnx Niv

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/349dab28-dd4f-4d5c-a9fe-e8ab27fee95d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(robynico) #3

Hey I found one solution because I had the same need :

use nested type mapping for array element and do something like that :
{
"aggregations": {
"views_count": {
"nested": {
"path": "views"
},
"aggs": {
"value_count": {
"sum": {
"field": "views",
"script": "doc['views'].value.length"
}
}
}
}
},
"size": 0
}

it works fine for me.


(system) #4