[FEATURE] Flatten result index mapping for visualizing nested objects in Dashboards

[jackiehanyang]

Is your feature request related to a problem?
Many values are not flattened, making it difficult to view them on the dashboard. For instance, entity values are nested objects, and features are arrays. The requirement is to reference a feature by name and apply conditions like f1 > 3. Additionally, there is a need to perform terms aggregation on categorical fields. This will require adjustments to the mapping and the addition of new fields in the result index.

What solution would you like?
Approach 1. Flattening the result index on the AD backend side, keeping both the original nested fields and the newly added flattened fields.
This approach involves update the index mapping everywhere we create/update result index to include the new flattened nested fields. You can find an example of the updated result index mapping in the github issue linked above.
Pros:

• Flattening and indexing all nested as new entries in the mapping provides flexibility in querying and visualization.

Cons:

• Significant effort is required to ensure the codebase is modified correctly.
• This could double the size of custom result index, which increases costs for customer using it.

Approach 2. Flattening the result index at AD backend side, but only keeping the flattened fields.
This approach is similar to Approach 1. But instead of retaining both nested and flattened fields in the index mapping, we could only keep the flattened fields.
Cons:

• Except same effort as approach 1 needed at backend side, we would also need to make frontend changes to accommodate this approach.

Approach 3: Flattening the result index mapping on the OpenSearch Dashboard side
This approach focuses on flattening the result index mapping on the OpenSearch Dashboard side. However, the challenge is that, unlike the current dashboard that allows customers to access nested objects using standard dot path notation, we need the nested fields values to be part of the do path notation. However, these values are not indexed for fast lookup. After consulting with the dashboard team and researching the issue, it’s unclear whether it’s possible to implement this solution to support aggregation on nested fields.
Pros:

• This would be the best practice for solving the problem.

Cons:

• The effort required to achieve this is unknown

Approach 4 (PROPOSING): Using ingest processors to update the custom result index with flattened fields.
Ingest processors are a core component of ingest pipelines that preprocess documents before indexing. Utilizing an ingest processor would allow us to update custom result index documents with flattened fields before indexing. We can prepare the pipeline for you with a button click on the AD dashboard. After processing, the custom result index would contain both the existing nested fields and the flattened fields.
Pros:

• This approach requires the least effort on making the job done

Cons:

• Customers will need an ingest pipeline setup, but we can handle that for you.

[dblock]

[Catch All Triage - 1, 2, 3, 4]

[jackiehanyang]

After setting up the ingest pipeline to flatten the nested fields, I can see the new flattened fields on the index pattern page. However, on the visualization side, the Field dropdown list is not loading the newly added flattened fields. I have created an issue on the OSD side regarding this matter - opensearch-project/OpenSearch-Dashboards#8722