Multi field facets

Hello Dears,

This is my first post here at this forum, I hope I get your help for my
question.

I've a db structure of the following:

  1. artciles
    id, title, description, country_id, category_id , insert_date

  2. country
    id, name

  3. category
    id, name, etc.

Article belongs to a country via country_id and belongs to category via
category_id.

I've created an index called articles_category_country that indexes each
article with with its category and country like this:
article {
id, title, description, country_id, category_id, insert_date, country {id,
name}, category {id, name}
}

I need to generate a report that shows number of articles per month per
country per category

In SQL we do it like this: SELECT article.insert_date, country.name as
"country_name", category.name "category_name" , count(*) total
FROM article INNER JOIN country ON country.id = article.country_id INNER
JOIN category ON category.id = article.category_id
GROUP BY article.insert_date , country_name, category_name ORDER BY total
DESC

I tried to do the same thing using elastic search using the following
query:

{
"facets": {
"by_date": {
"date_histogram": {
"field": "record_insert_date",
"interval": "month"
}
},
"by_country": {
"terms": {
"field": "posts.countries.name",
"all_terms": true
}
},
"by_cat": {
"terms": {
"script_field": "_source.posts.categories_new.name",
"all_terms": true, "order": "term"
}
}

}
}

The problem is that each facet criteria returns its own result of
aggregations , my question is: How can I get aggregations for my case
per month, country, category

I'd expect the results some thing like this:

2012-Jan Jordan Sport 5000 article
2012 Jan Jordan Technology 3000 article

2012 Jan USA Sport 1000 article
2012 Jan USA Technology 500 article

and so on.

Kindly Advice

--
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.
For more options, visit https://groups.google.com/groups/opt_out.

Hi,

