# Getting nested data into proper form for vega
**URL:** https://discuss.elastic.co/t/getting-nested-data-into-proper-form-for-vega/182948
**Category:** Kibana
**Tags:** vega
**Created:** [May 27, 2019, 4:46pm UTC](https://discuss.elastic.co/t/getting-nested-data-into-proper-form-for-vega/182948 "2019-05-27T16:46:01Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@ethrbunny](https://discuss.elastic.co/u/ethrbunny)
#### Post date: [May 27, 2019, 4:46pm UTC](https://discuss.elastic.co/t/getting-nested-data-into-proper-form-for-vega/182948/1 "2019-05-27T16:46:01Z")
Am hoping to make charts using vega but am struggling to get data formatted properly. Data in question is usage patterns for postgresql. Specific example:
```
"postgresql" : {
"database" : {
"rows" : {
"inserted" : 83977835
}
}
},
"beat" : {
"name" : "some_host"
}
```
Found this time format snip:
```
transform: [ {
calculate: "toDate(datum._source['@timestamp'])"
as: "time"
} ]
```
In my attempt at a vega vis I have this for the "url" param:
```
index: postgresql*
body: {
size: 10000
_source: ["@timestamp", "beat.name", "postgresql.database.rows.inserted"]
}
```
'x' would appear to be
```
x: {
field: time
type: temporal
axis: {title: false}
}
```
It's the 'encoding' section where things go awry. What values should I use for 'y'? I've tried various combinations of \_source. to no avail.
---
### Author:  [@mimitsu](https://discuss.elastic.co/u/mimitsu)
#### Post date: [May 29, 2019, 7:59pm UTC](https://discuss.elastic.co/t/getting-nested-data-into-proper-form-for-vega/182948/2 "2019-05-29T19:59:07Z")
An example of how you can access the data from ES is shown in your "transform".
The `calculate` transform gives the value `toDate(datum._source['@timestamp'])` to a field (or column header) called `time`.
Let's break this down. `toDate( )` is just a function that creates a date object initialized by whatever value `datum._source['@timestamp']` may be.
This `datum` is a special Vega term. It is the data that you defined in `data`. In your case, it's the JSON object that Elasticsearch returns from your `url` parameter.
If you actually run the content of the `url` parameter, in for example DevTools, you'll get to see what ES returns.
```auto
GET postgresql*/_search
{
"size": 10000,
"_source": ["@timestamp", "beat.name", "postgresql.database.rows.inserted"]
}
```
The response should look like this:
```auto
{
"took" : ...,
"timed_out" : false,
"_shards" : { ... },
"hits" : {
"total" : ...,
"max_score" : 1.0,
"hits" : [
{
"_index": "postgresql",
"_type": "_doc",
"_id": "....",
"_score": 1.0,
"_source": {
"@timestamp": "2019-05-29T04:12:53.318Z",
"postgresql" : {
"database" : {
"rows" : {
"inserted" : 83977835
}
}
},
"beat" : {
"name" : "some_host"
}
}
},
{
"_index": "postgresql",
"_type": "_doc",
"_id": "....",
"_score": 1.0,
"_source": {
"@timestamp": "2019-05-29T05:20:12.148Z",
"postgresql" : {
"database" : {
"rows" : {
"inserted" : 12
}
}
},
"beat" : {
"name" : "some_other_host"
}
}
},
...
]
}
}
```
In your `data` definition, you probably also have `format: {"property: hits.hits"}` somewhere in there. That `datum` variable is now pointing to an array, exactly the array of your `hits.hits`:
```auto
[
{
"_index": "postgresql",
"_type": "_doc",
"_id": "....",
"_score": 1.0,
"_source": {
"@timestamp": "2019-05-29T04:12:53.318Z",
"postgresql" : {
"database" : {
"rows" : {
"inserted" : 83977835
}
}
},
"beat" : {
"name" : "some_host"
}
}
},
{
"_index": "postgresql",
"_type": "_doc",
"_id": "....",
"_score": 1.0,
"_source": {
"@timestamp": "2019-05-29T05:20:12.148Z",
"postgresql" : {
"database" : {
"rows" : {
"inserted" : 12
}
}
},
"beat" : {
"name" : "some_other_host"
}
}
},
...
]
```
By saying `datum._source` you narrow that down to:
```auto
[
{
"@timestamp": "2019-05-29T04:12:53.318Z",
"postgresql" : {
"database" : {
"rows" : {
"inserted" : 83977835
}
}
},
"beat" : {
"name" : "some_host"
}
},
{
"@timestamp": "2019-05-29T05:20:12.148Z",
"postgresql" : {
"database" : {
"rows" : {
"inserted" : 12
}
}
},
"beat" : {
"name" : "some_other_host"
}
},
...
]
```
and further, `datum._source['@timestamp']` gives us this array of a bunch of date values:
```auto
[
"2019-05-29T04:12:53.318Z",
"2019-05-29T05:20:12.148Z",
...
]
```
At this point, imagine that Vega has created a table. The table has one column with a column header called `time` (which is what you called it using `calculate`). The rows have the values: `toDate("2019-05-29T04:12:53.318Z")` then `toDate("2019-05-29T05:20:12.148Z")` etc.
You refer to this column in your `encoding` by saying that you want your x-axis to use the values from that column `time`.
You can create another column by adding more to your transform.
```auto
transform: [
{
calculate: "toDate(datum._source['@timestamp'])"
as: "time"
},
{
calculate: "datum._source.postgresql.database.rows.inserted"
as: "no_of_rows"
}
]
```
This gives you two columns: `time` and `no_of_rows`. By the way, the syntax `_source['...']` was used instead of the usual dot notation only because the compiler wasn't happy to see the `@` character in the dot notation. The work around is to use `[' ... ']`.
So now you can write your encoding using two sets of values.
```auto
encoding: {
x: {
field: time
type: temporal
axis: {title: "Date"}
}
y: {
field: no_of_rows
type: quantitative
axis: {title: "Number of rows inserted"}
}
}
```
---
### Author:  [@ethrbunny](https://discuss.elastic.co/u/ethrbunny)
#### Post date: [May 31, 2019, 8:37pm UTC](https://discuss.elastic.co/t/getting-nested-data-into-proper-form-for-vega/182948/3 "2019-05-31T20:37:38Z")
This is an amazing writeup. Thank you so much! I will spend time digesting this and see how far I get.
---
### Author:  [@ethrbunny](https://discuss.elastic.co/u/ethrbunny)
#### Post date: [June 1, 2019, 5:08pm UTC](https://discuss.elastic.co/t/getting-nested-data-into-proper-form-for-vega/182948/4 "2019-06-01T17:08:59Z")
I believe I have this setup in Vega properly but I'm getting this error:
```
Cannot read property 'database' of undefined
```
When I run this query:
```
GET postgresql*/_search
{
"size": 10,
"_source": ["@timestamp", "beat.name","postgresql.database.rows.inserted"]
}
```
I get data like this:
```
"hits" : {
"total" : 13763253,
"max_score" : 1.0,
"hits" : [
{
"_index" : "postgresql-2019.05.19",
"_type" : "doc",
"_id" : "uldjzWoB9FCFqJTdlzd1",
"_score" : 1.0,
"_source" : {
"@timestamp" : "2019-05-19T00:00:16.176Z",
"beat" : {
"name" : "postgresql"
}
}
},
{
"_index" : "postgresql-2019.05.19",
"_type" : "doc",
"_id" : "vldjzWoB9FCFqJTdlzd1",
"_score" : 1.0,
"_source" : {
"@timestamp" : "2019-05-19T00:00:16.176Z",
"beat" : {
"name" : "postgresql"
}
}
},
{
"_index" : "postgresql-2019.05.19",
"_type" : "doc",
"_id" : "qmBjzWoBlRof6N68l855",
"_score" : 1.0,
"_source" : {
"@timestamp" : "2019-05-19T00:00:16.174Z",
"postgresql" : {
"database" : {
"rows" : {
"inserted" : 0
}
}
},
"beat" : {
"name" : "postgresql"
}
}
},
```
Is it a problem that there appear to be several entries for "beat.name" for one "postgresql..." entry?
---
### Author:  [@mimitsu](https://discuss.elastic.co/u/mimitsu)
#### Post date: [June 4, 2019, 4:21am UTC](https://discuss.elastic.co/t/getting-nested-data-into-proper-form-for-vega/182948/5 "2019-06-04T04:21:59Z")
The error says `Cannot read property 'database'` which means it could not find a field called `database`. The `database` from `postgresql.database`.
The first document that returns from your `GET` command is a document that happens to not have a value for `postgresql.database.rows.inserted`. So that's probably the issue.
One thing you can do is to clean up the data you're sending to Vega by searching for only those documents which have values in that field.
Try this in you url body:
```auto
body: {
size: 10000
_source: ["@timestamp", "beat.name", "postgresql.database.rows.inserted"]
query: {
exists: {
field: postgresql.database.rows.inserted
}
}
}
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 2, 2019, 4:22am UTC](https://discuss.elastic.co/t/getting-nested-data-into-proper-form-for-vega/182948/6 "2019-07-02T04:22:01Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.