JSON extraction

[psamuel69]

Hello Expert Splunk Community ,

I am struggling with a JSON extraction .
Need help/advice on how to do this operation

Data Sample :

[
{
"orderTypesTotal": [
{
"orderType": "Purchase",
"totalFailedTransactions": 0,
"totalSuccessfulTransactions": 0,
"totalTransactions": 0
},
{
"orderType": "Sell",
"totalFailedTransactions": 0,
"totalSuccessfulTransactions": 0,
"totalTransactions": 0
},
{
"orderType": "Cancel",
"totalFailedTransactions": 0,
"totalSuccessfulTransactions": 1,
"totalTransactions": 1
}
],
"totalTransactions": [
{
"totalFailedTransactions": 0,
"totalSuccessfulTransactions": 1,
"totalTransactions": 1
}
]
}
]

[
{
"orderTypesTotal": [
{
"orderType": "Purchase",
"totalFailedTransactions": 10,
"totalSuccessfulTransactions": 2,
"totalTransactions": 12
},
{
"orderType": "Sell",
"totalFailedTransactions": 1,
"totalSuccessfulTransactions": 2,
"totalTransactions": 3
},
{
"orderType": "Cancel",
"totalFailedTransactions": 0,
"totalSuccessfulTransactions": 1,
"totalTransactions": 1
}
],
"totalTransactions": [
{
"totalFailedTransactions": 11,
"totalSuccessfulTransactions": 5,
"totalTransactions": 16
}
]
}
]

I have the above event coming inside a field in _raw events . using json(field) i have validated that the above is a valid json .

UseCase :

I need to have the total of all the different ordertypes using totalFailedTransactions": ,
"totalSuccessfulTransactions": ,
"totalTransactions": numbers into a table .

	totalFailedTransactions	totalSuccessfulTransactions	totalTransactions
Purchase	10	2	12
Sell	1	2	3
Cancel	0	2	2

Thanks in advance!
Sam

[ITWhisperer · Accepted solution]

| spath {}.orderTypesTotal{} output=orderTypesTotal
| mvexpand orderTypesTotal
| spath input=orderTypesTotal
| stats sum(totalFailedTransactions) as totalFailedTransaction sum(totalSuccessfulTransactions) as totalSuccessfulTransactions sum(totalTransactions) as totalTransactions by orderType

[psamuel69]

Thanks for your quick response .

I tried using spath aswell .
But it seems that the field is not getting extracted in between , as the error suggests ..

Spoiler

Field 'orderTypesTotal' does not exist in the data.

Do you think an extracted json would have an issue where as a raw json would work with spath ?

As my json payload is created only after adding extraction via a regex on a raw event . 

[ITWhisperer]

That is difficult to determine since you haven't shared your raw event nor how you have extracted the JSON part.

[psamuel69]

eval'ed my extracted payload to _raw and voila , it works !!!

Thanks a lot for your time and expertise !

[ITWhisperer]

If your JSON was already in a field, you could have used the input parameter to spath (this defaults to _raw)

| spath input=<your field> ...