Extract json values from string json array

[mipa04]

Hi, my splunk search results in two fields - Time and Event. Inside Event field there are multiple searchable fields, one of which is json array as string like this:
params="[{'field1':'value1','field2':'value2','field3':'value3'}]"

Above json array always has one json object like in example. I need to extract values for given fields from this json object - how can i do that? I figured spath is the way to do this, but none of solutions I found so far worked - maybe because all examples were operating on json as string only and in my case it is in Event as splunk shows in search - can you help?

[ITWhisperer]

This isn't JSON as JSON uses double quotes not single quotes. Please post an accurate representation of the field you want to extract the data from.

Having said that, you should look at the json functions new to 9.x as these would probably be the basis of a solution.

[kamlesh_vaghela]

I agree with @ITWhisperer . 

and your sample JSON looks like an array of objects also. So please share a masked event here.  

Meanwhile, I'm sharing a sample query so that you can start on the solution. 

| makeresults 
| eval params="[{'field1':'value1','field2':'value2','field3':'value3','field4':'value4'}]"
| rename comment as "upto now is sample data only"
| eval params = replace(params, "'","\"")
| eval _raw = params
| spath

I have handled a single quote here.   if you have valid JSON then just remove `| eval params = replace(params, "'","\"")` .

Thanks 
KV 

[mipa04]

I pasted it the same as I see in splunk search results, field params is in double quotes, but inside there are single quotes. I know that json requires double quotes, but don't know if it is only matter of displaying in splunk search, or actually it is not proper json for splunk (source for this is database table, in which it is proper json array with double quotes)

[ITWhisperer]

Rather than pasting search results, please paste the raw event data, preferably in a code block </> to preserve original formatting.

[mipa04]

I don't know if that it is in Event top level field matters, so I'm pasting screenshot of raw data. Field in question is tlogParameters

[kamlesh_vaghela]

@mipa04 

I assume tlogParameters filed getting extracted properly.

Can you please try the below search?

YOUR_SEARCH
| eval tlogParameters = replace(tlogParameters, "'","\"")
| eval _raw = tlogParameters
| spath 
| rename {}.* as *

My Sample Search :

| makeresults 
| eval tlogParameters="[{'triggeredEventName': 'CustomerLoggedIn', 'owner': 'communicationcenter', 'channel': 'SiteMessage', 'sentOrNotSent': 'Sent', 'reasonNotSent':null}]" | append [| makeresults 
| eval tlogParameters="[{'triggeredEventName': 'CustomerLoggedIn', 'owner': 'communicationcenter', 'channel': 'SiteMessage', 'sentOrNotSent': 'Sent', 'reasonNotSent':null}]"]
| rename comment as "upto now is sample data only"
| eval tlogParameters = replace(tlogParameters, "'","\"")
| eval _raw = tlogParameters
| spath 
| rename {}.* as *

 I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

[mipa04]

Thanks for response, but unfortunately it doesn't work - 

YOUR_SEARCH
| eval tlogParameters = replace(tlogParameters, "'","\"")

this doesn't change anything - ie tlogParameters is still displayed in raw as single quotes and surrounded by double quotes as original. 

YOUR_SEARCH
| eval tlogParameters = replace(tlogParameters, "'","\"")
| eval _raw = tlogParameters

 this makes empty result (the same as full query you proposed):

[ITWhisperer]

Please paste the raw event data, preferably in a code block </> to preserve original formatting.