Splunk Search

how to get specific item in a nested json array

swin88
Engager

I have a field named "test" which has the following json. If I do:
| fields
test{}.data{}{}.metric,
test{}.data{}{}.value,
| table test{}.data{}{}.metric perfdata{}.data{}{}.value
I get (first item) a, 20 and b, 10  etc. in my table

How do I search to get (second item) p, 50 and q, 60 etc. in my table? Trying test{}[1] did not work for me. 
Thanks.

 

 

 

[
  {
    "data": [
      [
        {
          "metric": "a",
          "variables": {
            "Task": "x"
          },
          "value": 20
        },
        {
          "metric": "b",
          "variables": {
            "Task": "y"
          },
          "value": 10
        },
        {
          "metric": "c",
          "variables": {
            "Task": "z"
          },
          "value": 745
        }
      ]
    ]
  },
  {
    "data": [
      [
        {
          "metric": "p",
          "variables": {
            "Task": "e"
          },
          "value": 50
        },
        {
          "metric": "q",
          "variables": {
            "Task": "f"
          },
          "value": 60
        },
        {
          "metric": "r",
          "variables": {
            "Task": "g"
          },
          "value": 70
        }
      ]
    ]
  }
]

 

 

 

 

0 Karma
1 Solution

to4kawa
Ultra Champion
index=_internal | head 1 | fields _raw
| eval _raw="[{\"data\":[[{\"metric\":\"a\",\"variables\":{\"Task\":\"x\"},\"value\":20},{\"metric\":\"b\",\"variables\":{\"Task\":\"y\"},\"value\":10},{\"metric\":\"c\",\"variables\":{\"Task\":\"z\"},\"value\":745}]]},{\"data\":[[{\"metric\":\"p\",\"variables\":{\"Task\":\"e\"},\"value\":50},{\"metric\":\"q\",\"variables\":{\"Task\":\"f\"},\"value\":60},{\"metric\":\"r\",\"variables\":{\"Task\":\"g\"},\"value\":70}]]}]"
| spath {}.data{}{} output=data
| stats count by data
| spath input=data
| table metric variables* value

View solution in original post

0 Karma

to4kawa
Ultra Champion
index=_internal | head 1 | fields _raw
| eval _raw="[{\"data\":[[{\"metric\":\"a\",\"variables\":{\"Task\":\"x\"},\"value\":20},{\"metric\":\"b\",\"variables\":{\"Task\":\"y\"},\"value\":10},{\"metric\":\"c\",\"variables\":{\"Task\":\"z\"},\"value\":745}]]},{\"data\":[[{\"metric\":\"p\",\"variables\":{\"Task\":\"e\"},\"value\":50},{\"metric\":\"q\",\"variables\":{\"Task\":\"f\"},\"value\":60},{\"metric\":\"r\",\"variables\":{\"Task\":\"g\"},\"value\":70}]]}]"
| spath {}.data{}{} output=data
| stats count by data
| spath input=data
| table metric variables* value
0 Karma

swin88
Engager

Thank you.

0 Karma
Get Updates on the Splunk Community!

Leveraging Detections from the Splunk Threat Research Team & Cisco Talos

  Now On Demand  Stay ahead of today’s evolving threats with the combined power of the Splunk Threat Research ...

New in Splunk Observability Cloud: Automated Archiving for Unused Metrics

Automated Archival is a new capability within Metrics Management; which is a robust usage & cost optimization ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...