# How to aggregate & visualize data from specific fields in index elements? (mobile game)

**URL:** <https://discuss.elastic.co/t/how-to-aggregate-visualize-data-from-specific-fields-in-index-elements-mobile-game/205016>\
**Category:** Kibana\
**Created:** [October 24, 2019, 8:16am UTC](https://discuss.elastic.co/t/how-to-aggregate-visualize-data-from-specific-fields-in-index-elements-mobile-game/205016 "2019-10-24T08:16:15Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Anne\_Kim](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anne_kim/32/47196_2.png) [@Anne\_Kim](https://discuss.elastic.co/u/Anne_Kim)\
**Post date:** [October 24, 2019, 8:16am UTC](https://discuss.elastic.co/t/how-to-aggregate-visualize-data-from-specific-fields-in-index-elements-mobile-game/205016/1 "2019-10-24T08:16:15Z")

</div>

Hi there,

There is an index, each element of which contains:

1. general information about a player's session in tutorial
2. a few arrays with information for each tutorial stage

It looks like this:  
`> id:206 duration:35 created_at:Oct 23, 2019 @ 16:55:11.000 build:0.2.3.889 player_id:450 is_tester:true stages:{ "id": 1475, "stage_id": 0, "duration": 11, "completed": true }, { "id": 1476, "stage_id": 1, "duration": 6, "completed": true }, { "id": 1477, "stage_id": 2, "duration": 2, "completed": true }, { "id": 1478, "stage_id": 3, "duration": 2, "completed": true }, { "id": 1479, "stage_id": 4, "duration": 3, "completed": true }, { "id": 1480, "stage_id": 5, "duration": 3, "completed": true }, { "id": 1481, "stage_id": 6, "duration": 4, "completed": true } _id:Hdrq-G0BSOdvtuXl5PqJ _type:data _index:tutorial_data _score: -`

> **(Or same in JSON)**
>
> ```
> {
> "_index": "tutorial_data",
> "_type": "data",
> "_id": "Sdoi-W0BSOdvtuXlEfvi",
> "_version": 1,
> "_score": null,
> "_source": {
> "id": 207,
> "duration": 28,
> "created_at": "2019-10-23T14:26:28.000+00:00",
> "build": "0.2.3.889",
> "player_id": 450,
> "is_tester": true,
> "stages": [
> {
> "id": 1482,
> "stage_id": 0,
> "duration": 4,
> "completed": true
> },
> {
> "id": 1483,
> "stage_id": 1,
> "duration": 5,
> "completed": true
> },
> {
> "id": 1484,
> "stage_id": 2,
> "duration": 2,
> "completed": true
> },
> {
> "id": 1485,
> "stage_id": 3,
> "duration": 7,
> "completed": true
> },
> {
> "id": 1486,
> "stage_id": 4,
> "duration": 3,
> "completed": true
> },
> {
> "id": 1487,
> "stage_id": 5,
> "duration": 6,
> "completed": true
> }
> ]
> },
> "fields": {
> "created_at": [
> "2019-10-23T14:26:28.000Z"
> ]
> },
> "sort": [
> 1571840788000
> ]
> }
> 
> ```

The task is to create a table/ a diagram of an average time spent on each stage of the tutorial.  
When I aggregate through the "_stages.duration_" field (not just the "_duration_" !) and split the table by "_stages.stage\_id_", it calculates an average time basing on ALL stages in sessions that include a record about the given _stages.stages\_id_ (e.g., a stage №2).

> **This is my request**
>
> ```
> {
> "aggs": {
> "3": {
> "terms": {
> "field": "stages.stage_id",
> "order": {
> "_key": "desc"
> },
> "size": 9
> },
> "aggs": {
> "1": {
> "avg": {
> "field": "stages.duration"
> }
> }
> }
> }
> },
> "size": 0,
> "_source": {
> "excludes": []
> },
> "stored_fields": [
> "*"
> ],
> "script_fields": {},
> "docvalue_fields": [
> {
> "field": "created_at",
> "format": "date_time"
> }
> ],
> "query": {
> "bool": {
> "must": [
> {
> "range": {
> "created_at": {
> "format": "strict_date_optional_time",
> "gte": "2019-10-11T20:30:00.000Z",
> "lte": "2019-10-24T08:10:54.837Z"
> }
> }
> }
> ],
> "filter": [
> {
> "match_all": {}
> }
> ],
> "should": [],
> "must_not": []
> }
> }
> }
> 
> ```

> **Response**
>
> ```
> {
> "took": 4,
> "timed_out": false,
> "_shards": {
> "total": 1,
> "successful": 1,
> "skipped": 0,
> "failed": 0
> },
> "hits": {
> "total": 203,
> "max_score": null,
> "hits": []
> },
> "aggregations": {
> "3": {
> "doc_count_error_upper_bound": 0,
> "sum_other_doc_count": 0,
> "buckets": [
> {
> "1": {
> "value": 8.141304347826088
> },
> "key": 8,
> "doc_count": 147
> },
> {
> "1": {
> "value": 8.164634146341463
> },
> "key": 7,
> "doc_count": 150
> },
> {
> "1": {
> "value": 8.108289768483944
> },
> "key": 6,
> "doc_count": 154
> },
> {
> "1": {
> "value": 8.036390101892286
> },
> "key": 5,
> "doc_count": 160
> },
> {
> "1": {
> "value": 8.02030456852792
> },
> "key": 4,
> "doc_count": 161
> },
> {
> "1": {
> "value": 7.935256861365236
> },
> "key": 3,
> "doc_count": 172
> },
> {
> "1": {
> "value": 8.189151599443672
> },
> "key": 2,
> "doc_count": 179
> },
> {
> "1": {
> "value": 8.189151599443672
> },
> "key": 1,
> "doc_count": 179
> },
> {
> "1": {
> "value": 9.656871218668972
> },
> "key": 0,
> "doc_count": 160
> }
> ]
> }
> },
> "status": 200
> }
> 
> ```

So how can I "pick out" and aggregate through the data, e.g. just for the 2nd stage in all appropriate index elements?

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [October 24, 2019, 12:50pm UTC](https://discuss.elastic.co/t/how-to-aggregate-visualize-data-from-specific-fields-in-index-elements-mobile-game/205016/2 "2019-10-24T12:50:18Z")

</div>

Hi @Anne_Kim,

unfortunately this is currently not possible in Kibana because nested fields are not supported: [https://github.com/elastic/kibana/issues/1084](https://github.com/elastic/kibana/issues/1084)

For Kibana, `stages.duration` and `stages.stage_id` look like two separate arrays that don't have any relationship, so it can't associate one value from one array with a single value in the other array.

For your use case it makes sense to denormalize and ingest one document per individual stage - then you can create the diagram exactly like you described it.

---

<div class="post-metadata">

**Author:** ![Anne\_Kim](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anne_kim/32/47196_2.png) [@Anne\_Kim](https://discuss.elastic.co/u/Anne_Kim)\
**Post date:** [October 24, 2019, 1:46pm UTC](https://discuss.elastic.co/t/how-to-aggregate-visualize-data-from-specific-fields-in-index-elements-mobile-game/205016/3 "2019-10-24T13:46:13Z")

</div>

Ok, thank you!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 21, 2019, 1:46pm UTC](https://discuss.elastic.co/t/how-to-aggregate-visualize-data-from-specific-fields-in-index-elements-mobile-game/205016/4 "2019-11-21T13:46:16Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.