# Vega: Access nested fields of a JSON file
**URL:** https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277
**Category:** Kibana
**Tags:** vega
**Created:** [December 10, 2020, 1:11pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277 "2020-12-10T13:11:27Z")
**Posts on this page:** 11
**Page:** 1
### Author:  [@ege](https://discuss.elastic.co/u/ege)
#### Post date: [December 10, 2020, 1:11pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277/1 "2020-12-10T13:11:27Z")
Consider the below response for an Elasticsearch query.
```
"aggregations" : {
"terms" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "1",
"doc_count" : 3,
"top_status_hits" : {
"hits" : {
"total" : {
"value" : 3,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "demo-index",
"_type" : "_doc",
"_id" : "2L2JR3YBsgdxC8Q2whgP",
"_score" : null,
"_source" : {
"Status" : "end",
"ID" : "1"
},
"sort" : [
"end"
]
}
]
}
}
}
]
}
}
```
How can I access the **Status** and **ID** fields in a Vega code? I tried doing it like below without any success.
```
"format": {"property": "aggregations.terms.buckets"},
"transform": [
{
"type": "flatten",
"fields": ["top_status_hits.hits.hits"],
"as": ["data"]
},
{"type": "pie", "field": "data._source.Status"}
]
```
Any suggestions?
---
### Author:  [@aaron-nimocks](https://discuss.elastic.co/u/aaron-nimocks)
#### Post date: [December 10, 2020, 1:21pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277/2 "2020-12-10T13:21:23Z")
Untested. Try
`"format": {"property": "aggregations.terms.buckets. top_status_hits.hit.hits"},`
Then use the below to access them
`_source.Status`
`_source.ID`
---
### Author:  [@ege](https://discuss.elastic.co/u/ege)
#### Post date: [December 10, 2020, 1:58pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277/3 "2020-12-10T13:58:41Z")
Hi @aaron-nimocks, thanks for the reply.
I tried what you said, but, it gives the following error.
---
### Author:  [@brunet](https://discuss.elastic.co/u/brunet)
#### Post date: [December 10, 2020, 2:11pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277/4 "2020-12-10T14:11:43Z")
hits.hits perhaps
---
### Author:  [@ege](https://discuss.elastic.co/u/ege)
#### Post date: [December 10, 2020, 2:32pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277/5 "2020-12-10T14:32:11Z")
Nope, not working. ☹
---
### Author:  [@aaron-nimocks](https://discuss.elastic.co/u/aaron-nimocks)
#### Post date: [December 10, 2020, 3:12pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277/6 "2020-12-10T15:12:47Z")
This one is special because you have an array within an array. Not sure I've done that before.
Using the [Vega Editor](https://vega.github.io/editor/#/edited) I was getting close but couldn't get inside that 2nd array yet. If I have time I will come back to it later today.
Possible to change your query to only return a single array or to narrow down the results you need?
```auto
{
"$schema": "https://vega.github.io/schema/vega/v5.json",
"data": [
{
"name": "table",
"values": {
"aggregations": {
"terms": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "1",
"doc_count": 3,
"top_status_hits": {
"hits": {
"total": {"value": 3, "relation": "eq"},
"max_score": null,
"hits": [
{
"_index": "demo-index",
"_type": "_doc",
"_id": "2L2JR3YBsgdxC8Q2whgP",
"_score": null,
"_source": {"Status": "end", "ID": "1"},
"sort": ["end"]
}
]
}
}
}
]
}
}
},
"format": {"property": "aggregations.terms.buckets"},
"transform": [
{
"type": "formula",
"as": "data",
"expr": "datum.top_status_hits.hits.hits"
}
]
}
],
"marks": [
{
"type": "text",
"from": {"data": "table"},
"encode": {
"enter": {
"fill": {"value": "#000"},
"text": {"field": "doc_count"},
"x": {"value": 10},
"y": {"value": 10}
}
}
}
]
}
```
---
### Author:  [@ege](https://discuss.elastic.co/u/ege)
#### Post date: [December 10, 2020, 3:58pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277/7 "2020-12-10T15:58:35Z")
Sure @aaron-nimocks. It'll be really helpful if you could find a solution. Meanwhile, I'll show what I wanted to achieve.
What I did to get this result was by using a `top_hits` metric aggregator on the index. Here's the data I used.
1,start
1,pending
1,end
2,start
2,end
3,start
3,pending
3,end
4,start
4,pending
5,start
5,pending
5,end
6,start
7,start
8,start
And here's the Elasticsearch API request I used for achieving the response I mentioned earlier.
```
GET /demo*/_search
{
"aggs": {
"terms": {
"terms": {
"field": "ID.keyword",
"size": 10
},
"aggs": {
"top_status_hits": {
"top_hits": {
"sort": [
{
"Status.keyword": {
"order": "asc"
}
}
],
"_source": {
"includes": [
"ID",
"Status"
]
},
"size": 1
}
}
}
}
}
}
```
I wanted to get the _last status_ for each **ID** from the index.
---
### Author:  [@aaron-nimocks](https://discuss.elastic.co/u/aaron-nimocks)
#### Post date: [December 10, 2020, 4:03pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277/8 "2020-12-10T16:03:48Z")
Try
`"format": {"property": "aggregations.terms.buckets[0].top_status_hits.hits.hits"}`
and for displaying in marks use
`"text": {"field": "_source.Status"},`
---
### Author:  [@ege](https://discuss.elastic.co/u/ege)
#### Post date: [December 10, 2020, 4:16pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277/9 "2020-12-10T16:16:47Z")
**Woah, it's working!!!** 🎉 🎉 🎉
Thank you so, so much @aaron-nimocks!!! I was so lost for whole 2 days with this. Finally it's solved! What a relief! 😍
Thanks once again for showing the path! 😁
---
### Author:  [@aaron-nimocks](https://discuss.elastic.co/u/aaron-nimocks)
#### Post date: [December 10, 2020, 4:19pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277/10 "2020-12-10T16:19:49Z")
Awesome!
At first I was trying to dynamically parse the buckets array. After thinking about it there should only always be just 1 with that query.
Adding the `[0]` in `aggregations.terms.buckets[0].top_status_hits.hits.hits` just means use the first result and only first result. So really you just walk down the JSON path in order to get to the data you want and end at an array which is the 2nd hits.
Just adding more detail in case someone else runs across this and needs it.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [January 7, 2021, 4:20pm UTC](https://discuss.elastic.co/t/vega-access-nested-fields-of-a-json-file/258277/11 "2021-01-07T16:20:00Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.