In Splunk, Need to Pull Data from Nested JSON Array in an Array

Question

I have some data that is an array inside an array. If it makes it easier, there will only ever be the one nested array inside of StopData.

{
  "Name": "ExceptionLogs",
  "Id": "Id1",
  "StopData": [
    [
      {
        "level": "ERROR",
        "code": "UCV019",
        "description": "Invalid port type",
        "detail": "100000 Mbps"
      },
      {
        "level": "WARN",
        "code": "UCV023",
        "description": "Unrecognized key for config option",
        "detail": "sourceSystemProductName"
      }
    ]
  ]
}

I would like to have the fields of objects in a table, level, code, description, and detail. Also, this is one row in splunk. There will be many rows where the Id field will be different. And there can be many entries in the array. I'd like to have all the rows in one long table.

Id  level code   description         detail
--- ----- ----   -----------         ------
Id1 ERROR UCV019 Invalid port type   100000 Mbps
Id1 WARN  UCV023 Unrecognized key... sourceSystemProductName
Id2 ERROR UCL013 Invalid Config      334115687

I have tried searching and searching but I am unable to piece together anything that shows any data at all.

score 2 · Answer 1 · answered Nov 18 '22 at 14:01

Alternative Answer, Similar to Richg's answer That fixes an issue if any of the fields you need to extract in the json list do not exist in every element, they will not be extracted correctly.

Also this avoids MVZIP which can be a nightmare to add more fields to extract and works for all fields in the json list.

| spath 
| spath StopData{}{} output=StopData
| mvexpand StopData
| spath input=StopData
| fields - StopData _raw _time

Example 1:

  | makeresults 
  | eval _raw="{
    \"Name\": \"ExceptionLogs\",
    \"Id\": \"Id1\",
    \"StopData\": [
      [
        {
          \"level\": \"ERROR\",
          \"code\": \"UCV019\",
          \"description\": \"Invalid port type\",
          \"detail\": \"100000 Mbps\"
            },
        {
          \"level\": \"WARN\",
          \"code\": \"UCV023\",
          \"description\": \"Unrecognized key for config option\",
          \"detail\": \"sourceSystemProductName\",
          \"test\":\"123\"
        }
      ]
    ]
  }"

Example 2:

| makeresults 
| eval _raw="{
  \"Name\": \"ExceptionLogs\",
  \"Id\": \"Id1\",
  \"StopData\": [
    [
      {
        \"level\": \"ERROR\",
        \"code\": \"UCV019\",
        \"description\": \"Invalid port type\",
        \"detail\": \"100000 Mbps\"
          },
      {
        \"level\": \"WARN\",
        \"code\": \"UCV023\",
        \"description\": \"Unrecognized key for config option\",
        \"detail\": \"sourceSystemProductName\",
        \"test\":\"123\"
      },
      {
        \"level\": \"INFO\",
        \"code\": \"UCV021\",
        \"description\": \"Price key for config option\",
        \"detail\": \"sourceSystemProductPrice\",
        \"test\":\"456\"
      }
    ]
  ]
}"

score 1 · Accepted Answer · answered Nov 16 '22 at 01:17

It would help to know what searches you've tried so we can help you learn why you got no results.

Start with the spath command to parse the JSON data into fields. That will give you a few multi-value fields for each Id. If we only had a single multi-value field then we'd use mvexpand to break it into separate events, but that won't work with several fields. To work around that, use mvzip to combine all multi-value fields into a single multi-value field then expand it and unzip it.

Try this run-anywhere example query.

| makeresults | eval _raw="{
  \"Name\": \"ExceptionLogs\",
  \"Id\": \"Id1\",
  \"StopData\": [
    [
      {
        \"level\": \"ERROR\",
        \"code\": \"UCV019\",
        \"description\": \"Invalid port type\",
        \"detail\": \"100000 Mbps\"
      },
      {
        \"level\": \"WARN\",
        \"code\": \"UCV023\",
        \"description\": \"Unrecognized key for config option\",
        \"detail\": \"sourceSystemProductName\"
      }
    ]
  ]
}" 
```Everything above is just test data.  Don't use IRL```
```Parse the JSON```
| spath 
```Simplify the field names```
| rename "StopData{}{}.*" as *
```Combine the multi-value fields```
| eval deets=mvzip(level,mvzip(code, mvzip(description,detail)))
```Create separate events for the fields in each Id```
| mvexpand deets
```Unzip the multi-value fields```
| eval deets=split(deets,",")
| eval level=mvindex(deets,0), code=mvindex(deets,1), description=mvindex(deets,2), detail=mvindex(deets,3)
```Display the results```
| table Id level code   description         detail

Thank you very much. This works perfectly. My attempts were nowhere near this fully working answer. Maybe someday my splunk-fu will be worthy, but I'm still just a grasshopper. — stricq, Nov 16 '22 at 16:00
This search only works if the following property holds: The fields needing to be extracted (eg level code...) are present in every element in the list and none of the fields are NULL in any but the last element in list. If we added a test=123 to the json in the second element, both event would have this test value ( as only 1 test, after spath this would be a single value field, the MVZIP would give this field to every event) ** added correction — Daniel Price, Nov 18 '22 at 13:58

In Splunk, Need to Pull Data from Nested JSON Array in an Array

2 Answers2