Json Parsing.

[onthakur]

I have below json and I want table of url and corresponding duration.

{
"details": {
"sub-trans": [
{
"app-trans-id": "123",
"sub-trans-id": "234",
"startTime": "2024-01-18T12:37:12.482Z",
"endTime": "2024-01-18T12:37:12.502Z",
"duration": 20,

"req": {
"url": "http://abc123",

},
{
"app-trans-id": "123",
"sub-trans-id": "567",
"startTime": "2024-01-18T12:37:12.506Z",
"endTime": "2024-01-18T12:37:12.550Z",
"duration": 44,

"req": {
"url": "https://xyz567",

},
]
}
}

I am using below splunk query but duration field is not populating in table.

Kindly help

index=hello
|spath output=url details.sub-trans{}.req.url| mvexpand url
|spath output=duration details.sub-trans{}.duration |mvexpand duration
|table url,duration

[dtburrows3]

By going off what you pasted it is coming back as an invalid JSON, I would check that first.

But assuming that it is just a copy/paste error and you do have a valid json object as _raw then I would probably do an spath like this to retain associations between url and durations.

index=hello
    | spath input=_raw path=details.sub-trans{} output=sub_trans
    | fields - _raw
    | table sub_trans
    | mvexpand sub_trans
    | spath input=sub_trans
    | fields - sub_trans

You can see here all the field are extracted and they maintained their relationships to their individual url/duration according to the structure of detail.sub-trans{} array. Does require an mvexpand though, just keep an eye out for memory limits.

To retain specific associations of the url to its respective duration by extracting both as individual multivalued fields is possible but can be problematic. If any of them have a null entry for whatever reason then all associations are thrown off from that point on. Thats why in these sort of situations I would much rather extract the entire nested json object out of the array, mvexpand that, then spath that internal json. 

Also want to note that doing a mvexpand against two multivalue fields like in your original search will completely loose all association between which url should have which duration. you will actually end up with N^2 results when by the structure of the json I believe there should only be N results.

[richgalloway · Accepted solution]

The duration field populates in my sandbox, but values are duplicated.  That's likely because the two mvexpand calls break the association between url and duration.  Try this query, instead:

index=hello
| spath output=url details.sub-trans{}.req.url
| spath output=duration details.sub-trans{}.duration
``` Combine url and duration ```
| eval pairs=mvzip(url,duration)
``` Put each pair into a separate event ```
| mvexpand pairs
``` Extract the url and duration fields ```
| eval pairs=split(pairs,","), url=mvindex(pairs,0), duration=mvindex(pairs,1)
| table url,duration

[onthakur]

Thanks you very much , your solution worked perfectly.