How to break down (terms aggregation) in Kibana Lens by a sub-key of a flattened field (other than a runtime field)

Elastic Stack Kibana
1

Environment

  • Elasticsearch / Kibana: 8.14.0
  • ~several million to ~10 million documents per index (daily indices)

What I want to do

In Kibana Lens, I want to break down (Top values / terms aggregation) by the value of a specific sub-key of a flattened field.

For example, given my_object (type flattened), I want to aggregate/visualize by the value of the sub-key my_object.some_subkey.

Background (why we use flattened)

This field contains arbitrary JSON whose keys are not fixed. We previously used object (dynamic mapping), but the same key would be a string in one document and an object in another, causing mapping conflicts that rejected entire documents (data loss). To prevent this, we switched the field to flattened.

The problem

Switching to flattened resolved the conflicts, but since sub-keys are not independent fields, my_object.some_subkey does not appear in the Lens field picker and cannot be used for a breakdown. It is likewise not selectable in the "Add filter" UI.

What I have tried

  1. Runtime field (keyword) defined on the data view to extract the sub-key:
    painless
def v = doc\['my_object.some_subkey'\];
   if (v.size() > 0) emit(v.value);
  1. → This made the field available for Top values in Lens and the breakdown worked. However, with large data over a wide time range, the search returns partial results (some shards time out), so it is not usable for a practical dashboard. It works over a narrow time range.
  2. Reverting flattened back to object (dynamic:true) → sub-keys become real fields and show up in Lens, but this reintroduces the mapping conflicts (data loss) we originally solved, so it is not an option.

Questions

  1. Is there a way to aggregate/break down by a specific sub-key with good performance while keeping the field as flattened — i.e., something other than a runtime field that does not cause partial results on large data?
  2. As a general practice, how should this case be designed: "keep arbitrary JSON as flattened to avoid conflicts, but expose a few important keys as typed fields for fast aggregation"?
  • What I have in mind is promoting (copying) just those important keys out of the flattened field into separate, indexed keyword fields. Is this the recommended approach, or is there a better way?

Thank you in advance.