How to extract and rename field from JSON

[itnewbie]

I have an index, where each event is a JSON object, the structure is as follows:

    {
        "otherFields": "otherValue",
        "fields": [
            {
                "id": 123,
                "value": "ABC"
            },
            {
                "id": 456,
                "value": "DEF"
            }
        ]
    }

Now, I want to extract the "id" value and rename it with meaningful names, such that the renamed field can be seen at the "interesting field" section in the search app and I can use it in timechart command to filter for the value. 

I have tried something like:

| mvexpand fields{}.id
| search feilds{}.id=123
| rename fields{}.id AS "Product Name"

 And in the interesting field section, it gave me something like:

Current ouput

But I want it to look like the id with the corresponding value:

My desired output

 Not only I want to rename one but multiple ids, how may I do so?

[gcusello]

Hi @itnewbie,

you can use the spath command (https://docs.splunk.com/Documentation/Splunk/9.1.0/SearchReference/Spath9 to extract all the fields from your json source and then rename them as you like.

Ciao.

Giuseppe

[bowesmana]

Not sure I understood your 'look like the id' and the SeverityLevel image - do you mean you want ABC somehow in the mix associated with the '123' field?

This will assign the K=V pairs from the data if that's what you're after.

| foreach 0 1 2 3 [ eval f=mvindex('fields{}.id', <<FIELD>>), {f}=mvindex('fields{}.value', <<FIELD>>) | fields - f]

[ITWhisperer · Accepted solution]

Building on what @bowesmana said, you could include the rename in the foreach command

| foreach 0 1 2 3 [ eval f=mvindex('fields{}.id', <<FIELD>>), f=case(f="123","Product Name",1==1,f), {f}=mvindex('fields{}.value', <<FIELD>>) | fields - f]

[itnewbie]

I have another question, right now, I have an "organization_id" field in this index, it can be referenced as the "id" field to another index B, index B also has a "name" field representing the organization name. If I want to filter my result of index A by the organization name as well, how may I do so? 

The event of  index B like this:

{
"otherField: "",
"id" 123,
"name": "ABC Company"
}

And splunk already extracts the "id" and the "name" field in the interesting field section of index B. 

I am thinking about using join:

index=A (current index)
| rename organization_id as id
| dedup id
| join id
[ | search index=B
  ...how to retrieve the list of organization names?...
]

My current base search is like this:

index="A" 
| foreach 0 1 
    [ eval f=mvindex('fields{}.id', <<FIELD>>), f=case(f="123","Product Brand", f="456", "Severity Level", 1==1,f), {f}=mvindex('fields{}.value', <<FIELD>>) 
    | fields - f] 
| ...additional organization name extratcion logic here...
| search Organization IN (*) <- a dynamic filter in the dashboard
| timechart count by "Severity Level" usenull=f

[ITWhisperer]

I am confused by this - does index A have the organisation id already as a field or is it one of the fields you need to rename from the JSON using the foreach technique?

Does the search you are trying to join with (index B) have the same sort of JSON structure requiring a similar renaming of fields?

[itnewbie]

Thanks for the reply. Let me try to explain. I want it like: I extract the id "123" and rename it as "Product Name", when I click the "Product Name" in the interesting field, it should "map" and give it the "value" (ABC) of all the "Product Name" value in the index