Skip to content

Instantly share code, notes, and snippets.

@lukebyrne

lukebyrne/problem.md

Last active July 21, 2017 06:44
Show Gist options
  • Save lukebyrne/e4ad7357e3f632e6bd3ee7ab2ef14dcf to your computer and use it in GitHub Desktop.
Save lukebyrne/e4ad7357e3f632e6bd3ee7ab2ef14dcf to your computer and use it in GitHub Desktop.
Download ZIP
Need to aggregate by currency
Raw
problem.md

Requirements

We are updating our API to return currency conversions for all prices and currencies that we have. You can see that here, in the amount_by_currency

So in the API call for products_group_by_sizes we are returning lowPrice, avgPrice and highPrice, we would also like to return an amount_by _currency field here that will have the lowPrice, avgPrice and highPrice for each store amount_by_currency that is in the index production_compare-product_searches_group_by_sizes.

Here is the link to Kibana, I will send the password via Skype, the username is elastic

Below is the current query we are using.

Raw
query.json
{
"query": {
"function_score": {
"query": {
"bool": {
"must": [
{
"match": {
"product_keywords": {
"query": "'Scotch'",
"operator": "and",
"fuzziness": 1
}
}
}
],
"minimum_should_match": 1,
"should": [
{
"nested": {
"path": "stores",
"inner_hits": {
"size": 0
},
"query": {
"bool": {
"minimum_should_match": 1,
"should": [
{
"bool": {
"must": [
{
"term": {
"stores.type": 0
}
},
{
"geo_distance": {
"distance": "10km",
"stores.location": {
"lon": "-122.1178261",
"lat": "37.8929461"
}
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"stores.type": 1
}
},
{
"terms": {
"stores.id": [
237,
326,
416,
4,
304,
425,
275,
403,
359,
427,
355,
197,
38,
188,
227,
383,
285,
337,
353,
351,
370,
104,
180,
282,
292,
104,
312,
176,
415,
421,
113,
336,
405,
122,
446,
253,
385,
535,
1060,
1066,
1086,
1094,
1109,
14,
1139,
1141,
100,
324,
413,
176,
147,
197,
127,
310,
106,
391,
409,
216,
85,
184,
252,
366,
418,
16,
425,
319,
312,
5,
119,
203,
207,
300,
334,
222,
437,
451,
460,
460,
464,
471,
490,
498,
491,
181,
253,
508,
510,
510,
535,
1074,
1076,
1102,
1105,
1110,
14,
106,
340,
340,
255,
15,
415,
13,
355,
434,
184,
334,
96,
216,
421,
223,
289,
17,
112,
331,
130,
275,
361,
293,
201,
292,
449,
449,
452,
473,
484,
500,
504,
537,
1064,
1066,
1076,
1084,
1088,
1090,
1100,
15,
1088,
54,
98,
420,
349,
65,
378,
389,
180,
262,
66,
90,
141,
81,
418,
223,
420,
413,
81,
353,
262,
282,
147,
231,
222,
114,
304,
444,
448,
464,
467,
471,
473,
478,
488,
494,
490,
514,
529,
529,
539,
544,
544,
1080,
1098,
1114,
93,
297,
16,
359,
237,
289,
416,
9,
4,
336,
391,
187,
255,
17,
279,
285,
54,
351,
381,
9,
258,
90,
38,
98,
256,
102,
118,
252,
1,
361,
331,
454,
454,
466,
467,
488,
494,
181,
504,
505,
505,
508,
1064,
1078,
1105,
1106,
1109,
1114,
1139,
1141
]
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"stores.type": 1
}
},
{
"term": {
"stores.global_delivery": 1
}
}
]
}
}
]
}
}
}
}
],
"filter": {}
}
},
"script_score": {
"script": "_score + doc['product_relevancy_score'].value"
}
}
},
"sort": [
{
"_score": "desc"
},
{
"stores.amount": {
"mode": "min",
"order": "desc",
"nested_path": "stores",
"nested_filter": {
"bool": {
"minimum_should_match": 1,
"should": [
{
"bool": {
"must": [
{
"term": {
"stores.type": 0
}
},
{
"geo_distance": {
"distance": "10km",
"stores.location": {
"lon": "-122.1178261",
"lat": "37.8929461"
}
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"stores.type": 1
}
},
{
"terms": {
"stores.id": [
237,
326,
416,
4,
304,
425,
275,
403,
359,
427,
355,
197,
38,
188,
227,
383,
285,
337,
353,
351,
370,
104,
180,
282,
292,
104,
312,
176,
415,
421,
113,
336,
405,
122,
446,
253,
385,
535,
1060,
1066,
1086,
1094,
1109,
14,
1139,
1141,
100,
324,
413,
176,
147,
197,
127,
310,
106,
391,
409,
216,
85,
184,
252,
366,
418,
16,
425,
319,
312,
5,
119,
203,
207,
300,
334,
222,
437,
451,
460,
460,
464,
471,
490,
498,
491,
181,
253,
508,
510,
510,
535,
1074,
1076,
1102,
1105,
1110,
14,
106,
340,
340,
255,
15,
415,
13,
355,
434,
184,
334,
96,
216,
421,
223,
289,
17,
112,
331,
130,
275,
361,
293,
201,
292,
449,
449,
452,
473,
484,
500,
504,
537,
1064,
1066,
1076,
1084,
1088,
1090,
1100,
15,
1088,
54,
98,
420,
349,
65,
378,
389,
180,
262,
66,
90,
141,
81,
418,
223,
420,
413,
81,
353,
262,
282,
147,
231,
222,
114,
304,
444,
448,
464,
467,
471,
473,
478,
488,
494,
490,
514,
529,
529,
539,
544,
544,
1080,
1098,
1114,
93,
297,
16,
359,
237,
289,
416,
9,
4,
336,
391,
187,
255,
17,
279,
285,
54,
351,
381,
9,
258,
90,
38,
98,
256,
102,
118,
252,
1,
361,
331,
454,
454,
466,
467,
488,
494,
181,
504,
505,
505,
508,
1064,
1078,
1105,
1106,
1109,
1114,
1139,
1141
]
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"stores.type": 1
}
},
{
"term": {
"stores.global_delivery": 1
}
}
]
}
}
]
}
}
}
},
{
"stores.amount": {
"mode": "avg",
"order": "desc",
"nested_path": "stores",
"nested_filter": {
"bool": {
"minimum_should_match": 1,
"should": [
{
"bool": {
"must": [
{
"term": {
"stores.type": 0
}
},
{
"geo_distance": {
"distance": "10km",
"stores.location": {
"lon": "-122.1178261",
"lat": "37.8929461"
}
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"stores.type": 1
}
},
{
"terms": {
"stores.id": [
237,
326,
416,
4,
304,
425,
275,
403,
359,
427,
355,
197,
38,
188,
227,
383,
285,
337,
353,
351,
370,
104,
180,
282,
292,
104,
312,
176,
415,
421,
113,
336,
405,
122,
446,
253,
385,
535,
1060,
1066,
1086,
1094,
1109,
14,
1139,
1141,
100,
324,
413,
176,
147,
197,
127,
310,
106,
391,
409,
216,
85,
184,
252,
366,
418,
16,
425,
319,
312,
5,
119,
203,
207,
300,
334,
222,
437,
451,
460,
460,
464,
471,
490,
498,
491,
181,
253,
508,
510,
510,
535,
1074,
1076,
1102,
1105,
1110,
14,
106,
340,
340,
255,
15,
415,
13,
355,
434,
184,
334,
96,
216,
421,
223,
289,
17,
112,
331,
130,
275,
361,
293,
201,
292,
449,
449,
452,
473,
484,
500,
504,
537,
1064,
1066,
1076,
1084,
1088,
1090,
1100,
15,
1088,
54,
98,
420,
349,
65,
378,
389,
180,
262,
66,
90,
141,
81,
418,
223,
420,
413,
81,
353,
262,
282,
147,
231,
222,
114,
304,
444,
448,
464,
467,
471,
473,
478,
488,
494,
490,
514,
529,
529,
539,
544,
544,
1080,
1098,
1114,
93,
297,
16,
359,
237,
289,
416,
9,
4,
336,
391,
187,
255,
17,
279,
285,
54,
351,
381,
9,
258,
90,
38,
98,
256,
102,
118,
252,
1,
361,
331,
454,
454,
466,
467,
488,
494,
181,
504,
505,
505,
508,
1064,
1078,
1105,
1106,
1109,
1114,
1139,
1141
]
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"stores.type": 1
}
},
{
"term": {
"stores.global_delivery": 1
}
}
]
}
}
]
}
}
}
},
{
"stores.amount": {
"mode": "max",
"order": "desc",
"nested_path": "stores",
"nested_filter": {
"bool": {
"minimum_should_match": 1,
"should": [
{
"bool": {
"must": [
{
"term": {
"stores.type": 0
}
},
{
"geo_distance": {
"distance": "10km",
"stores.location": {
"lon": "-122.1178261",
"lat": "37.8929461"
}
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"stores.type": 1
}
},
{
"terms": {
"stores.id": [
237,
326,
416,
4,
304,
425,
275,
403,
359,
427,
355,
197,
38,
188,
227,
383,
285,
337,
353,
351,
370,
104,
180,
282,
292,
104,
312,
176,
415,
421,
113,
336,
405,
122,
446,
253,
385,
535,
1060,
1066,
1086,
1094,
1109,
14,
1139,
1141,
100,
324,
413,
176,
147,
197,
127,
310,
106,
391,
409,
216,
85,
184,
252,
366,
418,
16,
425,
319,
312,
5,
119,
203,
207,
300,
334,
222,
437,
451,
460,
460,
464,
471,
490,
498,
491,
181,
253,
508,
510,
510,
535,
1074,
1076,
1102,
1105,
1110,
14,
106,
340,
340,
255,
15,
415,
13,
355,
434,
184,
334,
96,
216,
421,
223,
289,
17,
112,
331,
130,
275,
361,
293,
201,
292,
449,
449,
452,
473,
484,
500,
504,
537,
1064,
1066,
1076,
1084,
1088,
1090,
1100,
15,
1088,
54,
98,
420,
349,
65,
378,
389,
180,
262,
66,
90,
141,
81,
418,
223,
420,
413,
81,
353,
262,
282,
147,
231,
222,
114,
304,
444,
448,
464,
467,
471,
473,
478,
488,
494,
490,
514,
529,
529,
539,
544,
544,
1080,
1098,
1114,
93,
297,
16,
359,
237,
289,
416,
9,
4,
336,
391,
187,
255,
17,
279,
285,
54,
351,
381,
9,
258,
90,
38,
98,
256,
102,
118,
252,
1,
361,
331,
454,
454,
466,
467,
488,
494,
181,
504,
505,
505,
508,
1064,
1078,
1105,
1106,
1109,
1114,
1139,
1141
]
}
}
]
}
},
{
"bool": {
"must": [
{
"term": {
"stores.type": 1
}
},
{
"term": {
"stores.global_delivery": 1
}
}
]
}
}
]
}
}
}
}
],
"aggs": {
"product_container": {
"terms": {
"field": "product_container.keyword",
"size": 10000
}
},
"product_type_name": {
"terms": {
"field": "product_type_name.keyword",
"size": 10000
}
},
"product_country": {
"terms": {
"field": "product_country.keyword",
"size": 10000
}
},
"product_years": {
"terms": {
"field": "product_years.keyword",
"size": 10000
}
},
"product_packaging": {
"terms": {
"field": "product_packaging.keyword",
"size": 10000
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment