Count of list in document (sql group by)

Elastic Stack Elasticsearch
1

Hi

If I have a list in each document and I want to get the document id plus
the size of the list eg

POST /articles/article
{"title" : "One", "tags" : ["foo"]}
POST /articles/article
{"title" : "Two", "tags" : ["foo", "bar"]}
POST /articles/article
{"title" : "Three", "tags" : ["foo", "bar", "baz"]}

So One will have a count of: 1
article Two has a count of: 2
Three has a count of: 3

Is that something the facets can do?

Actually I want the result ordered by count
and indexed by document id so something like:

  • "_id" , count*
    "osweUJq0Ts6BrlIGETLXpA", 1
    "Fi8eBgZHQjOLWQwQESJqCw", 2
    "EQBHVcPuSce1kNUZXhFtMw", 3

In other words retrieve all documents along with "size of tags" order by
size of tags (ascending or descending)

Thanks in advance

--
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/74be4adf-d72f-4033-bb40-40f45d8c83ee%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

2

I think you should index it as a value in your document.

POST /articles/article
{"title" : "One", "tags" : ["foo"], "size": 1}
POST /articles/article
{"title" : "Two", "tags" : ["foo", "bar"], "size": 2}
POST /articles/article
{"title" : "Three", "tags" : ["foo", "bar", "baz"], "size": 3}

It will be really efficient.

That said, you could probably use a field script to compute it live: http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-request-script-fields.html#search-request-script-fields and this for sorting: http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-request-sort.html#_script_based_sorting

HTH

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet | @elasticsearchfr

Le 29 avril 2014 à 00:10:17, eunever32@gmail.com (eunever32@gmail.com) a écrit:

Hi

If I have a list in each document and I want to get the document id plus the size of the list eg

POST /articles/article
{"title" : "One", "tags" : ["foo"]}
POST /articles/article
{"title" : "Two", "tags" : ["foo", "bar"]}
POST /articles/article
{"title" : "Three", "tags" : ["foo", "bar", "baz"]}

So One will have a count of: 1
article Two has a count of: 2
Three has a count of: 3

Is that something the facets can do?

Actually I want the result ordered by count
and indexed by document id so something like:

"_id" , count
"osweUJq0Ts6BrlIGETLXpA", 1
"Fi8eBgZHQjOLWQwQESJqCw", 2
"EQBHVcPuSce1kNUZXhFtMw", 3

In other words retrieve all documents along with "size of tags" order by size of tags (ascending or descending)

Thanks in advance

--
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/74be4adf-d72f-4033-bb40-40f45d8c83ee%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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/etPan.535ed3bb.614fd4a1.16bd1%40MacBook-Air-de-David.local.
For more options, visit https://groups.google.com/d/optout.

3

Thanks David but
the list is being appended during bulk indexing.
To calculate the size on each update would slow things down.
Is there no equivalent to
select id, length (tags) order by length (tags) desc;

?

Thanks.

--
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/44036826-6f41-48e9-bf0f-9fe8dc339dbc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

4

Do you append it using a script in bulk request?
If so you could also increment size of the list by script I guess.

May be something like this could help? http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-metrics-valuecount-aggregation.html Unsure though.
But you won't be able to sort based on that.

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet | @elasticsearchfr

Le 29 avril 2014 à 00:43:27, eunever32@gmail.com (eunever32@gmail.com) a écrit:

Thanks David but
the list is being appended during bulk indexing.
To calculate the size on each update would slow things down.
Is there no equivalent to
select id, length (tags) order by length (tags) desc;

?

Thanks.

--
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/44036826-6f41-48e9-bf0f-9fe8dc339dbc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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/etPan.535eda93.5072367.16bd1%40MacBook-Air-de-David.local.
For more options, visit https://groups.google.com/d/optout.

5

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other counts another day.

--
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/2e1b9e8f-df79-4fb4-80f3-6fa27d357fcd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

6

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other counts another day.

--
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/1557bd63-14bb-455a-92fa-505bb3df1e1b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

7

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other counts another day.

--
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/1224d6d2-266c-4a15-9e47-1c7d30d3a52c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

8

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other counts another day.

--
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/1108824d-3701-4fc5-8edd-738956e057aa%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

9

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other counts another day.

--
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/808e8ae4-dca9-47ec-820c-c6e2582e6b00%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

10

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other counts another day.

--
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/1570499d-6df2-4890-8de4-b47e95428b4c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

11

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other counts another day.

