How to get specific value from key/value array to form table?

[btsr]

Hi All,

I don't have much experience with Splunk. My JSON payload looks like as shown below. The msg.details array can have any number key/value pairs in any order.

{
  "appName": "TestApp",
  "eventType": "Response",
  "msg": {
    "transId": "Trans1234",
    "status": "Success",
    "client": "clientXyz",
    "responseTime": 1650,
    "details": [
      {
        "keyName": "rtt",
        "keyValue": 2778
      },
      {
        "keyName": "trace",
        "keyValue": 97007839130680
      }
    ],
    "url": "/v1/test"
  }
}

 I am trying to write a query and form a table as shown below. I am interested in displaying only the keyValue of keyName:trace in the table. Any help is appreciated. Thanks.

index=* appName="TestApp" msg.url="/v1/test" | table msg.transId, msg.status, msg.details[keyName="trace"].keyValue

msg.transId	msg.status	msg.details[keyName="trace"].keyValue
Trans1234	Success	97007839130680
Trans7890	ERROR	29411645500355

[ITWhisperer · Accepted solution]

Another way to do it is to extract and expand the collection

| spath
| spath msg.details{} output=details
| mvexpand details
| spath input=details
| where keyName="trace"
| table msg.transId msg.status keyValue

[btsr]

This saved my day! Thank you @ITWhisperer!

[richgalloway]

Splunk doesn't recognize square brackets as denoting a JSON array; they identify a subsearch.

We also can't reference a JSON array element by name.  Perhaps some day...

Here's a run-anywhere query showing one way to accomplish the task.

| makeresults 
| eval _raw="{
  \"appName\": \"TestApp\",
  \"eventType\": \"Response\",
  \"msg\": {
    \"transId\": \"Trans1234\",
    \"status\": \"Success\",
    \"client\": \"clientXyz\",
    \"responseTime\": 1650,
    \"details\": [
      {
        \"keyName\": \"rtt\",
        \"keyValue\": 2778
      },
      {
        \"keyName\": \"trace\",
        \"keyValue\": 97007839130680
      }
    ],
    \"url\": \"/v1/test\"
  }
}" | spath 
``` Everything above sets up test data.  Delete IRL. ```
``` Combine keyName and keyValue so we can work with them as a pair. ```
| eval foo=mvzip('msg.details{}.keyName','msg.details{}.keyValue')
``` Locate "trace" keys ```
| eval foo=mvindex(foo,mvfind(foo,"trace"))
``` Break up the keyName/keyValue pair for display ```
| eval foo=split(foo,",")
| eval keyName=mvindex(foo,0), keyValue=mvindex(foo,1)
| table msg.transId, msg.status, keyName, keyValue