Tables
Orders
orderstate
time_reserved
user_id
product_id
Product
id
refid
This query hit 76 documents.
select * from Orders
inner join Products on Orders.productid = Products.id
where Products.`refid` = 9
and orderstate like "confirmed"
group by userid;
With the list of all unique users and I would like to group that with time_reserved to know the list of unique users first and then group this list by "day" counting the users in this day.
My ElasticSearch query is:
{
"aggs": {
"group_by_date": {
"date_histogram": {
"field": "time_reserved",
"interval": "day",
"format": "yyyy-MM-dd"
},
"aggs": {
"amount": {
"cardinality": {
"field": "user_id"
}
}
}
}
},
"query": {
"bool": {
"filter": [{
"term": {
"dealid": "9"
}
}],
"must": [{
"terms": {
"orderstate": ["confirmed"]
}
}]
}
}
}
Which works but it hits 144 documents, that is because it is not doing the cardinality in the second aggregation and it is returning duplicate user_id documents and match with the result of the same query above but without group by:
select * from Orders
inner join Products on Orders.productid = Products.id
where Products.`refid` = 9
and orderstate like "confirmed";
There is a simple way to have to group by ?