Kibana - Parse Out / Aggregate Total Values Scripted Code

[prohit (Rohit)]

Hi All, Need help in writing script painless code to parse out/aggregate the "hours worked" in below document.
My Kibana Variable Document:

[{"Dateweeklycomments":"2020-04-09T00:00:00.000Z","Name":"Roger Benny","Role":{"id":null,"name":"Primary Analyst"},"Hours Worked":"5"},"Dateweeklycomments":"2020-04-16T00:00:00.000Z","Name":"Roger Benny","Role":{"id":null,"name":"Primary Analyst"},"Hours Worked":"15"}]

Team, i need your help in writing script to get the output of total Hours Worked in this document for example: in this case it is 5 + 15: 20

[afharo (Alejandro Fernandez Haro)]

Hi @prohit,

Sorry, I'm a bit confused: in the description, you are talking about 1 document, but the code you pasted is an array of 2 objects.

Does it mean you've pasted 2 documents or that the array belongs in a field inside a document?

In any case, I think you don't need a Painless script to do this. We only need to tell Elasticsearch that we want to be able to analyze Hours Worked as a number.

I've created a test index myself to simulate your scenario. You can run the same commands in Kibana's Dev Tools if you want.

1. Without setting any previous mappings, I start indexing the values:

PUT my-test-index/_doc/1
{
  "Dateweeklycomments": "2020-04-09T00:00:00.000Z",
  "Name": "Roger Benny",
  "Role": { "id": null, "name": "Primary Analyst" },
  "Hours Worked": "5"
}

PUT my-test-index/_doc/2
{
  "Dateweeklycomments": "2020-04-16T00:00:00.000Z",
  "Name": "Roger Benny",
  "Role": { "id": null, "name": "Primary Analyst" },
  "Hours Worked": "15"
}

2. Elasticsearch has created the mappings dynamically for me GET my-test-index/_mapping returns

{
  "my-test-index" : {
    "mappings" : {
      "properties" : {
        "Dateweeklycomments" : {
          "type" : "date"
        },
        "Hours Worked" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "Name" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "Role" : {
          "properties" : {
            "name" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            }
          }
        }
      }
    }
  }
}

3. Focusing on Hours Worked, you can see Elasticsearch has identified it as a text, and it has also added the multi-field .keyword in case we want to analyze them as such. But we want to be able to sum those values, right? Let's make use of the multi-field feature, to create a new subfield .number, that will be analyzed as a short (you can also use any other Numeric type as it fits your data). To do so, we can add that new multi-field in the mappings:

PUT my-test-index/_mapping
{
  "properties": {
    "Hours Worked": {
      "type": "text",
      "fields": {
        "number": {"type": "short"}
      }
    }
  }
}

4. If we get the mappings again GET my-test-index/_mapping, we can see Hours Worked is still type: "text", but it now has 2 multi-fields: the previous keyword and the new number.

{
  "my-test-index" : {
    "mappings" : {
      "properties" : {
        "Dateweeklycomments" : {
          "type" : "date"
        },
        "Hours Worked" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            },
            "number" : {
              "type" : "short"
            }
          }
        },
        "Name" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "Role" : {
          "properties" : {
            "name" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            }
          }
        }
      }
    }
  }
}

5. Then, we need to tell Elasticsearch to re-process all the documents, so it applies the new mapping to the previous documents. We can do that by running POST my-test-index/_update_by_query.

6. Finally, now we can use the new field Hours Worked.number to aggregate and sum the hours

GET my-test-index/_search
{
  "query": {
    "match_all": {}
  },
  "size": 0, 
  "aggs": {
    "names": {
      "terms": {
        "field": "Name.keyword",
        "size": 10
      },
      "aggs": {
        "Sum of Hours Worked": {
          "sum": {
            "field": "Hours Worked.number"
          }
        }
      }
    }
  }
}

Note, you can also do visualizations now in Kibana with the new field.

If you still prefer the painless-scripted way, let me know. But I wouldn't recommend it because this approach is more performant and it also allows you to search by ranges.

[prohit (Rohit)]

@afharo Thank you Alejandro for taking time and helping me out, really appreciated.
Yes, this is one document and array belongs in a field inside a document. Like in a month we could have 5 weeks, so here we're aggregating 5 weekly hours worked.

So the below example, i see it is two documents, can i try using this code for single document ? will it work, ill try if possible share few more snapshot or examples for better explanation.

[afharo (Alejandro Fernandez Haro)]

Hi @prohit,

Thank you for your further explanation. I understand is that you've got one document with a property, and that property is an array of objects like the ones you posted in your original description. Is that correct?

I'll assume it looks like below:

{
  "weekly_comments": [
    {"Dateweeklycomments":"2020-04-09T00:00:00.000Z","Name":"Roger Benny","Role":{"id":null,"name":"Primary Analyst"},"Hours Worked":"5"},
    {"Dateweeklycomments":"2020-04-16T00:00:00.000Z","Name":"Roger Benny","Role":{"id":null,"name":"Primary Analyst"},"Hours Worked":"15"}
  ],
  ...some_other_common_fields
}

Question: do all the entries in the array refer to the same employee, or is it possible to have a document referring to multiple employees?

If all entries in the array refer to the same employee, a sum aggregation would be enough as Elasticearch will flatten the array.

If the array might include multiple employees, you might need to either, index your documents in a different way to avoid that, or make use of the nested type field in Elasticsearch. Unfortunately, that type is not supported natively in Kibana, and you might need to use Vega for a more complex aggregation.

You can read the documentation about nested type to understand how Elasticsearch handles arrays of objects and why I'm making such distinction: Nested field type | Elasticsearch Guide [7.12] | Elastic

[prohit (Rohit)]

@afharo thank you Alejandro for coming back, yes it is one document with property and all entries in the array refer to the same employee, so that's make the job little easy to run down as suggested by sum aggregation, I have tried a code it din't worked, you have already solved major part, could you please help me with code for this.

[prohit (Rohit)]

@afharo , Request you to kindly help me with code for sum aggregation for the above document.

[afharo (Alejandro Fernandez Haro)]

Sorry @prohit, I've been out for a couple of months. I'll try to help you with the last bit to get it working.

@prohit

I have tried a code it din't worked,

Can you share the error that you get, please?