Splunk Search

How to combine two JsonArray into a single column?

weiquanswq
Explorer

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! 🙂

0 Karma

sundareshr
Legend

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
0 Karma

weiquanswq
Explorer

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

0 Karma

sundareshr
Legend

Try the updated query

0 Karma

weiquanswq
Explorer

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
----------------+----------------------------------------------------

0 Karma

gokadroid
Motivator

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
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...