How to parse my JSON data with spath and table the data?

[deepak312]

I am trying to parse this json using spath,

{
  "Class": "11",
  "date": "05/16/2016",
  "Student": [
    {
      "RollNo": "1234",
      "SubjectDetails": [
        {
          "type": "Mandatory",
          "startTime": "05/16/2016 21:30",
          "endTime": "05/16/2016 22:00",
          "name": "English"
        },
        {
          "type": "Optional",
          "startTime": "05/16/2016 20:00",
          "endTime": "05/16/2016 21:30",
          "name": "Math"
        }
      ]
    }
  ]
}

I am trying to plot it in a table. Any help will be appreciated.

[ddelapasse]

Can someone please tell me why this answer isn't working in my 7.1.3? I only get one row instead of the two rows shown above. I'm brand new to Splunk, but this is the 3rd similar example I've tried that is supposed to render multiple rows but does not for me.

| makeresults
 | eval _raw="{\"Class\":\"11\",\"date\":\"05/16/2016\",\"Student\":[{\"RollNo\":\"1234\",\"SubjectDetails\":[{\"type\":\"Mandatory\",\"startTime\":\"05/16/2016 21:30\",\"endTime\":\"05/16/2016 22:00\",\"name\":\"English\"},{\"type\":\"Optional\",\"startTime\":\"05/16/2016 20:00\",\"endTime\":\"05/16/2016 21:30\",\"name\":\"Math\"}]}]}"
| spath | rename Student{}.SubjectDetails{}.name AS name, Student{}.SubjectDetails{}.type AS type, Student{}.SubjectDetails{}.endTime as endTime, Student{}.SubjectDetails{}.startTime as startTime, Student{}.RollNo as RollNo 
 | eval a=mvzip(Class,RollNo) 
 | eval b=mvzip(date,RollNo) 
 | eval c=mvzip(RollNo,name) 
 | eval d=mvzip(name,type) 
 | eval e=mvzip(name,startTime) 
 | eval f=mvzip(name,endTime) 
 | eval g=mvzip(a,b) 
 | eval h=mvzip(c,d) 
 | eval i=mvzip(e,f) 
 | eval j=mvzip(g,h) 
 | eval k=mvzip(g,i) 
 | eval x=mvzip(j,k) 
 | mvexpand x 
 | eval x=split(x,",") 
 | eval Class=mvindex(x,0) 
 | eval RollNo=mvindex(x,1)
 | eval date=mvindex(x,2)
 | eval name=mvindex(x,5)
 | eval type=mvindex(x,7)
 | eval startTime=mvindex(x,13)
 | eval endTime=mvindex(x,15)
 | table  Class, RollNo, date, name, type startTime, endTime

[jkat54]

Not sure when the behavior changed, but now i have to mvexpand twice:

  | makeresults
  | eval _raw="{\"Class\":\"11\",\"date\":\"05/16/2016\",\"Student\":[{\"RollNo\":\"1234\",\"SubjectDetails\":[{\"type\":\"Mandatory\",\"startTime\":\"05/16/2016 21:30\",\"endTime\":\"05/16/2016 22:00\",\"name\":\"English\"},{\"type\":\"Optional\",\"startTime\":\"05/16/2016 20:00\",\"endTime\":\"05/16/2016 21:30\",\"name\":\"Math\"}]}]}"
  | spath 
  | rename Student{}.SubjectDetails{}.name AS name, Student{}.SubjectDetails{}.type AS type, Student{}.SubjectDetails{}.endTime as endTime, Student{}.SubjectDetails{}.startTime as startTime, Student{}.RollNo as RollNo
  | eval a=mvzip(name,type) 
  | mvexpand a
  | eval b=mvzip(startTime,RollNo) 
  | eval c=mvzip(a,b)
  | mvexpand c
  | eval x=split(c,",")
  | eval name=mvindex(x,0)
  | eval type=mvindex(x,1)
  | eval startTime=mvindex(x,2)
  | eval RollNo=mvindex(x,3)
  | table name, type, startTime, RollNo

[chlima]

I think it's a simplest answer:

yoursearch | spath input=your_json_field

Try it!

[woodcock]

This is a more general approach:

 | makeresults count=3
 | eval ExtendedProperties.Name="DisplayName IsPublic Mail"
 | makemv ExtendedProperties.Name
 | eval ExtendedProperties.Value=case(
    (serial=1), "Test Group::False::testgroup@microsoft.com",
    (serial=2), "Group1::True::group1@microsoft.com",
    (serial=3), "Group2::True::group2@microsoft.com")
 | makemv delim="::" ExtendedProperties.Value
 | fields - _time

 | rename COMMENT AS "Everything above is faking your data; everyting below is your solution"

 | streamstats count AS serial
 | eval KVP=mvzip('ExtendedProperties.Name', 'ExtendedProperties.Value', "=")
 | table KVP serial
 | mvexpand KVP
 | rex field=KVP "^(?<KVP_key>[^=]+)=(?<KVP_value>[^=]+)$"
 | eval {KVP_key}=KVP_value
 | fields - KVP* 
 | stats values(*) AS * BY serial

[jkat54 · Accepted solution]

Hello,

This seems to work with your data:

...
| spath 
| rename Student{}.SubjectDetails{}.name AS name, Student{}.SubjectDetails{}.type AS type, Student{}.SubjectDetails{}.endTime as endTime, Student{}.SubjectDetails{}.startTime as startTime, Student{}.RollNo as RollNo 
| eval a=mvzip(Class,RollNo) 
| eval b=mvzip(date,RollNo) 
| eval c=mvzip(RollNo,name) 
| eval d=mvzip(name,type) 
| eval e=mvzip(name,startTime) 
| eval f=mvzip(name,endTime) 
| eval g=mvzip(a,b) 
| eval h=mvzip(c,d) 
| eval i=mvzip(e,f) 
| eval j=mvzip(g,h) 
| eval k=mvzip(g,i) 
| eval x=mvzip(j,k) 
| mvexpand x 
| eval x=split(x,",") 
| eval Class=mvindex(x,0) 
| eval RollNo=mvindex(x,1)
| eval date=mvindex(x,2)
| eval name=mvindex(x,5)
| eval type=mvindex(x,7)
| eval startTime=mvindex(x,13)
| eval endTime=mvindex(x,15)
| table  Class, RollNo, date, name, type startTime, endTime

This was my first time successfully parsing json this way... I used this to learn how to do it: http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/Spath

[deepak312]

Thank You !! Worked like a charm.

[jkat54]

Many thanks to you for the opportunity to learn how to parse json... now if only i had known this a couple years ago 😉