Archive
Highlighted

Referring to array elements by index

Explorer

Hi,
I am trying to take each field out of array in json, can someone please help? My problem is that I want the elemets to be extracted based on index as I know one of the elements could have different field name like below

Root [
{ Name: "Brian", School :"KVG" }.
{ Name: "Steve", School :"MKG" }.
{ Name: "Gerry", School :"KG" }.
{LastName: "Awkward Todd", School :"Illeterate" }
]

How can I get these fields based on index? so that it looks like this:

Name School
Brian KVG
Steve MKG
Gerry KG

and separately in other table I can show :
LastName School
Awkward Todd Illeterate

Thanks !

0 Karma
Highlighted

Re: Referring to array elements by index

SplunkTrust
SplunkTrust

Your JSON is not standard and I don't know if the number of items on each element of the array will always be 2 or it might change, so I've written something generic but a maybe unnecessarily complex. See below and you can obviously ignore the first lines up to the first rex, which is what I used to replicate your use case:

| stats count | fields - count
| eval _raw = "
   Root [
      { Name: \"Brian\", School :\"KVG\" }.
      { Name: \"Steve\", School :\"MKG\" }.
      { Name: \"Gerry\", School :\"KG\" }.
      {LastName: \"Awkward Todd\", School :\"Illeterate\" }
   ]
"
| rex field=_raw max_match=0 "(?mi)\{\s*(?<keyvalue>.+)\s*\}\.?\n"
| mvexpand keyvalue
| streamstats count as N
| eval keyvalue = split(keyvalue, ",")
| mvexpand keyvalue
| rex field=keyvalue max_match=0 "(?msi)(?<key>\w+)[\s:\"]+(?<value>[^\"]+)"
| eval {key} = value
| fields - keyvalue, key, value
| stats first(*) as * by N, _raw
| table Name, School, LastName

Output (see image below, simply play with the table command to get what you want):

alt text

View solution in original post

0 Karma
Highlighted

Re: Referring to array elements by index

Explorer

Thanks for reply, I am quite new to splunk and regex and trying to understand what you have answered. also realized that there could be more than two elements i.e. one of the row could have something like this:

{ Name: "Gerry", School :"KG" , "Address": Alabama }

Does it matter if any of key values are in quote? My all keys are in double quote but values may or may not be in double quote.

Thank you !

0 Karma
Highlighted

Re: Referring to array elements by index

Explorer

Thanks it worked !!! Thannk you so much !

0 Karma