# How to unnest - flatten this answer

**URL:** <https://discuss.elastic.co/t/how-to-unnest-flatten-this-answer/261419>\
**Category:** Kibana\
**Tags:** vega\
**Created:** [January 18, 2021, 11:58am UTC](https://discuss.elastic.co/t/how-to-unnest-flatten-this-answer/261419 "2021-01-18T11:58:33Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![brunet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brunet/32/80525_2.png) [@brunet](https://discuss.elastic.co/u/brunet)\
**Post date:** [January 18, 2021, 11:58am UTC](https://discuss.elastic.co/t/how-to-unnest-flatten-this-answer/261419/1 "2021-01-18T11:58:33Z")

</div>

Dear community,

I need to transform the ES response to flatten the received data, i would like to make a radar type viz but i'm not able to unnest / flatten it.

I have this answer (cleaned it a bit)...

```
"aggregations": {
  "subject": {
    "buckets": [
      {
        "key": "Hematology",
        "person": {
          "buckets": [
            {
              "key": "John Doe",
              "hours": {
                "value": 287.5
              }
            },
            {
              "key": "Peter Duck",
              "hours": {
                "value": 203
              }
            }
          ]
        }
      },
      {
        "key": "Immunology",
        "category": {
          "buckets": [
            {
              "key": "John Doe",
              "hours": {
                "value": 110.75
              }
            },
            {
              "key": "Peter Duck",
              "hours": {
                "value": 122.75
              }
            }
          ]
        }
      }
    ]
  }

```

What i need, vega needs is something flattened like this:

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/5/d/5d69163271cc88a0cb98501b5ccc66fccb5e1334.png)

So I can finally achieve something like this:

![image](https://us1.discourse-cdn.com/elastic/original/3X/d/3/d3488785a79c4fba6b4113e9d59e026fc1c9fc43.png)

What i had so far is to set "propierties" : "aggregations.subject.buckets", this is ok.  
But then, using project/flatten transformation, i cannot access [hours.value] since i get undefined values. Or again, have a nested json in the new field.

Anyway,... anyone knows how is this done correctly.

So many thanks.

---

<div class="post-metadata">

**Author:** ![brunet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brunet/32/80525_2.png) [@brunet](https://discuss.elastic.co/u/brunet)\
**Post date:** [January 18, 2021, 12:22pm UTC](https://discuss.elastic.co/t/how-to-unnest-flatten-this-answer/261419/2 "2021-01-18T12:22:29Z")

</div>

This is the data section:  
The issue is this notation -\> "category.buckets[0].key", this is working but then i can access only 1 category. If I write it like this -\> "category.buckets.key", aiming to get all the array then it Shows "undefined".

```
"data": [
{
	"name": "table"
	"url": {
		%context%: true
		%timefield%: @timestamp
		"index": whatever*
		"body": {
			"aggs": {
				"subject": {
					"terms": {"field": "subject.keyword","size": 20},
					"aggs": {
						"category" : {
							"terms": {
								"field": "name.keyword", "size": 10
							},
							"aggs": {
								"hours": {
									"sum": {
										"field": "hours_working"
									}
								}
							}
						}
					}
				}
			},
			"size": 0
		}
	},"format": {"property": "aggregations.subject.buckets"},
	"transform":[
		{
			"type": "project",
			"fields": ["key","category.buckets[0].key", "category.buckets[0].hours.value"],
			"as": ["key", "category", "value"]
		}
	]
},
{
	"name": "keys",
	"source": "table",
	"transform": [{
		"type": "aggregate",
		"groupby": ["key"]
	}]
}
```

---

<div class="post-metadata">

**Author:** ![brunet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brunet/32/80525_2.png) [@brunet](https://discuss.elastic.co/u/brunet)\
**Post date:** [January 18, 2021, 3:48pm UTC](https://discuss.elastic.co/t/how-to-unnest-flatten-this-answer/261419/3 "2021-01-18T15:48:09Z")

</div>

Ok, so this did the trick.

```
"transform": [
  {
    "type": "flatten",
    "fields": ["subject.buckets"],
    "as": ["key1"]
  },
  {
    "type": "project",
    "fields": ["key1.key", "key1.hours.value", "key"],
    "as": ["key", "value", "category"]
  }
]
```

---

<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:** [February 15, 2021, 3:48pm UTC](https://discuss.elastic.co/t/how-to-unnest-flatten-this-answer/261419/4 "2021-02-15T15:48:11Z")

</div>

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