Extract json fields

[shaunm001]

How do I extract these name/value elements from the "DeviceProperties" field below? Need it to be in table format such that the column names are the "Name" values and the rows of each column are the "Value" values. 

[yuanliu · Accepted solution]

First off, please post raw text to illustrate data (not Splunk's contracted display).  You already get a fields DeviceProperties{}.Name and DeviceProperties{}.Value.  There are several ways to transform that into the table format you want, one of which do not require any field extraction.  But let me use extraction - because when dealing with structured data such as JSON, it is important to not attempt to text extraction such as regex.

DeviceProperties is an array.  That is why Splunk flattens it into the notation of {}.   The most straightforward method is spath command toward this array, run mvexpand over the array so they become single-valued hash elements, then run spath over these elements:

| spath path=DeviceProperties{}
| mvexpand DeviceProperties{}
| spath input=DeviceProperties{}

This will give you a field called Name, and a field called Value.  All you need to do is to transpose it.  So, you add

| table Name Value
| transpose 0 header_field=Name column_name=_

This, of course is assuming that you only have one event.  If you have multiple events, you must have a unique event key for each row. (When asking a question, these constraints must be clearly stated.  Asking volunteers to read your mind is never a good idea.)  Assuming that unique  key is called "UniqueKey" for each event (it could be a combination of existing fields, just like in SQL), you can use xyseries instead of transpose.

| xyseries UniqueKey Name Value

[marnall]

Perhaps this method could work if the number of unique Names is not too high:

<yoursearch>
| eval mvs = mvzip('DeviceProperties{}.Name','DeviceProperties{}.Value',"=")
| rex field=mvs "^Id=(?<Id>.*)"
| rex field=mvs "^DisplayName=(?<DisplayName>.*)"
| rex field=mvs "^OS=(?<OS>.*)"
| rex field=mvs "^BrowserType=(?<BrowserType>.*)"
| rex field=mvs "^TrustType=(?<TrustType>.*)"
| rex field=mvs "^SessionId=(?<SessionId>.*)"

[livehybrid]

Hi @shaunm001 

How about something like this? Its not perfect but should work. Be careful using REX as can be resource intensive, although iterating over things like this is never perfect. Could be some other ways using mvexpand too but tried to avoid that!

I've added some sample data for you to see how it might work!

| makeresults 
| eval _raw="{
    \"id\": \"12345\",
    \"params\": [
        {
            \"key\": \"name\",
            \"value\": \"Splunker1\"
            },
        {
            \"key\": \"score\",
            \"value\": 100
        },
        {
            \"key\": \"department\",
            \"value\": \"support\"
            }
    ]
}" 
| eval _raw=json_extract(_raw,"")
| eval params=json_array_to_mv(json_extract(_raw,"params"))
| foreach params mode=multivalue [
    |  eval val_<<ITER>>=mvindex(params,<<ITER>>) ]
| foreach val_* 
    [| eval fieldName=json_extract(mvindex(params, <<MATCHSTR>>),"key"), {fieldName}=json_extract(mvindex(params, <<MATCHSTR>>),"value") ]
    | fields - params val_*

Please let me know how you get on and consider accepting this answer or adding karma this answer if it has helped.
Regards

Will