Include empty rows not working for date field in Kibana table lens

[Jonas_S]

Hello,

i'm creating ES indices every month where one of the date fields can be empty for all documents from time to time. To make sure that visualizations do not break if this happens i added a mapping for this field to the index template.

New indices have the field in their mappings, as expected:

I have a table lens in a dashboard that should show some fields, including this, from all documents. But as soon as i add it, no more results are found. I thought the solution would be the "Include empty rows" switch, but it does not change the behaviour as expected. I thought the table would again show all documents with a "(missing value)" entry for the empty date fields.

I'm using Kibana 8.2. Is this fixed in a newer Version?

Edit: My solution so far was an extra string field containing '-' if the date is null, or the date as string otherwise. But i was trying to switch to a true date field such that the sorting an filtering is working as expected.
With a smal testsample i can see all three documents using the string version:

As soon as i add the true date field, the two documents without it disappear. The Include empty rows switch does nothing:

Best regards
Jonas

[jsanz (Jorge Sanz)]

I think I understand what you want to achieve but let me share my sample data so we can align in the issue description

With this I create an index with a keyword and date fields, along with the supplemental "date as string" field you described. I also added a few documents :

DELETE delete_date_missing

PUT delete_date_missing
{
  "mappings": {
    "properties": {
      "keyword_field": {"type": "keyword"},
      "date_field": {"type": "date"},
      "date_keyword_field": {"type": "keyword"}
    }
  }
}

POST delete_date_missing/_bulk
{"index":{}}
{"keyword_field":"hola","date_keyword_field":"-"}
{"index":{}}
{"keyword_field":"mundo","date_field":"2022-09-08T00:00:00+02:00","date_keyword_field":"2022-09-08T00:00:00+02:00"}
{"index":{}}
{"keyword_field":"baaaar","date_field":"2022-09-09T18:00:00+02:00","date_keyword_field":"2022-09-09T18:00:00+02:00"}

So I think you want to create a table where the columns are for example the count of the rows per date_field (say by day) and you want an extra column for the data that is not bucketed on any of the date bins, so the table would show the three documents in a table cell.

And because the first document is not showing in the table you are creating a copy as a keyword field to also get the - as a column

Does this match what you were referring in your post?

I also don't get any difference from using the Include empty rows switch in the columns definition, so I'm not sure what is it supposed to do but before going deeper let me know if I got it right.

[Jonas_S]

Hello,

i tried to continue this using your example, but i could not add the documents to the index. It says there is no body, even if there clearly is. What am i doing wrong here?

My first post was a bit confusing because i was talking about two problems at once. The other one being visualisations breaking, because of fields that are not found because of no document containing the field. Which i was trying to solve by giving the mapping. But let's leave this out for now.

What i'm trying to understand now is how to create a lens showing some fields (including a date field that can be empty) of all documents, even if a document has an empty date field.

Sorry, i have to modify your example as i could not get it to work:

POST delete_date_missing/_bulk
{"index":{}}
{"persnr.keyword":"3014","bestaetigtes_datum_string":"-"}
{"index":{}}
{"persnr.keyword":"3021","bestaetigtes_datum":"10.11.2024 @ 00:00:00.000","bestaetigtes_datum_string":"10.11.2024"}
{"index":{}}
{"persnr.keyword":"3024","bestaetigtes_datum_string":"-"}

If i create the lens with persnr.keyword and bestaetigtes_datum_string it works as expected:

Two columns showing the content of the fields.

Now, if i add the date field, only the document containing this field is shown:

My expectation was: if i flip the switch "Include empty rows" that all three documents are shown again, similar to the "include documents without this field" switch for empty string fields. But as it turns out, this does not happen:

So this would be what i expected:

The discoverer is able to do it, but it seems like table lens is not:

Using a saved search is sadly no option, because as far as i know it is not possible to include a saved search in a dashboard without the ability to look at the whole document, because the expand document button at the start:

can not be removed. And the people who will work with the dashboard should not be able to see all fields of the documents.

Maybe this works as intended, but then i don't understand what this switch for empty rows is for. I assumed if i can include documents with empty string fields it should be possible for empty date fields as well.

Best regards
Jonas

[jsanz (Jorge Sanz)]

OK I think I understood well the issue. After asking to my colleagues, this is what I can note and suggest:

• The include empty rows switch is meant to fill the gaps when doing a histogram, which is different from counting documents without the field.
• You can create the separate keyword field using a runtime field, that way you are not indexing repeated data, less management, etc.
• You can add a separate visualization with the count of missing values to give your users a complete picture.

So I recreated again the index without the keyword copy:

DELETE delete_date_missing

PUT delete_date_missing
{
  "mappings": {
    "properties": {
      "text_field": {"type": "text"},
      "keyword_field": {"type": "keyword"},
      "date_field": {"type": "date"}
    }
  }
}

POST delete_date_missing/_bulk
{"index":{}}
{"text_field":"hola"}
{"index":{}}
{"keyword_field":"mundo","date_field":"2022-09-08T00:00:00+02:00"}
{"index":{}}
{"text_field":"fo","keyword_field":"baaaar","date_field":"2022-09-09T18:00:00+02:00"}

Then I created a new runtime field in the Data View called date_keyword_field:

if (doc['date_field'].size() != 0){
    ZonedDateTime zdt = doc['date_field'].value;
    emit(zdt.format(DateTimeFormatter.ISO_LOCAL_DATE))
} else {
    emit('-')
}

Now you can create a table as you had on the left and on the right a new table only including those rows where the runtime field value is -

Hope this helps

[Jonas_S]

Thank you for taking the time and giving your suggestion.

So it is not possible at the moment to include documents with empty number or date fields in a table lens. So i would not get a different behaviour if i would upgrade to 8.4 from 8.2.

I like the approach with the runtime field but i think it doesn't give me much of a benefit because of localization. In germany and thus in our company we only use DD.MM.YYYY as a date format. And a sort on such a keyword field will be lexicographic, which is meaningless with the used date format.

Many thanks nonetheless
Jonas

[jsanz (Jorge Sanz)]

Take a look to the painless and dates guide, it provides plenty of detail and examples on how to parse and format dates in any formats, I just used the simplest example for brevity.

Using Datetime in Painless | Painless Scripting Language [master] | Elastic

It may not be useful this time but it's a good tool to have in your box when working with the Elastic stack.