--
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/9de6c438-44df-403e-8714-0989542f70c1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

12

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other counts another day.

--
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/660c5916-9f0a-4aca-8834-9151461f127e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

13

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other counts another day.

--
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/7d0e0e4f-7bcd-4571-975e-ca6d90c4f844%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

14

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other counts another day.

--
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/c88b863d-4428-41c3-969e-8e19dad265f6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

15

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other counts another day.

--
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/1aca0d98-cb82-4911-8e64-03054154509c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

16

Folks

Apologies about the multiple posts yesterday. A problem with the mobile
device I borrowed, (not android).

Anyway what I was getting at is lets say you have rows in a database

Then what I want to do is:
select col1, count(col2) group by col1

Is this an option in Elasticsearch ?

If I have to do something like: create index2 as select col1, count(col2)
group by col1 then okay I will do that in Elasticsearch.....

How do I get these statistics on the data?

Thanks.

On Tuesday, April 29, 2014 12:11:58 AM UTC+1, eune...@gmail.com wrote:

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get other
counts another day.

--
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/7614d210-5dee-4770-98d7-6121c683efd9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

17

Me again;)

This looks like what I need but can't get it to work...

Here's the query:

GET /articles/_search
{
"aggs": {
"group_by_id": {
"terms": {
"field": "id",
"size":0
},
"aggs": {
"sum_count": {
"value_count": {
"field": "tags"
}
}
}
}
}
}

Here's the data:

POST /articles/article
{"title" : "One", "tags" : ["foo"]}
POST /articles/article
{"title" : "Two", "tags" : ["foo", "bar"]}
POST /articles/article
{"title" : "Three", "tags" : ["foo", "bar", "baz"]}

And what I hope to achieve is
id1, 1
id2, 2
id3,3

in other words the count of tags in the list.

Thanks.

On Tuesday, April 29, 2014 9:25:17 PM UTC+1, eune...@gmail.com wrote:

Folks

Apologies about the multiple posts yesterday. A problem with the mobile
device I borrowed, (not android).

Anyway what I was getting at is lets say you have rows in a database

Then what I want to do is:
select col1, count(col2) group by col1

Is this an option in Elasticsearch ?

If I have to do something like: create index2 as select col1, count(col2)
group by col1 then okay I will do that in Elasticsearch.....

How do I get these statistics on the data?

Thanks.

On Tuesday, April 29, 2014 12:11:58 AM UTC+1, eune...@gmail.com wrote:

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get
other counts another day.

--
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/0e6fd36f-5f01-4aa2-bff5-a4388a18c24b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

18

Hey: I figured out a way to do what I want:

GET /articles/_search
{

"query" : {
    "match_all" : {}
},
"sort" : {
    "_script" : { 
        "script" : "doc['tags'].values.length",
        "type" : "number",
        "order" : "desc"
    }
}

}

On Tuesday, April 29, 2014 10:27:51 PM UTC+1, eune...@gmail.com wrote:

Me again;)

This looks like what I need but can't get it to work...

Here's the query:

GET /articles/_search
{
"aggs": {
"group_by_id": {
"terms": {
"field": "id",
"size":0
},
"aggs": {
"sum_count": {
"value_count": {
"field": "tags"
}
}
}
}
}
}

Here's the data:

POST /articles/article
{"title" : "One", "tags" : ["foo"]}
POST /articles/article
{"title" : "Two", "tags" : ["foo", "bar"]}
POST /articles/article
{"title" : "Three", "tags" : ["foo", "bar", "baz"]}

And what I hope to achieve is
id1, 1
id2, 2
id3,3

in other words the count of tags in the list.

Thanks.

On Tuesday, April 29, 2014 9:25:17 PM UTC+1, eune...@gmail.com wrote:

Folks

Apologies about the multiple posts yesterday. A problem with the mobile
device I borrowed, (not android).

Anyway what I was getting at is lets say you have rows in a database

Then what I want to do is:
select col1, count(col2) group by col1

Is this an option in Elasticsearch ?

If I have to do something like: create index2 as select col1, count(col2)
group by col1 then okay I will do that in Elasticsearch.....

How do I get these statistics on the data?

Thanks.

On Tuesday, April 29, 2014 12:11:58 AM UTC+1, eune...@gmail.com wrote:

Yes you're right I thought of that.

But I just want to get some counts

Not a permanent count. Not a development change. I might want to get
other counts another day.

--
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/fb955f0a-ff85-4869-b845-a58f321fcbf0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.