How do you sum all values of a field that has JSON data?

[gauravepi]

I have some JSON data , in that i want to sum all values of a key in a Splunk query. Below is the sample data :

data":[{"abc":"1234","mainrate":12},{"abc":"186","mainrate":222},{"abc":"633","mainrate":121},]}],

Tried using sum but that is not giving me sum of all minrate. I'm using the below query to get this done :

eventtype=mytest| eventstats sum(mainrate) AS TotalMinRate

[somesoni2]

Seems like you want to sum the multivalued field mainrate values within same event. Unfortunately, there is no built-in function to do a multivalued field's value sum. Give this workaround a try.

If there are no primary key (some key or keys that uniquely represent each row) in your data, try this

eventtype=mytest | streamstats count as rank | eventstats sum(mainrate) AS TotalMinRate by rank

If there is a primary key in your data, use it in eventstats.

eventtype=mytest | eventstats sum(mainrate) AS TotalMinRate by yourPrimaryKeyField

[gauravepi]

eventtype=mytest  |eventstats  sum(mainrate) AS TotalMinRate  by PrimaryKey | rename date AS "datetime" | table datetime, TotalMinRate

Above is the full query but it is not doing the sum of all the json key it has for PrimaryKeyField

[somesoni2]

Is the name of the primary key field PrimaryKey?

[gauravepi]

No it's name is different i have just added as an example

[skoelpin]

You need to include a function after the pipe. Try using eventstats or stats to do this

eventtype=mytest
| eventstats sum(mainrate) AS TotalMinRate

[gauravepi]

I tried that as well but it is not working , can we use foreach to get this done?

[skoelpin]

No.. I see you updated your answer to reflect eventstats. What is not working? Have you tried stats? Are you trying to create a field with the summed values or trying to show a table view with the summed values? Have you confirmed the field is populating? If it's not then you have an issue with your extraction. Can you confirm minrate is producing results? Can you confirm minrate is numeric? If they are string values then it will not sum

[gauravepi]

I have tried both stats and eventstats but both are helping me to sum all the key values.

[gauravepi]

Yes I am trying to create a table that will have totalMinrate field and that field will have the sum of all mainrate.It is showing the TotalMinRate in rate but it is not doing the sum for each minrate present in the Json array.

[skoelpin]

You haven't answered my questions.. If these values are NOT numeric then it will not work. You should try testing with another field such as date_minute. I just tested in my env and its working as expected

index=..
| eventstats sum(date_minute)

[gauravepi]

Yes it is working for numeric values ,
Those are Json values might possible splunk is considering them as a String