Evaluating content of a list of JSON key/value pairs in search

[pracsys]

I have a search where 2 of the fields returned are based on the following JSON structure:

"tags": [

        {

            "key": "My Key to Search For",

            "value": "The value I want to see",

        },

        {

            "key": "Some other key",

            "value": "some value",

        }]

I can get the data in a table, eg:  

|table asset,tags{}.key,tags{}.value

In my search this will list all my assets, each with their respective tag keys and values as lists in their own fields. 

asset	tags{}.key	tags{}.value
asset_001	[TAG_001, TAG_002]	[VALUE_001, VALUE002]
asset_002	[TAG_001]	[VALUE_001]

I now want to create a new field based on these tags, where:

mynewfield = tags{}.value where tags{}.key = "My Key to Search For"

so that:

asset	mynewfield
asset_001	VALUE_002
asset_002	NONE

I tried using eval and mvfilter but I cannot seem to get the statements right, and I'm sure I'm missing something. 

Can anyone shed some light on how to do this in a Splunk search? 

[pracsys]

Thank you both for helping me to understand this better

Based on @manjunathmeti 's answer, what worked in my search was the following:

| eval idx=mvfind('tags{}.key', "TAG_002"), mynewfield=mvindex('tags{}.value', idx) 

I also hadn't realised that in eval my lists had to be enclosed in quotes as they contain special characters. 

@scelikok 's answer is what I was trying to get to, but even with fixing quotes, I kept getting errors in the eval syntax.  I finally got to the following, but that still gives me all the values in the list IF my wanted key was present:

| eval mynewfield = if(mvfind('tags{}.key', "My Key to Search For")>0,'tags{}.value',null())

I realise now that mvfind returns an index, and that we need to be able to use that same index to retrieve the value in list 2. 

[manjunathmeti · Accepted solution]

hi @pracsys,
Check if this works:

| eval tags_key='tags{}.key', tags_value='tags{}.value'
| eval tags_key=replace(tags_key, "\[|\]|\s", ""), tags_value=replace(tags_value, "\[|\]|\s", "") 
| eval tags_key=split(tags_key, ","), tags_value=split(tags_value, ",") 
| eval idx=mvfind(tags_key, "TAG_002"), mynewfield=mvindex(tags_value, idx) 
| fields asset, mynewfield
| fillnull value=NONE mynewfield

If this reply helps you, an upvote/like would be appreciated.

[scelikok]

Hi @pracsys,

Please try below;

| eval mynewfield = if(mvfind(tags{}.key, "My Key to Search For"),tags{}.value,null())