Create a table visualization with nested fields

Elastic Stack Kibana
1

Hello,

I have the following index user_roles

{
  "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!

2

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

3

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:

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.

{
  "$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:

{"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

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

The final visual result is this tiny table:

Screenshot 2025-03-10 at 16.29.35
Screenshot 2025-03-10 at 16.29.35330×192 5.01 KB

4

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

{
  "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

 "_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

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

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!