Kibana Table Visualization of documents with nested array of JSON

[fefontana (Franco Fontana)]

Hi, I have the following json documents stored in Elasticsearch. It is a json with a nested array with json items:

{
"took": 40,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 1,
"relation": "eq"
},
"max_score": 1.9924302,
"hits": [
{
"_index": "ff_sms",
"_type": "_doc",
"_id": "60df32454534910028e35459",
"_score": 1.9924302,
"_source": {
"type": "prueba",
"sentBySistarBank": false,
"deliveryReport": false,
"canceled": false,
"irisIxId": "60df32454534910028e35454",
"client": "test_iris",
"app": "postman",
"cellNumber": 59892300727,
"text": "prioridad 1",
"creationDate": "2021-07-02T15:35:33.458Z",
"priority": 1,
"log": [
{
"error": false,
"date": "2021-07-02T15:35:33.458Z",
"step": "recibidoAPI"
},
{
"error": false,
"date": "2021-07-02T15:35:33.848Z",
"step": "agregadoKafka"
}
],
"__v": 1
}
}
]
}
}

I'm trying to build a Kibana "Aggregation based" visualization (not Lens) using a table and "Terms" aggregation for each "Bucket" in Kibana Data menu.
I don't understand why the table is repeating rows when using items of the inner array. The attached image is showing the issue:

[Marco_Liberati]

Hi @fefontana ,

I see in your picture that you are using 4 distinct aggregations as Split by row there.
These splits will first divide each row of the table by irisIxId.keyword, then by log.date, and so on so forth: assuming you've set size 100 for all the other split aggregations, you can have "potentially" 100 x 100 x 100 rows for each irisIxId.keyword.
In your example the first 2 columns are the same for each irisIxId.keyword, but there are 2 documents with different log.step.keyword value.

[fefontana (Franco Fontana)]

Ok, I understand your point, but is there any way to include all the listed columns in the table without having repeated rows ?.

[Marco_Liberati]

But rows are not really repeated, you are visualizing data aggregations, not just raw documents.
If you want to show only 1 aggregated row per irisIxId.keyword you can set the size of all the other splits aggregations to 1, but then you'll only see the top value there.

If you're interested in raw documents, you can probably check Discover and use saved searched to show a table of documents in your dashboard.

[fefontana (Franco Fontana)]

Sorry @Marco_Liberati but your solution doesn't work in order to get a table like the image below. I need a really simple thing, a table showing only the filtered document and a unique item of the inner array. This issue doesn't occur if the document doesn't contain an inner array. I understand data aggregations in Kibana but I don't want to include in the table not existing rows. How can I do that in Kibana with this JSON data source ?. Do I have to transform the JSON ?.

Also tried this visualization in Lens after modify one value of the field "error" in the inner array, and I'm getting the same behavior. See the image below:

[fefontana (Franco Fontana)]

With a nested array of JSON's, the only way I found to build a Kibana table showing columns taken from the first level and the second level of the document was to split each document in the following way:

ORIGINAL DOC:

[
  {
    "field1": "Test1",
    "field2": "DescrTest1",
    "array": [
      {
        "fieldA": "error1",
        "fieldB": "message1"
      },
      {
        "fieldA": "error2",
        "fieldB": "message2"
      }
    ]
  }
]

SPLIT DOC:

[
  {
    "field1": "Test1",
    "field2": "DescrTest1",
    "array_fieldA": "error1",
    "array_fieldB": "message1"
  },
  {
    "field1": "Test1",
    "field2": "DescrTest1",
    "array_FieldA": "error2",
    "array_FieldB": "message2"
  }
]

The main disadvantage of this approach is to repeat all the level 1 fields for each item of the level 2.