How do I extract key value pairs from json file using regex?

[anooshac]

Hi all, I have a  sample json file like this.

{
"Project Name" : "abc",
"Project Group":"A",
"Unit":"B",
"groups_data":[{
"a":"32.064453125",
"b":"5.451171875",
"c":"0.3349609375",
"d":"0.181640625",
"e":"4.58203125",
"f":"81.1611328125"}]
}

I want to plot a pie chart for the key value pairs present in the groups_data. I tried extracting the data using this query.

myindex sourcetype="_json"| rex field=_raw "\"group_data\":\[\{\"(?<component>[^/]*)\":"\"(?<Value>\d+)\"\}\]| eval tmp = mvzip(component,Value) |mvexpand tmp |eval component=mvindex(split(tmp,","),0) |eval Value=mvindex(split(tmp,","),1)|chart values(Value) by component

I am not able to pie chart. It says tmp does not exist.Can anyone tell me is there anything wrong in the regex part? Something i missed anywhere?

[ITWhisperer]

Try extracting the groups_data (group_data?) with spath and then use max_match=0 for multiple extracts. Also, use a match string that matches your (example) data

myindex sourcetype="_json"
| spath output=groups_data groups_data{}
| rex field=groups_data max_match=0 "\"(?<component>[^\"]+)\":\"(?<Value>[\d\.]+)\""
| eval tmp = mvzip(component,Value) 
| mvexpand tmp 
| eval component=mvindex(split(tmp,","),0) 
| eval Value=mvindex(split(tmp,","),1)
| chart values(Value) by component

[anooshac]

Thank you so much it is working.. Is it mandatory to use spath while using regex?

Also some of the keys have comma because of key is splitting, how can i avoid this? How to escape comma present in key?

[ITWhisperer]

No, spath is not mandatory for use with rex, you could have extracted the groups_data in to a separate field with rex, then used another rex to extract the components and value pairs - the key here is the max_match=0 to apply the extract pattern multiple times.

Can you give an example of where this is failing (due to commas)?

[anooshac]

Okay.. Thanks alot for the information.

There are some values like below. 

"x,y":"32.064453125

this will be extracted as x and y as key and values. How can i extract "x,y"  as a key?

[ITWhisperer · Accepted solution]

Extract the pairs as tmp and split using the colon then trim the double-quotes

myindex sourcetype="_json"
| spath output=groups_data groups_data{}
| rex field=groups_data max_match=0 "(?<tmp>\"[^\"]+\":\"[\d\.]+\")"
| mvexpand tmp 
| eval component=trim(mvindex(split(tmp,":"),0),"\"")
| eval Value=trim(mvindex(split(tmp,":"),1),"\"")
| chart values(Value) by component

[jotne]

You could also extract component Value using regex.

| eval component=trim(mvindex(split(tmp,":"),0),"\"")
| eval Value=trim(mvindex(split(tmp,":"),1),"\"")

=

| rex field=tmp "\"(?<component>[^\"]+)\":\"(?<Value>[^\"]+)\""

[anooshac]

Thank you so much for the help! It is working fine now..