How to accumulate a numerical field of a multivalue object based on other field?

[gorkazabarte]

I have many event with the following format: 

EVENT 1

{

    'colors': [ 

        {'color': 'red', 'appearances': 3},

        {'color': 'blue', 'appearances': 2},

        ...

        {'color': 'yellow', 'appearances': 4}

}

EVENT 2

{

    'colors': [ 

        {'color': 'green', 'appearances': 1},

        {'color': 'blue', 'appearances': 4},

        ...

        {'color': 'yellow', 'appearances': 2}

}

I want to accumulate the field appearances after verifying each event grouped by the field color to have the following output:

| ---------------------------------------------------------------------------------- |

|                              Color                         |                     Appearances               |

| ---------------------------------------------------------------------------------- |

|                          blue                               |                               6                              |

|                          red                                 |                               3                              |

|                          yellow                           |                               6                              |

|                          green                             |                               1                              |

|  ---------------------------------------------------------------------------------- |

Does anyone know how to obtain this result, I have been playing with mv functions, but I am not able to produce the expected output. 

[yuanliu]

@ITWhisperer's answer assumes that the JSON is contained in a field named event.  If the events are raw, you would already have flattened fields named colors{}.*.  Performing calculation on array elements using flattened field can obscure the underlying mathematics.  So, here I give an alternative, more semantic approach. (I'll take the assumption that the events are raw; if not, just add one more spath at top as ITWhisperer demonstrates.)

| spath path=colors{}
| mvexpand colors{}
| spath input=colors{}
| stats sum(appearances) as appearances by color

Your sample data give

color	appearances
blue	6
green	1
red	3
yellow	6

Here is data emulation that you can play with and compare with real data.

| makeresults
| eval data = split("{
    \"colors\": [ 
        {\"color\": \"red\", \"appearances\": 3},
        {\"color\": \"blue\", \"appearances\": 2},
        {\"color\": \"yellow\", \"appearances\": 4}
        ]
}|{
    \"colors\": [ 
        {\"color\": \"green\", \"appearances\": 1},
        {\"color\": \"blue\", \"appearances\": 4},
        {\"color\": \"yellow\", \"appearances\": 2}
        ]
}", "|")
| mvexpand data
| rename data AS _raw
``` data emulation above ```

One more note: When you share anonymized data, please make sure to preserve the original data format.  The ones in your OP used incorrect quotation marks.  They also missed closing brackets for arrays.  This makes the volunteers' work more difficult.

If your data are truly conformant, they should look like these:

{
    "colors": [ 
        {"color": "red", "appearances": 3},
        {"color": "blue", "appearances": 2},
        {"color": "yellow", "appearances": 4}
        ]
}
{
    "colors": [ 
        {"color": "green", "appearances": 1},
        {"color": "blue", "appearances": 4},
        {"color": "yellow", "appearances": 2}
        ]
}

[ITWhisperer]

| spath input=event
| rename colors{}.* as *
| eval count=mvcount(color)
| eval row=mvrange(0,count)
| mvexpand row
| eval color=mvindex(color,row)
| eval appearances=mvindex(appearances,row)
| stats sum(appearances) as appearances by color