[FEATURE]New expand PPL Command

[YANG-DB]

Is your feature request related to a problem?
Adding a PPL new expand command which adds array and nested object expansion functionality to PPL

Is your feature request related to a problem? Please describe.
OpenSearch's Piped Processing Language (PPL) currently lacks an efficient way to expand arrays and nested objects into separate events, similar to SQL's UNNEST or JSON expansion functions. This limitation hinders the analysis of complex data structures, particularly when working with JSON logs or documents containing arrays or nested objects.

Describe the solution you'd like
We propose adding a new command to PPL that would allow users to expand arrays and nested objects into separate events, similar to SQL's UNNEST function, but with additional flexibility.

The functionality should:

1. Expand array fields or nested objects into separate events (similar to SQL's UNNEST)
2. Retain all other fields from the original event in each new event (addressing a limitation of SQL UNNEST)
3. Support nested fields and complex JSON structures (going beyond basic SQL capabilities)
4. Allow for subsequent processing of each expanded value in the PPL pipeline
5. Work seamlessly with unstructured or semi-structured data (unlike SQL, which typically requires predefined schemas)

SQL-like example and comparison:
Consider this SQL-like syntax:

SELECT * FROM my_index
CROSS JOIN UNNEST(items) AS expanded_item
WHERE expanded_item.status = 'active'

The proposed OpenSearch PPL equivalent might look like:

source =  my_index 
| parse my_nested_field `?<items>[*]` as items
| expand items
| where items.status = "active"

Key differences and advantages:

1. No need for explicit JOIN syntax, making it more intuitive for log analysis
2. Automatic handling of nested structures without need for complex JSON parsing functions
3. Ability to work with dynamic schemas and unstructured data

Describe alternatives you've considered
Current alternatives include:

1. Using complex JSON path queries, which can be cumbersome
2. Processing the data outside of OpenSearch, reducing real-time analysis capabilities

Additional context
This feature would bridge the gap between SQL's structured data handling and the need for flexible, real-time analysis of semi-structured log data. It combines the power of SQL's UNNEST with the flexibility required for log and event processing.

Potential Impact

• Simplified queries for complex data structures in logs and events
• Enhanced real-time analytics capabilities for nested JSON data
• Improved performance compared to client-side processing of nested structures
• Better alignment with SQL-like functionality while maintaining PPL's simplicity

Proposed Implementation
The new command (e.g., expand) could be implemented as a new command in the PPL engine, combining the concepts of SQL's UNNEST with the flexibility needed for unstructured log data.

[salyh]

@YANG-DB @vamsi-amazon It would be great if we can have a few examples (in- and output in tabular form) and if can clarify the relationship to the flatten (#669) command because it seems that its partially overlapping

[YANG-DB]

The main objective of expand is to mimic the following behaviour :

Given the next ip->product Id purchase table,

IP Address	Total Purchases	Total Products	Product IDs
107.3.146.207	72	3	DB-SG-G01, FS-SG-G03, WC-SH-G04
128.241.220.82	95	2	DB-SG-G01, DC-SG-G02
194.215.205.19	60	4	DB-SG-G01, DC-SG-G02, FS-SG-G03, WC-SH-G04
211.166.11.101	91	2	DB-SG-G01, WC-SH-G04
87.194.216.51	134	3	DC-SG-G02, FS-SG-G03, WC-SH-G04

we would like to expand each individual product, or rows, for each value in a multi-value field.
So now a new row is created for each product ID. The multivalued fields are expanded into individual search results. The other fields are unchanged.

ipaddress	total_purchases	total_products	productId
107.3.146.207	72	3	DB-SG-G01
107.3.146.207	72	3	FS-SG-G03
107.3.146.207	72	3	WC-SH-G04
128.241.220.82	95	2	DB-SG-G01
128.241.220.82	95	2	DC-SG-G02
194.215.205.19	60	4	DB-SG-G01
194.215.205.19	60	4	DC-SG-G02
194.215.205.19	60	4	FS-SG-G03
194.215.205.19	60	4	WC-SH-G04
........

The following spark sql command should allow our expanding of the multi-valued field

SELECT ipaddress, total_purchases, total_products, exploded_productId
FROM your_table
LATERAL VIEW explode(productId) AS exploded_productId

Notes
Ensure that the Product IDs in the original data are stored as an array.

This functionality will enhance our ability to analyze product purchases by providing more granular data for each product associated with an IP address.

Example ppl expand queries:

 - source = table | expand productId as product_Id