For moment there is no convenient way to do it in ES, i believe this is
planned in the 1.0 release
(https://github.com/elasticsearch/elasticsearch/issues/1076)
But a hacky way to do it is to use term facet with a script field
containing the concatenation of your 3 fields (for ex.
2012Jan_Jordan_Sport) and then parse the results to deconstruct your fields.

Nabloom

On Wednesday, July 17, 2013 3:26:22 PM UTC+2, Anas Jaghoub wrote:

Hello Dears,

This is my first post here at this forum, I hope I get your help for my
question.

I've a db structure of the following:

  1. artciles
    id, title, description, country_id, category_id , insert_date

  2. country
    id, name

  3. category
    id, name, etc.

Article belongs to a country via country_id and belongs to category via
category_id.

I've created an index called articles_category_country that indexes each
article with with its category and country like this:
article {
id, title, description, country_id, category_id, insert_date, country {id,
name}, category {id, name}
}

I need to generate a report that shows number of articles per month per
country per category

In SQL we do it like this: SELECT article.insert_date, country.name as
"country_name", category.name "category_name" , count(*) total
FROM article INNER JOIN country ON country.id = article.country_id INNER
JOIN category ON category.id = article.category_id
GROUP BY article.insert_date , country_name, category_name ORDER BY total
DESC

I tried to do the same thing using elastic search using the following
query:

{
"facets": {
"by_date": {
"date_histogram": {
"field": "record_insert_date",
"interval": "month"
}
},
"by_country": {
"terms": {
"field": "posts.countries.name",
"all_terms": true
}
},
"by_cat": {
"terms": {
"script_field": "_source.posts.categories_new.name",
"all_terms": true, "order": "term"
}
}

}
}

The problem is that each facet criteria returns its own result of
aggregations , my question is: How can I get aggregations for my case
per month, country, category

I'd expect the results some thing like this:

2012-Jan Jordan Sport 5000 article
2012 Jan Jordan Technology 3000 article

2012 Jan USA Sport 1000 article
2012 Jan USA Technology 500 article

and so on.

Kindly Advice

--
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.
For more options, visit https://groups.google.com/groups/opt_out.

I don't see an option of script field for term stat facet, however I see
that for other facet. do you have the sample query that you think will work
using script field for term start facet?

On Wednesday, July 17, 2013 6:26:45 PM UTC-4, Nabloom wrote:

Hi,

For moment there is no convenient way to do it in ES, i believe this is
planned in the 1.0 release (
https://github.com/elasticsearch/elasticsearch/issues/1076)
But a hacky way to do it is to use term facet with a script field
containing the concatenation of your 3 fields (for ex.
2012Jan_Jordan_Sport) and then parse the results to deconstruct your fields.

Nabloom

On Wednesday, July 17, 2013 3:26:22 PM UTC+2, Anas Jaghoub wrote:

Hello Dears,

This is my first post here at this forum, I hope I get your help for my
question.

I've a db structure of the following:

  1. artciles
    id, title, description, country_id, category_id , insert_date

  2. country
    id, name

  3. category
    id, name, etc.

Article belongs to a country via country_id and belongs to category via
category_id.

I've created an index called articles_category_country that indexes each
article with with its category and country like this:
article {
id, title, description, country_id, category_id, insert_date, country
{id, name}, category {id, name}
}

I need to generate a report that shows number of articles per month per
country per category

In SQL we do it like this: SELECT article.insert_date, country.name as
"country_name", category.name "category_name" , count(*) total
FROM article INNER JOIN country ON country.id = article.country_id INNER
JOIN category ON category.id = article.category_id
GROUP BY article.insert_date , country_name, category_name ORDER BY total
DESC

I tried to do the same thing using elastic search using the following
query:

{
"facets": {
"by_date": {
"date_histogram": {
"field": "record_insert_date",
"interval": "month"
}
},
"by_country": {
"terms": {
"field": "posts.countries.name",
"all_terms": true
}
},
"by_cat": {
"terms": {
"script_field": "_source.posts.categories_new.name",
"all_terms": true, "order": "term"
}
}

}
}

The problem is that each facet criteria returns its own result of
aggregations , my question is: How can I get aggregations for my case
per month, country, category

I'd expect the results some thing like this:

2012-Jan Jordan Sport 5000 article
2012 Jan Jordan Technology 3000 article

2012 Jan USA Sport 1000 article
2012 Jan USA Technology 500 article

and so on.

Kindly Advice

--
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.
For more options, visit https://groups.google.com/groups/opt_out.

Thanks Nabloom for your help. one more question please, Is there a way to
combine date histogram facet with terms facet? if so please show me a
sample query for it.

Thanks

On Thursday, July 18, 2013 1:26:45 AM UTC+3, Nabloom wrote:

Hi,

For moment there is no convenient way to do it in ES, i believe this is
planned in the 1.0 release (
https://github.com/elasticsearch/elasticsearch/issues/1076)
But a hacky way to do it is to use term facet with a script field
containing the concatenation of your 3 fields (for ex.
2012Jan_Jordan_Sport) and then parse the results to deconstruct your fields.

Nabloom

On Wednesday, July 17, 2013 3:26:22 PM UTC+2, Anas Jaghoub wrote:

Hello Dears,

This is my first post here at this forum, I hope I get your help for my
question.

I've a db structure of the following:

  1. artciles
    id, title, description, country_id, category_id , insert_date

  2. country
    id, name

  3. category
    id, name, etc.

Article belongs to a country via country_id and belongs to category via
category_id.

I've created an index called articles_category_country that indexes each
article with with its category and country like this:
article {
id, title, description, country_id, category_id, insert_date, country
{id, name}, category {id, name}
}

I need to generate a report that shows number of articles per month per
country per category

In SQL we do it like this: SELECT article.insert_date, country.name as
"country_name", category.name "category_name" , count(*) total
FROM article INNER JOIN country ON country.id = article.country_id INNER
JOIN category ON category.id = article.category_id
GROUP BY article.insert_date , country_name, category_name ORDER BY total
DESC

I tried to do the same thing using elastic search using the following
query:

{
"facets": {
"by_date": {
"date_histogram": {
"field": "record_insert_date",
"interval": "month"
}
},
"by_country": {
"terms": {
"field": "posts.countries.name",
"all_terms": true
}
},
"by_cat": {
"terms": {
"script_field": "_source.posts.categories_new.name",
"all_terms": true, "order": "term"
}
}

}
}

The problem is that each facet criteria returns its own result of
aggregations , my question is: How can I get aggregations for my case
per month, country, category

I'd expect the results some thing like this:

2012-Jan Jordan Sport 5000 article
2012 Jan Jordan Technology 3000 article

2012 Jan USA Sport 1000 article
2012 Jan USA Technology 500 article

and so on.

Kindly Advice

--
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.
For more options, visit https://groups.google.com/groups/opt_out.

Unfortunately not yet, there is a work in progress for an aggregation
framework that will be released in the 1.0
(https://github.com/elasticsearch/elasticsearch/issues/3300) that will
enable such queries.

@Kajal: the hack consisted just of using terms facet and not terms stat
facet, if you want to use a terms stat facet you can rely on the
value_script instead of the value_field as described in the example in :
http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet/

Nabloom

On Saturday, July 20, 2013 12:15:12 PM UTC+2, Anas Jaghoub wrote:

Thanks Nabloom for your help. one more question please, Is there a way to
combine date histogram facet with terms facet? if so please show me a
sample query for it.

Thanks

On Thursday, July 18, 2013 1:26:45 AM UTC+3, Nabloom wrote:

Hi,

For moment there is no convenient way to do it in ES, i believe this is
planned in the 1.0 release (
https://github.com/elasticsearch/elasticsearch/issues/1076)
But a hacky way to do it is to use term facet with a script field
containing the concatenation of your 3 fields (for ex.
2012Jan_Jordan_Sport) and then parse the results to deconstruct your fields.

Nabloom

On Wednesday, July 17, 2013 3:26:22 PM UTC+2, Anas Jaghoub wrote:

Hello Dears,

This is my first post here at this forum, I hope I get your help for my
question.

I've a db structure of the following:

  1. artciles
    id, title, description, country_id, category_id , insert_date

  2. country
    id, name

  3. category
    id, name, etc.

Article belongs to a country via country_id and belongs to category via
category_id.

I've created an index called articles_category_country that indexes each
article with with its category and country like this:
article {
id, title, description, country_id, category_id, insert_date, country
{id, name}, category {id, name}
}

I need to generate a report that shows number of articles per month per
country per category

In SQL we do it like this: SELECT article.insert_date, country.name as
"country_name", category.name "category_name" , count(*) total
FROM article INNER JOIN country ON country.id = article.country_id
INNER JOIN category ON category.id = article.category_id
GROUP BY article.insert_date , country_name, category_name ORDER BY
total DESC

I tried to do the same thing using elastic search using the following
query:

{
"facets": {
"by_date": {
"date_histogram": {
"field": "record_insert_date",
"interval": "month"
}
},
"by_country": {
"terms": {
"field": "posts.countries.name",
"all_terms": true
}
},
"by_cat": {
"terms": {
"script_field": "_source.posts.categories_new.name",
"all_terms": true, "order": "term"
}
}

}
}

The problem is that each facet criteria returns its own result of
aggregations , my question is: How can I get aggregations for my case
per month, country, category

I'd expect the results some thing like this:

2012-Jan Jordan Sport 5000 article
2012 Jan Jordan Technology 3000 article

2012 Jan USA Sport 1000 article
2012 Jan USA Technology 500 article

and so on.

Kindly Advice

--
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.
For more options, visit https://groups.google.com/groups/opt_out.

@Nabloom, thanks for your tips. I find script_field works too on nested
keys, like this

"facets":{
"lpload-facet":{
"terms": {
"script_field" : "_source.lp_load.source + ':::' +
_source.lp_load.landing_page",
"size" : 10
}
}
}

but this will cause 500 error

"facets":{
"lpload-facet":{
"terms": {
"script_field" : "_source.source + ':::' + _source.landing_page",
"size" : 10
},
"nested": "lp_load"
}
}

so nested facets is not necessarily for "inner" documents faceting? But the
doc[http://www.elasticsearch.org/guide/reference/api/search/facets/] says
this

First of all, this is the only way to use facets on nested documents once

they are used

so, I'm kind of confused here.

On Sun, Jul 21, 2013 at 2:38 AM, Nabloom benmira@gmail.com wrote:

Unfortunately not yet, there is a work in progress for an aggregation
framework that will be released in the 1.0 (
https://github.com/elasticsearch/elasticsearch/issues/3300) that will
enable such queries.

@Kajal: the hack consisted just of using terms facet and not terms stat
facet, if you want to use a terms stat facet you can rely on the
value_script instead of the value_field as described in the example in :
http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet/

Nabloom

On Saturday, July 20, 2013 12:15:12 PM UTC+2, Anas Jaghoub wrote:

Thanks Nabloom for your help. one more question please, Is there a way to
combine date histogram facet with terms facet? if so please show me a
sample query for it.

Thanks

On Thursday, July 18, 2013 1:26:45 AM UTC+3, Nabloom wrote:

Hi,

For moment there is no convenient way to do it in ES, i believe this is
planned in the 1.0 release (https://github.com/**
elasticsearch/elasticsearch/**issues/1076https://github.com/elasticsearch/elasticsearch/issues/1076
)
But a hacky way to do it is to use term facet with a script field
containing the concatenation of your 3 fields (for ex.
2012Jan_Jordan_Sport) and then parse the results to deconstruct your fields.

Nabloom

On Wednesday, July 17, 2013 3:26:22 PM UTC+2, Anas Jaghoub wrote:

Hello Dears,

This is my first post here at this forum, I hope I get your help for my
question.

I've a db structure of the following:

  1. artciles
    id, title, description, country_id, category_id , insert_date

  2. country
    id, name

  3. category
    id, name, etc.

Article belongs to a country via country_id and belongs to category via
category_id.

I've created an index called articles_category_country that indexes
each article with with its category and country like this:
article {
id, title, description, country_id, category_id, insert_date, country
{id, name}, category {id, name}
}

I need to generate a report that shows number of articles per month per
country per category

In SQL we do it like this: SELECT article.insert_date, country.name as
"country_name", category.name "category_name" , count(*) total
FROM article INNER JOIN country ON country.id = article.country_id
INNER JOIN category ON category.id = article.category_id
GROUP BY article.insert_date , country_name, category_name ORDER BY
total DESC

I tried to do the same thing using elastic search using the following
query:

{
"facets": {
"by_date": {
"date_histogram": {
"field": "record_insert_date",
"interval": "month"
}
},
"by_country": {
"terms": {
"field": "posts.countries.name",
"all_terms": true
}
},
"by_cat": {
"terms": {
"script_field": "_source.posts.categories_new.**namehttp://source.posts.categories_new.name
",
"all_terms": true, "order": "term"
}
} ** **
** **

}
}

The problem is that each facet criteria returns its own result of
aggregations , my question is: How can I get aggregations for my case
per month, country, category

I'd expect the results some thing like this:

2012-Jan Jordan Sport 5000 article
2012 Jan Jordan Technology 3000 article

2012 Jan USA Sport 1000 article
2012 Jan USA Technology 500 article

and so on.

Kindly Advice

--
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.
For more options, visit https://groups.google.com/groups/opt_out.

--
Perry | 彭琪
http://pengqi.me

--
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.
For more options, visit https://groups.google.com/groups/opt_out.