# Create a table visualization with nested fields
**URL:** https://discuss.elastic.co/t/create-a-table-visualization-with-nested-fields/375647
**Category:** Kibana
**Tags:** vega
**Created:** [March 10, 2025, 11:37am UTC](https://discuss.elastic.co/t/create-a-table-visualization-with-nested-fields/375647 "2025-03-10T11:37:56Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Alice\_Ionescu](https://discuss.elastic.co/u/Alice_Ionescu)
#### Post date: [March 10, 2025, 11:37am UTC](https://discuss.elastic.co/t/create-a-table-visualization-with-nested-fields/375647/1 "2025-03-10T11:37:56Z")
Hello,
I have the following index user\_roles
```auto
{
"user_roles" : {
"mappings" : {
"properties" : {
"roles" : {
"type" : "nested",
"properties" : {
"effective_from" : {
"type" : "date"
},
"effective_until" : {
"type" : "date"
},
"role_id" : {
"type" : "keyword"
},
"role_name" : {
"type" : "keyword"
}
}
},
"timestamp" : {
"type" : "long"
},
"user_id" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"username" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
}
}
}
```
which represents the assignment of roles to a user with the field roles being a nested field.
I am trying to create a visualization, I've noticed that the classical visualizations aren't available for the nested fields, only vega
I could not find a vega table visualization to display on one column the user name, and on the other the flattened role name
Could someone help me with an example?
Thanks a lot!
---
### Author:  [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)
#### Post date: [March 10, 2025, 1:59pm UTC](https://discuss.elastic.co/t/create-a-table-visualization-with-nested-fields/375647/2 "2025-03-10T13:59:24Z")
Hi @Alice_Ionescu
currently there's no direct support for Nested Fields in the visualization editor.
There are few options tho, you can read them here: [Nested field support · Issue #1084 · elastic/kibana · GitHub](https://github.com/elastic/kibana/issues/1084#issuecomment-2052107011)
---
### Author:  [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)
#### Post date: [March 10, 2025, 3:35pm UTC](https://discuss.elastic.co/t/create-a-table-visualization-with-nested-fields/375647/3 "2025-03-10T15:35:18Z")
As for the table representation you are looking for, can you clarify what's the expected output of it?
Here's a small example of building a table with Vega using the nested fields you've had above.
Take the following documents:
```auto
POST /user_roles_analytics/_bulk
{"index":{}}
{ "timestamp": 1741601520000, "user_id": "1", "username": "dej611", "roles": [{"effective_from": "2025-03-07T11:12:00", "effective_until": "2025-03-10T11:12:00", "role_id": "01", "role_name": "writer"}, {"effective_from": "2025-03-07T11:12:00", "effective_until": "2025-03-10T11:12:00", "role_id": "02", "role_name": "reader"}]}
{"index":{}}
{ "timestamp": 1741601520000, "user_id": "2", "username": "es_user", "roles": [{"effective_from": "2025-03-07T11:12:00", "effective_until": "2025-03-10T11:12:00", "role_id": "02", "role_name": "reader"}]}
```
The spec here is about searching for those users who have the `effective` fields valid for today (`now/d`): think of showing a table of those users who have access to a system as for today.
```auto
{
"$schema": "https://vega.github.io/schema/vega-lite/v5.json",
"data": {
url: {
// Which index to search
index: user_roles_analytics
// Aggregate data by the time field into time buckets, counting the number of documents in each bucket.
body: {
"query": {
"bool": {
"must": [
{
"nested": {
"path": "roles",
"query": {
"bool": {
"must": [
{ "range": { "roles.effective_from": { "lte": "now/d" } } },
{ "range": { "roles.effective_until": { "gte": "now/d" } } }
]
}
}
}
}
],
"must_not": []
}
}
}
}
format: {property: "hits.hits"}
},
"transform": [
{"calculate": "datum._source.username", "as": "username"},
{"calculate": "datum._source.roles", "as": "roles"},
{"flatten": ["roles"]},
{"window": [{"op": "rank", "as": "rank"}]}
],
"hconcat": [
{
"width": 50,
"title": "Usernames",
"mark": "text",
"encoding": {
"text": {"field": "username", "type": "nominal"},
"y": {"field": "rank", "type": "ordinal", "axis": null},
}
},
{
"width": 50,
"title": "Roles",
"mark": "text",
"encoding": {
"text": {"field": "roles.role_name", "type": "nominal"},
"y": {"field": "rank", "type": "ordinal", "axis": null},
}
}
],
"config": {"view": {"stroke": null}}
}
```
Note these lines in the `spec`:
```auto
{"flatten": ["roles"]},
{"window": [{"op": "rank", "as": "rank"}]}
```
`flatten` will take the `roles` field and create one version of the document with each entry of the `roles` array. See more documentation here: [Flatten | Vega-Lite](https://vega.github.io/vega-lite/docs/flatten.html)
`rank` is a transformation that will assign an index to each document (it can identify duplicates) and this will be used to vertical position each entry in the table. See more documentation here: [Window | Vega-Lite](https://vega.github.io/vega-lite/docs/window.html#ops)
The final visual result is this tiny table:
---
### Author:  [@Alice\_Ionescu](https://discuss.elastic.co/u/Alice_Ionescu)
#### Post date: [March 14, 2025, 7:15am UTC](https://discuss.elastic.co/t/create-a-table-visualization-with-nested-fields/375647/4 "2025-03-14T07:15:49Z")
Thanks! It works, but while trying to use the vega we realized that all the custom features are disabled (like exporting, the filtering part)
so, we flattened the data by adding another section roles\_display
Now we have the following mapping
```auto
{
"mappings": {
"properties": {
"email": {
"type": "text",
"fields": {
"keyword": { "type": "keyword", "ignore_above": 256 }
}
},
"identity_source": {
"type": "text",
"fields": {
"keyword": { "type": "keyword", "ignore_above": 256 }
}
},
"roles": {
"type": "nested",
"properties": {
"assigned_at": { "type": "date" },
"effective_from": { "type": "date" },
"effective_until": { "type": "date" },
"reason": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } },
"role_id": { "type": "keyword" },
"role_name": { "type": "keyword" },
"source": { "type": "keyword" }
}
},
"roles_display": {
"properties": {
"assigned_at": { "type": "date" },
"effective_from": { "type": "date" },
"reason": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } },
"role_id": { "type": "keyword" },
"role_name": { "type": "keyword" },
"source": { "type": "keyword" }
}
},
"timestamp": { "type": "date" },
"user_id": {
"type": "text",
"fields": {
"keyword": { "type": "keyword", "ignore_above": 256 }
}
},
"username": {
"type": "text",
"fields": {
"keyword": { "type": "keyword", "ignore_above": 256 }
}
}
}
}
}
```
Here it is an example of a document
```auto
"_index": "user_roles",
"_id": "user_roles+0+231",
"_score": 0,
"_source": {
"user_id": "1b5170f4-b539-497b-bd46-1fd52bc7ca3d",
"username": "1000005",
"email": "1000005@mail.com",
"identity_source": "Application",
"roles": [
{
"role_id": "77777777-1111-0300-0000-000000000000",
"role_name": "Basic User",
"assigned_at": 0,
"effective_from": 0,
"effective_until": null,
"source": "Internal Users",
"reason": "Unknown"
},
{
"role_id": "fed78074-4d05-4b1d-830d-c9c477638f70",
"role_name": "0001",
"assigned_at": 0,
"effective_from": 0,
"effective_until": null,
"source": "direct",
"reason": "Unknown"
},
{
"role_id": "becf5b74-c1b5-4daf-a09f-1c75cfae6979",
"role_name": "A001",
"assigned_at": 0,
"effective_from": 0,
"effective_until": null,
"source": "Role: 0001",
"reason": "Unknown"
},
{
"role_id": "084d5f92-d9ac-4eb8-be32-6a5274fd8645",
"role_name": "A002",
"assigned_at": 0,
"effective_from": 0,
"effective_until": null,
"source": "Role: 0001",
"reason": "Unknown"
}
],
"roles_display": [
{
"role_id": "77777777-1111-0300-0000-000000000000",
"role_name": "Basic User",
"assigned_at": 0,
"effective_from": 0,
"effective_until": null,
"source": "Internal Users",
"reason": "Unknown"
},
{
"role_id": "fed78074-4d05-4b1d-830d-c9c477638f70",
"role_name": "0001",
"assigned_at": 0,
"effective_from": 0,
"effective_until": null,
"source": "direct",
"reason": "Unknown"
},
{
"role_id": "becf5b74-c1b5-4daf-a09f-1c75cfae6979",
"role_name": "A001",
"assigned_at": 0,
"effective_from": 0,
"effective_until": null,
"source": "Role: 0001",
"reason": "Unknown"
},
{
"role_id": "084d5f92-d9ac-4eb8-be32-6a5274fd8645",
"role_name": "A002",
"assigned_at": 0,
"effective_from": 0,
"effective_until": null,
"source": "Role: 0001",
"reason": "Unknown"
}
],
"timestamp": 1741784080103
}
}
```
I created a visualization of type enhanced table with the following configuration
```auto
Aggregation - Terms - field username.keyword
Aggregation - Terms - field roles_display.role_name
Aggregation - Terms - field roles_display.reason.keyword
Aggregation - Terms - field roles_display.source
```
When I create the table with
```auto
Aggregation - Terms - field username.keyword
Aggregation - Terms - field roles_display.role_name
```
all is OK, but when I try to add more fields like the reason or the source, all is mixed up, with all the possible variants from the nested fields
| User | Role name | Source |
| --- | --- | --- |
| 1000005 | 0001 | Internal Users |
| 1000005 | 0001 | Role: 0001 |
| 1000005 | 0001 | direct |
even thought for user 1000005 and role 0001 there is only 1 source direct
How could I avoid this?
To create a filter on the visualization ? For the aggregation I have only terms, filters , significant terms and others related to histogram and dates
Thanks!