0

i have a similar problem like described here: SQL like GROUP BY AND HAVING

My documents all have an array with such entries.

performance: 5,    
region: [
      {"weight":37.34405,"label":"United States"},
      {"weight":5.56081,"label":"Japan"},
      {"weight":0.12385,"label":"Australasia"},
      {"weight":0.77158,"label":"Asia - Developed"},
      {"weight":2.42013,"label":"Asia - Emerging"},
      {"weight":3.06209,"label":"Emerging Market"},
      {"weight":95.39525,"label":"Developed Country"},
      {"weight":0,"label":"Not Classified"},
      {"weight":2.04096,"label":"Canada"},
      {"weight":0.38679,"label":"Latin America"},
      {"weight":18.018,"label":"United Kingdom"},
      {"weight":23.90912,"label":"Eurozone"},
      {"weight":7.59103,"label":"Europe - ex Euro"},
      {"weight":0.14624,"label":"Europe - Emerging"},
      {"weight":0.10893,"label":"Africa"},
      {"weight":0.03585,"label":"Middle East"}
    ]

In each document the labels are the same but the weight-values are potential different. My task is to group all the regions where weight is 80+ and do math-stuff with the sum-weight from this group and the performance-value. In SQL i would simply do a "group by label having weight >= 80" query and got my values, but as a newbee in ES i haven't getting any good results.

Are here kind ES-experts, which could give me some hint for correct mapping and a query, where i get the correct result for group, sum-weight and the performance of each document?

Thank you.

Jan Wegner
  • 1
  • Did you consider denormalizing `region` values into separate objects? ES loves flat denormalized data. – ilvar Feb 02 '22 at 21:31
  • Hi ilvar, don't know, what you mean with denormalizing. Made a mapping for `region` which looks like this: "region": { "type": "nested", "properties": { "label": { "type": "text", "fields": { "keyword": { "type":"keyword", "ignore_above":256 } } }, "weight": { "type": "float" } } } – Jan Wegner Feb 03 '22 at 05:57
  • I mean, do not use `nested` instead create 10+ docs, each with `performance`, `weight` and `label` – ilvar Feb 03 '22 at 11:42

0 Answers0