topic Re: How to convert JSON array of Key/Value pairs to Column/Value? in Getting Data In

How to convert JSON array of Key/Value pairs to Column/Value?

mlevsh — Sun, 19 May 2019 18:47:38 GMT

Lets say we have Json data in the following format ( using 2 events as an example)

Event 1)

Time    Event 5/19/19 2:26:06.730 PM    
{ [-] 
   account_id: 1234567890 
   created_time: 2019-05-16T15:00:45.000Z 
   region: us-1
   tags: [ [-] 
     { [-] 
       Key: environment 
       Value: env1 
     } 
     { [-] 
       Key: data_class 
       Value: Class1 
     } 
     { [-] 
       Key: built_by 
       Value: group1
     } 
     { [-] 
       Key: description 
       Value: anything 
     } 
     { [-] 
       Key: application_id 
       Value: abc 
     } 
}

Event 2:

5/19/19 2:26:06.620 PM  
{ [-] 
   account_id: 1234567891 
   created_time: 2019-05-13T21:31:02.710Z 
   region: us-1 
   tags: [ [-] 
     { [-] 
       Key: environment 
       Value: env2 
     } 
     { [-] 
       Key: data_class 
       Value: Class1 
     } 
     { [-] 
       Key: built_by 
       Value: group1
     } 
     { [-] 
       Key: description 
       Value: anything 
     } 
     { [-] 
       Key: application_id 
       Value: def 
     } 
   ]

The objective to display it in the following format: each Key in Key/Value pair needs to be a Column header and each Value needs to belong to corresponding Header(Key) , for example the above data needs to be in the following format

Environment       data_class      build_by      description      application_id
env1              Class1          group1      anything            abc
env2              Class2          group1      anything            def

Any ideas will be appreciated!

Re: How to convert JSON array of Key/Value pairs to Column/Value?

koshyk — Sun, 19 May 2019 19:26:58 GMT

Your payload is not pure JSON, but a mixed message of timestamp && JSON
So there are two steps
1. Do at indextime for extracting timestamp and line breaker etc.
2. Do at searchtime for pure JSON message using REPORT-

The link https://answers.splunk.com/answers/117121/extract-json-data-within-the-logs-json-mixed-with.html will explain you how to do it using props & transforms.

Alternatively, if you Just wanted to do at Search time, you can use SPATH command. Feed the pure JSON into spath command http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/spath

Re: How to convert JSON array of Key/Value pairs to Column/Value?

kamlesh_vaghela — Mon, 20 May 2019 05:58:02 GMT

@mlevsh

Can you please try this?

index=YOUR_INDEX | rename tags{}.Key as Keys, tags{}.Value as Values | eval temp=mvzip(Keys,Values) | mvexpand temp | eval Key=mvindex(split(temp,","),0),Value=mvindex(split(temp,","),1) | table account_id Key Value | eval {Key} = Value | stats values(*) as * by account_id | fields - Key, Value

My Sample Search:

| makeresults | eval _raw="{\"account_id\": \"1234567890\",\"created_time\": \"2019-05-16T15:00:45.000Z\",\"region\": \"us-1\",\"tags\": [ {\"Key\": \"environment\",\"Value\": \"env1\" }, {\"Key\" :\"data_class\",\"Value\": \"Class1\"},{\"Key\": \"built_by\",\"Value\": \"group1\" },{\"Key\": \"description\",\"Value\": \"anything\" },{\"Key\":\"application_id\",\"Value\": \"abc\" }] }" | kv | rename tags{}.Key as Keys, tags{}.Value as Values | eval temp=mvzip(Keys,Values) | mvexpand temp | eval Key=mvindex(split(temp,","),0),Value=mvindex(split(temp,","),1) | table account_id Key Value | eval {Key} = Value | stats values(*) as * by account_id | fields - Key, Value

Thanks

Re: How to convert JSON array of Key/Value pairs to Column/Value?

mlevsh — Mon, 20 May 2019 16:09:19 GMT

@koshyk,
We are running Slunk Cloud and cannot easily make any modifications on Indexers or Search heads

We did tried to use spath command, but were not successful so far

Re: How to convert JSON array of Key/Value pairs to Column/Value?

to4kawa — Sat, 11 Jan 2020 05:10:45 GMT

Sample query:

| makeresults 
| eval _raw="{\"account_id\": \"1234567890\",\"created_time\": \"2019-05-16T15:00:45.000Z\",\"region\": \"us-1\",\"tags\": [ {\"Key\": \"environment\",\"Value\": \"env1\" }, {\"Key\" :\"data_class\",\"Value\": \"Class1\"},{\"Key\": \"built_by\",\"Value\": \"group1\" },{\"Key\": \"description\",\"Value\": \"anything\" },{\"Key\":\"application_id\",\"Value\": \"abc\" }] }" 
| spath
| eval _raw=mvzip('tags{}.Key','tags{}.Value',"=")
| kv
| fields - _* tags*

Recommend:

your_search
| spath
| eval _raw=mvzip('tags{}.Key','tags{}.Value',"=")
| kv
| fields - _* tags*