topic Re: How to parse my JSON data with spath and table the data? in Splunk Search

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

deepak312 — Fri, 13 May 2016 22:28:42 GMT

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.

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

jkat54 — Sun, 15 May 2016 11:46:09 GMT

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

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

deepak312 — Mon, 16 May 2016 19:42:38 GMT

Thank You !! Worked like a charm.

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

jkat54 — Mon, 16 May 2016 19:58:17 GMT

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

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

woodcock — Wed, 14 Jun 2017 14:37:03 GMT

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

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

chlima — Tue, 29 Sep 2020 17:09:17 GMT

I think it's a simplest answer:

yoursearch | spath input=your_json_field

Try it!

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

ddelapasse — Wed, 21 Nov 2018 15:15:30 GMT

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

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

jkat54 — Tue, 02 Apr 2019 15:27:39 GMT

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