[BUG] Visualization Field dropdown list not loading all index pattern aggregatable fields

[jackiehanyang]

Describe the bug

On the Index Pattern page in the Dashboards Management section, I can see all fields associated with this index pattern. However, when building a visualization based on this index pattern, the Field dropdown is not loading all the fields that were shown on the Index Pattern page. For example, the aggregatable field expected_values.likelihood, which appears on the Index Pattern page, is not showing up in the Field dropdown on the visualization page.

[ananzh]

I could see the problem but for opensearch dashboards sample data we also have nested fields and they could be loaded in visualization.

@jackiehanyang could you share with me how you use ingest pipeline to process and flatten nested fields before indexing? and how to reproduce? thanks.

[ananzh]

@jackiehanyang more questions: I am reading the proposal you made in AD. So the ingest processor runs on the OpenSearch server side, not in Dashboards right? When a document is indexed, it goes through the ingest pipeline first. The processing happens before the document is actually stored. So the workflow is something like this:

Original Document
↓
[Ingest Pipeline]
↓
Document Transformed
↓
Indexed in OpenSearch
↓
Available for Query

Is my understanding correct? So if the data is like

"field": [
    {
      "name": "a",
      "value": "a"
    },
    {
      "name": "b",
      "value": 2
    },
    {
      "name": "c",
      "value": 3.12
    }
  ],

would be stored as

"flattened_field.a": { "type": "string" },
"flattened_field.b": { "type": "number" },
"flattened_field.c": { "type": "float" },

for OSD to query? could you share the mapping of this index pattern GET opensearch-ad-plugin-result-t1/_mapping in OSD?

[ananzh]

The reason why Jackie’s way is not working is due to the mapping is still nested. Currently we don’t support visualize nested field in OSD. Here is more research:

Current Support

• Index Patterns: Can detect and manage nested fields
• Discover: Can display nested field contents
• Search: Search bar and DQL support nested field queries
• Vega: Can visualize nested data through custom specifications

Missing Support

• Standard Visualizations

Technical Limitations

The visualization limitation stems from three main issues:

1. Field Selection:

   • Nested fields are filtered out at the field selection level
   • No configuration to specify which visualization types support nested fields

2. Query Construction:

   • Aggregation DSL construction doesn't handle nested paths
   • Missing support for nested and reverse_nested aggregations

3. Response Handling:

   • Tabify response parser can't properly interpret nested aggregation responses
   • No handling of metrics across nested/parent document boundaries

Required Changes

Proper nested field support requires:

1. Query Building:

• Enable nested field selection for supported visualizations
• Generate proper nested/reverse_nested aggregations
• Track and maintain nested paths through aggregation chains

2. Response Parsing:

• Enhance tabify to handle nested response structures
• Properly aggregate metrics across nested boundaries
• Support nested bucket prefixing

Two workarounds until implementation

• Use Vega visualization with custom specifications. We could add document to guide users.

• Pre-process data to flatten nested structures before visualization

References

1. Original Kibana issue: Nested field support elastic/kibana#1084
2. OSD issue: Nested field support in visualizations #657
3. Closed PR: Implement nested support for histograms aggregations #794

[jackiehanyang]

The reason why Jackie’s way is not working is due to the mapping is still nested. Currently we don’t support visualize nested field in OSD.

@ananzh I just wanted to clarify the problem here. It's not that we cannot visualize nested fields anymore, as mentioned in the original Kibana issue and OSD issue. We're already taking the second workarounds approach as I have already implemented a solution to pre-process the nested data before indexing it, so the nested fields are indexed along with their flattened versions as string or number types. For example, in the first pic in this issue, feature_data_data is indexed as a number type, and feature_data_feature_name as a string type. These fields are not nested and appear correctly on the Index Pattern page. However, the issue is that when building a visualization on the Visualization page, these fields do not appear in the Field dropdown list.

We are avoiding asking customers to make any configurations on their side to make this work. This is because customers may have multiple detectors, and we do not want them to customize configurations individually for each detector. Additionally, there is a significant gap between the Index Pattern and Visualization. While the Index Pattern shows that these fields are aggregatable, customers cannot actually aggregate them when building a visualization based on this index pattern. Can we actively work on a solution to bridge this gap for our customers?