Extraction of value from nested multivalue field

[Poojitha]

Hi All,

I have a multivalue field that contains nested key value pair with key named as "Key" and Value named as "Value".

Example snippet :

 tags: [ [-]
     { [-]
       Key: Contact
       Value: abc@gmail.com
     }
     { [-]
       Key: Name
       Value: abc
     }

I want to extract only the Contact value from here i.e abc@gmail.com. I am trying with multivalue functions and spath. Still stuck here. Please help me.

Regards,
PNV

[Poojitha]

@inventsekar @dtburrows3   Thank you both for your reply. I was trying to use the spath command but was failing in extraction.

@dtburrows3 : The second method using for loop worked well. I am running this query against large set of query. Does using for loop and json functions has any limitation in that case ? Like results getting truncated and so ?

[inventsekar]

>>> I am running this query against large set of query. Does using for loop and json functions has any limitation in that case ? Like results getting truncated and so ?

1) may we know how large is the data set, so that we can suggest you better. 

2) foreach does not have any limitation, whereas the spath got 5000 characters limitation. but the docs has given this overriding method:

By default, the spath command extracts all the fields from the first 5,000 characters in the input field. If your events are longer than 5,000 characters and you want to extract all of the fields, you can override the extraction character limit for all searches that use the spath command. To change this character limit for all spath searches, change the extraction_cutoff setting in the limits.conf file to a larger value.

https://docs.splunk.com/Documentation/Splunk/9.1.2/SearchReference/Spath

https://docs.splunk.com/Documentation/Splunk/9.1.2/SearchReference/Foreach

PS - Upvotes / like / karma points are appreciated, thanks. 

[inventsekar]

Hi @Poojitha 

The Splunk command "spath" enables you to extract information from the structured data formats XML and JSON. The command Ref doc link is:

https://docs.splunk.com/Documentation/Splunk/9.1.2/SearchReference/Spath

Pls let us know if you are able to use the spath command (as seen in the previous reply)

or you could use direct "rex" command extract field values and do the stats.

but, the spath is the simplest option i think. pls let us know if you are ok with spath or not, thanks. 

[dtburrows3 · Accepted solution]

I dont know the complete path to the nested tags array but you can do something like this to target the value contained within the Contact Key in the MV json fields.

Something like this.

<base_search>
    | eval
        tags_json=spath(_raw, "tags{}"),
        contact=case(
            mvcount(tags_json)==1, if(spath(tags_json, "Key")=="Contact", spath(tags_json, "Value"), null()),
            mvcount(tags_json)>1, mvmap(tags_json, if(spath(tags_json, "Key")=="Contact", spath(tags_json, "Value"), null()))
            )
    | fields + _time, _raw, tags_json, contact

Below is a screenshot of an example on my local instance.

First we extract all json objects from the tags array as a multivalued field named "tags_json". From there you can use the mvmap() function to loop through the multivalue field and check each entry to see if the Key field value of the json object is equal to "Contact". If it is, then we know this is the json object we want to target the extraction of the "Value" key from. So we do an Spath specificly on that object and store the returned value as a field named "contact".

Option 2:
Another route to take (depending on the structure of your event and if it make sense to do i this way).
We can loop through each json object in the tags array and stuff the key/values into a temporary json object that we can then do a full spath against. This is a more exhaustive approach as apposed to the targeted one in the previous example.

SPL to do this would look something like this.

<base_search>
    | eval
        ``` extract array of json objects a multivalued field ```
        tags_json=spath(_raw, "tags{}"),
        ``` initialize the temporary json object that will hold all the key/value pairs contained within the tags array ```
        final_tag_json=json_object()
    ``` use the mode=multivalue foreach loop to loop through each entry in the multivalued field ```
    | foreach mode=multivalue tags_json
        [
            | eval
                ``` json_set() function will set up each Key/Value as a new key/value pair in the temporary json "final_tag_json" ```
                final_tag_json=json_set(final_tag_json, spath('<<ITEM>>', "Key"), spath('<<ITEM>>', "Value"))
            ]
    | fields - tags_json
    ``` full spath against the final_tag_json field ```
    | spath input=final_tag_json
    | fields - final_tag_json
    | fields + _time, _raw, Contact, Name

You can see in this screenshot that not only is the "Contact" field extracted but "Name" value is extracted as well, this method would loop through each json array and extract a new Key/Value pair for each entry.

Below is a screenshot showing what the temporary final_tag_json object looks like that we did the full spath against for context.