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.
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
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
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
I think it's a simplest answer:
yoursearch | spath input=your_json_field
Try it!
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
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
Thank You !! Worked like a charm.
Many thanks to you for the opportunity to learn how to parse json... now if only i had known this a couple years ago 😉