How to extract all occurrences of a field from JSON with a repeating structure?

[roshannon]

I have the following JSON and am looking to extract all of the occurrences of Lat and Long. I have used a macro defined as

rex "(?<json_field>{[^}]+})" 

per previous answer http://answers.splunk.com/answers/79029/part-1-how-to-extract-a-json-portion-of-an-event-then-use-sp...
However, when I try to search with:

... | `my_macro`| mvexpand json_field | spath input=json_field | table   GpsUpdateRequest.GPSUpdateGroup{}.Lat GpsUpdateRequest.GPSUpdateGroup{}.Long

only the first occurrence is returned. How can I get all occurrences? Also, is there a way to get only the first and last as well?

{"GpsUpdateRequest":
 {"StartLocation":"ABC","UserId":"55555","DeviceId":"1234567890123","TripNo":0,"GPSUpdateGroup":
  [{"Lat":45.55714442,"Long":-122.70291961,"Speed":5,"Elevation":-16.4,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:24:37-00:00"},
   {"Lat":45.55657249,"Long":-122.70304847,"Speed":5,"Elevation":-59.1,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:25:43-00:00"},
   {"Lat":45.5562907,"Long":-122.69994322,"Speed":5,"Elevation":-52.5,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:26:04-00:00"},
   {"Lat":45.5559368,"Long":-122.6947226,"Speed":9,"Elevation":-32.8,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:26:35-00:00"},
   {"Lat":45.55589454,"Long":-122.68949185,"Speed":4,"Elevation":13.1,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:26:59-00:00"},
   {"Lat":45.55594183,"Long":-122.68454893,"Speed":2,"Elevation":45.9,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:27:25-00:00"},
   {"Lat":45.55605582,"Long":-122.68327135,"Speed":10,"Elevation":75.5,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:27:39-00:00"},
   {"Lat":45.55613833,"Long":-122.68113534,"Speed":5,"Elevation":118.1,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:28:22-00:00"},
   {"Lat":45.55606329,"Long":-122.678692,"Speed":6,"Elevation":137.8,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:28:37-00:00"},
   {"Lat":45.55590463,"Long":-122.67817822,"Speed":6,"Elevation":137.8,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:28:41-00:00"},
   {"Lat":45.55531425,"Long":-122.67833457,"Speed":7,"Elevation":108.3,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:29:06-00:00"},
   {"Lat":45.55894644,"Long":-122.67843673,"Speed":3,"Elevation":105.0,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:29:31-00:00"},
   {"Lat":45.56637296,"Long":-122.67849605,"Speed":5,"Elevation":108.3,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:30:07-00:00"},
   {"Lat":45.57367101,"Long":-122.67855006,"Speed":5,"Elevation":78.7,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:30:40-00:00"},
   {"Lat":45.5811205,"Long":-122.67842766,"Speed":5,"Elevation":39.4,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:31:14-00:00"},
   {"Lat":45.5841273,"Long":-122.67899733,"Speed":4,"Elevation":32.8,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:31:28-00:00"},
   {"Lat":45.59111136,"Long":-122.6821438,"Speed":5,"Elevation":-13.1,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:32:01-00:00"},
   {"Lat":45.59615269,"Long":-122.68411631,"Speed":6,"Elevation":-29.5,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:32:26-00:00"},
   {"Lat":45.59988899,"Long":-122.68355645,"Speed":3,"Elevation":-23.0,"NodeSeq":0,"EventTimestamp":"2015-09-28T19:32:47-00:00"}
  ]
 }
}

[vasanthmss]

Hi Roshanon,

Check this one, The below search will help you to extract all the fields in the JSON, NOTE: This will work only all the fields are presents in the events.

index=blah | table _raw | spath | rename GpsUpdateRequest.* as * GpsUpdateRequest.GPSUpdateGroup{}.* as * | eval first_Lat=mvindex(Lat,0) | eval first_Long=mvindex(Long,0) | eval end_lat=mvindex(Lat,mvcount(Lat)-1) | eval end_long=mvindex(Long,mvcount(Long)-1)  | eval temp=mvzip(Elevation,mvzip(EventTimestamp,mvzip(Lat, mvzip(Long,mvzip(NodeSeq, Speed,"###"),"###"),"###"),"###"),"###") | mvexpand temp  | rex field=temp "(?<Elevation>.*)###(?<EventTimestamp>.*)###(?<Lat>.*)###(?<Long>.*)###(?<NodeSeq>.*)###(?<Speed>.*)" | fields - _raw, temp

I understand that you want to keep the first and last (lat and long) in each fields, if that is not the case remove the blow code,

| eval first_Lat=mvindex(Lat,0) | eval first_Long=mvindex(Long,0) | eval end_lat=mvindex(Lat,mvcount(Lat)-1) | eval end_long=mvindex(Long,mvcount(Long)-1)

Hope this will helps you.

Thanks,
V