yeah this is doable with elastic, but first of all you have to model your data from SQL table to document based no-sql data.
Nested document approach
1)You can model one instance of data as follows
{
"purchasedDate": "2017-04-05",
"revenue": 30,
"user": {
"id": 1,
"city": "delhi",
"event": [{
"name": "signup",
"time": "2017-04-05"
}]
}
}
For this data model you will also need to add supporting mappings
{
"mappings": {
"type_name": {
"properties": {
"purchasedDate": {
"type": "date"
},
"revenue": {
"type": "integer"
},
"user": {
"type": "object",
"properties": {
"id": {
"type": "integer"
},
"city": {
"type": "text"
},
"event": {
"type": "nested",
"properties": {
"name": {
"type": "text"
},
"time": {
"type": "date"
}
}
}
}
}
}
}
}
}
and finally you can fire the following query to get the desired results
{
"size": 0,
"aggs": {
"revenue_sum": {
"sum": {
"field": "revenue"
}
}
},
"query": {
"bool": {
"must": [{
"term": {
"purchasedDate": {
"value": "2017-04-05"
}
}
}, {
"term": {
"user.city": {
"value": "delhi"
}
}
}, {
"nested": {
"path": "user.event",
"query": {
"bool": {
"must": [{
"term": {
"user.event.name": {
"value": "signup"
}
}
}, {
"term": {
"user.event.time": {
"value": "2017-04-05"
}
}
}]
}
}
}
}]
}
}
}
for furthur reading you can refer to following links
mappings
nested datatype
Parent Child approach
Mappings
{
"mappings": {
"user": {
"properties": {
"city": {
"type": "text"
}
}
},
"event": {
"_parent": {
"type": "user"
},
"properties": {
"name": {
"type": "text"
},
"time": {
"type": "date"
}
}
},
"order": {
"_parent": {
"type": "user"
},
"properties": {
"purchasedDate": {
"type": "date"
},
"revenue": {
"type": "integer"
}
}
}
}
}
Index user, order and event documents
POST data_play2/user/1
{
"city":"london"
}
POST data_play2/order/10?parent=1
{
"purchasedDate":"2017-04-05",
"revenue": 100
}
POST data_play2/event/1?parent=1
{
"userid" : 1,
"name" : "signup",
"time" : "2017-04-05"
}
Query
{
"size": 0,
"aggs": {
"revenue": {
"children": {
"type": "order"
},
"aggs": {
"filtered_order": {
"filter": {
"bool": {
"must": [{
"term": {
"purchasedDate": {
"value": "2017-04-05"
}
}
}]
}
},
"aggs": {
"revenue_sum": {
"sum": {
"field": "revenue"
}
}
}
}
}
}
},
"query": {
"bool": {
"must": [{
"term": {
"city": {
"value": "london"
}
}
}, {
"has_child": {
"type": "order",
"query": {
"bool": {
"must": [{
"term": {
"purchasedDate": {
"value": "2017-04-05"
}
}
}]
}
}
}
}, {
"has_child": {
"type": "event",
"query": {
"bool": {
"must": [{
"term": {
"name": {
"value": "signup"
}
}
}, {
"term": {
"time": {
"value": "2017-04-05"
}
}
}]
}
}
}
}
]
}
}
}
Refer this link for furthur reading
Hope this helps
