topic Re: How to combine two JsonArray into a single column? in Splunk Search

How to combine two JsonArray into a single column?

weiquanswq — Wed, 05 Oct 2016 09:41:47 GMT

HI!!!

I am trying to combine two JsonArray (Nextbus & SubsequentBus) to a single column.
I managed to extract both JsonArray but unable to append.

This is how my data looks like this :

Services: [ [-] 
     { [-] 
        NextBus: { [+] 
       } 
        Operator:  xxx 
        ServiceNo:  000
        SubsequentBus: { [+] 
       } 

     } 
     { [+] 
     } 
     { [+] 
     } 
     { [+] 
     } 
   ]

This is my search query :

 ... | spath  path=Services{} 
 | rename Services{}.ServiceNo AS name , Services{}.Operator AS type, Services{}.NextBus{}.ArrivingTime As ar,  Services{}.SubsequentBus{}.ArrivingTime As est_arr
 | eval x=mvzip(name,mvzip(type,arr) ) | mvexpand x 
 | eval y=mvzip(name,mvzip(type,est_arr)) | mvexpand y

Hope to get advice.
Thanks in advance! 🙂

Re: How to combine two JsonArray into a single column?

gokadroid — Mon, 10 Oct 2016 04:11:10 GMT

If its about placing the value of NextBus().ArrivingTime and SubsequentBus.ArrivingTime in same column , following way to append values can be explored:

... | spath  path=Services{} 
| rename Services{}.ServiceNo AS name , Services{}.Operator AS type, Services{}.NextBus{}.ArrivingTime As ar,  Services{}.SubsequentBus{}.ArrivingTime As est_arr
| eval arrivalTime="NextBus  : ".ar."      SubsequentBus : ".est_arr 
| table name, type, arrivalTime

Re: How to combine two JsonArray into a single column?

sundareshr — Mon, 10 Oct 2016 04:19:13 GMT

Try this..

*UPDATED*

 ... | spath  path=Services{} 
| rename Services{}.ServiceNo AS name , Services{}.Operator AS type, Services{}.NextBus{}.ArrivingTime As ar,  Services{}.SubsequentBus{}.ArrivingTime As est_arr
| eval x=mvzip(name,mvzip(type, mvzip(arr, est_arr)) ) 
| mvexpand x 
| table name type arr est_arr
| untable name type arrival_times

Re: How to combine two JsonArray into a single column?

weiquanswq — Tue, 11 Oct 2016 01:38:32 GMT

Sundareshr,
I am trying to combine both arr and est_arr columns into single column.

eg.

name | type | arrivalTime
123 | aa | 2016-10-11T10:00:00 <- Next Bus
456 | aa | 2016-10-11T10:05:00
123 | aa | 2016-10-11T10:07:00 <- Subsequent Bus

Re: How to combine two JsonArray into a single column?

sundareshr — Tue, 11 Oct 2016 02:23:56 GMT

Try the updated query

Re: How to combine two JsonArray into a single column?

weiquanswq — Wed, 12 Oct 2016 09:21:13 GMT

I am trying to extract both ArrivingTiming for both "nextBus" and " SubsequentBus". then add them below one another.

Hope this is clearer.
eg.

name	arrivalTime
123	2016-10-11T10:00:00 <- Next Bus

----------------+----------------------------------------------------
456 | 2016-10-11T10:05:00 <- Next Bus
----------------+----------------------------------------------------
123 | 2016-10-11T10:07:00 <- Subsequent Bus
----------------+----------------------------------------------------
456 | 2016-10-11T10:12:00 <- Subsequent Bus
----------------+----------------------------------------------------