How to extract field value from JSON and add the total of the value?

[jitin_ratra]

I have the following JSON format .

Content : {

"purchaseId":12345,
"items":[

{ }
],
"total":1100.24
},

{

"purchaseId":67890,
"items":[

{ }
],
"total":1100.24
}

I want to extract the field total and add the sum of the field "total" and create a table based on the sum. Tried to see the usage of spath in my case but no luck. Looking for final output as table like below. Appreciate any advise here.

| Total |

2200.48

[jitin_ratra]

Thanks @niketnilay and @skoelpin for your feedback. I used JSON validator to validate the JSON format i posted above. The solution works when we evaluate the JSON data for 2 or 3 events but since i have lot of events which i need to add , it still does not show me any results.

Below is the sample of query i ran based on your suggestion above . I can see there are 12951 events but they dont turn up in statistics.

[niketn]

@jitin_ratra, you seem to be missing max_match=0 argument for the rex command in the screenshot provided. Can you please check?

  <yourCurrentBaseSearch>
 | rex "\"total\":(?<total>[^\s]+)\s" max_match=0
 | mvexpand total
 | stats sum(total) as total

[niketn]

@jitin_ratra, the JSON data you have shared in your question does not seem to be valid JSON packet. So, you would need to ensure that JSON tree structure is corrected first. You can then use
1) KV_MODE=json to have fields from JSON automatically extracted at search time (refer to KV_MODE details in props.conf documentation) or
2) spath command to traverse the JSON data.

Following is a run anywhere example with valid JSON data structure:

| makeresults
| eval _raw="{   \"Content\" : 
    {   
        \"purchaseId\":12345, 
        \"items\":[ { } ], 
        \"total\":1100.24 
    },
    \"Content\" :
    {   
        \"purchaseId\":67890, 
        \"items\":[ { } ], 
        \"total\":1100.24 
    }
}"
| spath
| mvexpand Content.total
| stats sum(Content.total) as total

In case you do not have access to modify the JSON data format, you mat have to use the option suggested by @skoelpin of using rex command to apply regular expression field extraction. Only difference is that since you have a multivalue total field, you would need to use argument max_match=0 for unlimited matches or some high number based on maximum total fields that you can have in your JSON.

Following is a run anywhere search with rex command based on your sample data (as is):

| makeresults
| eval _raw="Content : { 
\"purchaseId\":12345,
\"items\":[ 
{ }
],
\"total\":1100.24
},

{ 
\"purchaseId\":67890,
\"items\":[ 
{ }
],
\"total\":1100.24
}"
| rex "\"total\":(?<total>[^\s]+)\s" max_match=0
| mvexpand total
| stats sum(total) as total

[jitin_ratra]

@niketnilay, looks like something else is missing here. Tried to use max_match=0 as well. but no events returned now.
Below is my exact JSON data and highlighted is the field i want to extract and sum

[niketn]

@jitin_ratra from the screenshot of your SPL, seems like you have not copied over the rex command correctly. Please ensure double quotes and escaped double quote are applied as is.

 | rex "\"total\":(?<total>[^\s]+)\s" max_match=0

[skoelpin]

Add this

index=...
| rex total\"\:(?<total>\d+\.\d+)
| stats sum(total) AS Total

[niketn]

@skoelpin, slight correction since you plan to use rex max_match=0 or some high number depending on data needs to be added. I have added two approach in my answer 🙂