Table visualization on arrays

Elastic Stack Kibana
1

Hello,

We have some documents with nested fields (roles) for which we did a flattening by created an array (roles_display)

Here is it the mapping of the index

{
  "mappings": {
    "properties": {
      "compliance_flags": {
        "properties": {
          "access_review_pending": { "type": "boolean" },
          "gdpr": { "type": "boolean" }
        }
      },
      "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"
            }
          ],
          "compliance_flags": {
            "gdpr": true,
            "access_review_pending": false
          },
          "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!