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:
-
artciles
id, title, description, country_id, category_id , insert_date -
country
id, name -
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.