Last active
October 5, 2022 15:40
-
-
Save paranoiq/b181b2ff5b7d25fc0a5da21a78cf13bc to your computer and use it in GitHub Desktop.
ElasticSearch Query Language idea...
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "fields": [ | |
| "id", | |
| "startTime", | |
| "priority", | |
| "malePriority", | |
| "lastFourDaysMaleViewsInCity1", | |
| "commissionPerVisit", | |
| "lastFourDaysViewsInCity1", | |
| "lastFourDaysViews" | |
| ], | |
| "query": { | |
| "bool": { | |
| "filter": [ | |
| { | |
| "bool": { | |
| "must": [ | |
| { | |
| "terms": { | |
| "visibility": [ | |
| 1 | |
| ] | |
| } | |
| }, | |
| { | |
| "nested": { | |
| "path": "categories", | |
| "query": { | |
| "terms": { | |
| "categories.id": [ | |
| 163 | |
| ] | |
| } | |
| } | |
| } | |
| }, | |
| { | |
| "bool": { | |
| "must": [ | |
| { | |
| "range": { | |
| "startTime": { | |
| "lte": "now" | |
| } | |
| } | |
| }, | |
| { | |
| "range": { | |
| "endTime": { | |
| "gte": "now" | |
| } | |
| } | |
| } | |
| ] | |
| } | |
| }, | |
| { | |
| "nested": { | |
| "path": "categories", | |
| "query": { | |
| "bool": { | |
| "must": [ | |
| { | |
| "term": { | |
| "categories.main": { | |
| "value": true | |
| } | |
| } | |
| } | |
| ], | |
| "must_not": [ | |
| { | |
| "exists": { | |
| "field": "categories.disclaimer" | |
| } | |
| } | |
| ] | |
| } | |
| } | |
| } | |
| }, | |
| { | |
| "term": { | |
| "cityIds": { | |
| "value": 1 | |
| } | |
| } | |
| } | |
| ], | |
| "must_not": [ | |
| { | |
| "nested": { | |
| "path": "campaigns", | |
| "query": { | |
| "term": { | |
| "campaigns.visibility": { | |
| "value": 2 | |
| } | |
| } | |
| } | |
| } | |
| } | |
| ] | |
| } | |
| } | |
| ] | |
| } | |
| }, | |
| "post_filter": { | |
| "bool": { | |
| "must": [ | |
| { | |
| "nested": { | |
| "path": "categories", | |
| "query": { | |
| "terms": { | |
| "categories.id": [ | |
| 173 | |
| ] | |
| } | |
| } | |
| } | |
| } | |
| ] | |
| } | |
| }, | |
| "aggs": { | |
| "slevomat-product-elasticsearch-filter-producteventstarttimefilter-filtered": { | |
| "filter": { | |
| "bool": { | |
| "must": [ | |
| { | |
| "nested": { | |
| "path": "categories", | |
| "query": { | |
| "terms": { | |
| "categories.id": [ | |
| 173 | |
| ] | |
| } | |
| } | |
| } | |
| } | |
| ] | |
| } | |
| }, | |
| "aggs": { | |
| "slevomat-product-elasticsearch-filter-producteventstarttimefilter": { | |
| "filter": { | |
| "nested": { | |
| "path": "categories", | |
| "query": { | |
| "terms": { | |
| "categories.id": [ | |
| 173 | |
| ] | |
| } | |
| } | |
| } | |
| }, | |
| "aggs": { | |
| "slevomat-product-elasticsearch-filter-producteventstarttimefilter-nested": { | |
| "nested": { | |
| "path": "variants" | |
| }, | |
| "aggs": { | |
| "slevomat-product-elasticsearch-filter-producteventstarttimefilter-filter": { | |
| "filter": { | |
| "bool": { | |
| "must": [ | |
| { | |
| "term": { | |
| "variants.purchasable": { | |
| "value": true | |
| } | |
| } | |
| }, | |
| { | |
| "range": { | |
| "variants.eventStartTime": { | |
| "from": "2016-05-31" | |
| } | |
| } | |
| } | |
| ] | |
| } | |
| }, | |
| "aggs": { | |
| "slevomat-product-elasticsearch-filter-producteventstarttimefilter-terms": { | |
| "terms": { | |
| "field": "variants.eventStartTime", | |
| "format": "yyyy-MM-dd", | |
| "size": 0 | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| }, | |
| "slevomat-product-elasticsearch-filter-producteventstarttimefilter-notfiltered": { | |
| "filter": { | |
| "nested": { | |
| "path": "categories", | |
| "query": { | |
| "terms": { | |
| "categories.id": [ | |
| 173 | |
| ] | |
| } | |
| } | |
| } | |
| }, | |
| "aggs": { | |
| "slevomat-product-elasticsearch-filter-producteventstarttimefilter-nested": { | |
| "nested": { | |
| "path": "variants" | |
| }, | |
| "aggs": { | |
| "slevomat-product-elasticsearch-filter-producteventstarttimefilter-filter": { | |
| "filter": { | |
| "bool": { | |
| "must": [ | |
| { | |
| "term": { | |
| "variants.purchasable": { | |
| "value": true | |
| } | |
| } | |
| }, | |
| { | |
| "range": { | |
| "variants.eventStartTime": { | |
| "from": "2016-05-31" | |
| } | |
| } | |
| } | |
| ] | |
| } | |
| }, | |
| "aggs": { | |
| "slevomat-product-elasticsearch-filter-producteventstarttimefilter-terms": { | |
| "terms": { | |
| "field": "variants.eventStartTime", | |
| "format": "yyyy-MM-dd", | |
| "size": 0 | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| }, | |
| "slevomat-product-elasticsearch-filter-productcategoriestreeaggregationfilter": { | |
| "nested": { | |
| "path": "categories" | |
| }, | |
| "aggs": { | |
| "slevomat-product-elasticsearch-filter-productcategoriestreeaggregationfilter-filter": { | |
| "filter": { | |
| "terms": { | |
| "categories.parentId": [ | |
| 163, | |
| 173 | |
| ] | |
| } | |
| }, | |
| "aggs": { | |
| "slevomat-product-elasticsearch-filter-productcategoriestreeaggregationfilter-terms": { | |
| "terms": { | |
| "field": "categories.data", | |
| "size": 0 | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| }, | |
| "sort": [ | |
| { | |
| "purchasable": "desc" | |
| }, | |
| { | |
| "_script": { | |
| "lang": "groovy", | |
| "script": "doc['startTime'].value >= startTimeLimit && doc['priority'].value >= 4 ? doc['priority'].value : 0", | |
| "type": "number", | |
| "order": "desc", | |
| "params": { | |
| "startTimeLimit": 1464645600000 | |
| } | |
| } | |
| }, | |
| { | |
| "_script": { | |
| "lang": "groovy", | |
| "script": "doc['startTime'].value >= startTimeLimit && doc['priority'].value >= 4 ? doc['malePriority'].value : 0", | |
| "type": "number", | |
| "order": "desc", | |
| "params": { | |
| "startTimeLimit": 1464645600000 | |
| } | |
| } | |
| }, | |
| { | |
| "_script": { | |
| "lang": "groovy", | |
| "script": "doc['lastFourDaysMaleViewsInCity1'].value * (doc['commissionPerVisit'].value > 3.000 ? 3.000 : (doc['commissionPerVisit'].value > 0.200 ? doc['commissionPerVisit'].value : 0.200))", | |
| "type": "number", | |
| "order": "desc" | |
| } | |
| }, | |
| { | |
| "_script": { | |
| "lang": "groovy", | |
| "script": "doc['lastFourDaysViewsInCity1'].value * (doc['commissionPerVisit'].value > 3.000 ? 3.000 : (doc['commissionPerVisit'].value > 0.200 ? doc['commissionPerVisit'].value : 0.200))", | |
| "type": "number", | |
| "order": "desc" | |
| } | |
| }, | |
| { | |
| "_script": { | |
| "lang": "groovy", | |
| "script": "doc['lastFourDaysViews'].value * (doc['commissionPerVisit'].value > 3.000 ? 3.000 : (doc['commissionPerVisit'].value > 0.200 ? doc['commissionPerVisit'].value : 0.200))", | |
| "type": "number", | |
| "order": "desc" | |
| } | |
| }, | |
| { | |
| "lastFourDaysViews": "desc" | |
| }, | |
| { | |
| "malePriority": "desc" | |
| }, | |
| { | |
| "priority": "desc" | |
| }, | |
| { | |
| "startDate": "desc" | |
| }, | |
| { | |
| "id": "desc" | |
| } | |
| ], | |
| "from": 0, | |
| "size": 12 | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT -- fields | |
| id, | |
| startTime, | |
| priority, | |
| malePriority, | |
| lastFourDaysMaleViewsInCity1, | |
| commissionPerVisit, | |
| lastFourDaysViewsInCity1, | |
| lastFourDaysViews | |
| FROM products | |
| WHERE visibility IN [1] -- query | |
| AND categories.id IN [163] | |
| AND starttime <= "now" | |
| AND endtime >= "now" | |
| AND categories.main = TRUE | |
| AND NOT EXIST categories.disclaimer | |
| AND cityIds = 1 | |
| AND NOT campaigns.visibility = 2 | |
| AGGREGATE "slevomat-product-elasticsearch-filter-producteventstarttimefilter-filtered" | |
| SELECT variants.eventStartTime FORMAT "yyyy-MM-dd" | |
| WHERE variants.purchasable = TRUE | |
| AND variants.eventStartTime > "2016-05-31" | |
| AND categories.id IN [173] | |
| LIMIT 0 | |
| AGGREGATE "slevomat-product-elasticsearch-filter-producteventstarttimefilter-notFiltered" | |
| SELECT variants.eventStartTime FORMAT "yyyy-MM-dd" | |
| WHERE variants.purchasable = TRUE | |
| AND variants.eventStartTime > "2016-05-31" | |
| LIMIT 0 | |
| AGGREGATE "slevomat-product-elasticsearch-filter-productcategoriestreeaggregationfilter" | |
| SELECT categories.data | |
| WHERE categories.parentId IN [163, 173] | |
| LIMIT 0 | |
| ORDER BY purchasable DESC, | |
| startTime >= 1464645600000 && priority >= 4 ? priority : 0 DESC, | |
| lastFourDaysMaleViewsInCity1 * (commissionPerVisit > 3.0 ? 3.0 : (commissionPerVisit > 0.2 ? commissionPerVisit : 0.2)) DESC, | |
| lastFourDaysViewsInCity1 * (commissionPerVisit > 3.0 ? 3.0 : (commissionPerVisit > 0.2 ? commissionPerVisit : 0.2)) DESC, | |
| lastFourDaysViews * (commissionPerVisit > 3.0 ? 3.0 : (commissionPerVisit > 0.2 ? commissionPerVisit : 0.2)) DESC, | |
| lastFourDaysViews DESC, | |
| malePriority DESC, | |
| priority DESC, | |
| startDate DESC, | |
| id DESC | |
| HAVING categories.id IN [173] -- post filter | |
| LIMIT 12 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
those two queries should be functionally identical, but the later one is much more readable and accessible for people with only SQL experience. and also written in non-existing language ^_^