[RFC] xyseries command implementation in PPL

Problem Statement
xyseries command is missing in PPL which is part of todo in roadmap

This RFC proposes adding a new PPL transforming command, xyseries, with syntax and behavior aligned with SPL .

xyseries converts row-oriented grouped results into a wide table where:

• One field is the X axis (x-field) and stays as a row key.
• One field (y-name-field) provides a part of column name from proivded paramter values to choose from and used as pivot values in conjuction with (y-data-field) field name , where data for this column are pivoted cells that are agrregated value fields.

OpenSearch PPL already supports stats, chart, and timechart, but there is no direct equivalent for SPL xyseries.

Adding xyseries improves:

• Query readability for pivot-style result shaping.
• Dashboard interoperability for charts requiring wide-table series layout.

Current State
There is no xyseries compatiable command.

Long-Term Goals
Provide xyseries functionality.

Proposal
Summarizes the suggested solution or improvement.

Approach

User Syntax

xyseries  [sep=<string>] [format=<string>] <x-field> <y-name-field> in (<value1>, <value2>, ...) <y-data-field>[,<y-data-field2>

Arguments

• x-field (required): row key in output.
• y-name-field (required): Number of values used to generate output series column names based on provided in paramter values such as <value1>, <value2> etc
• y-data-field... (required, at least one): value field(s) used to fill cells.

Options

Option	Default	Behavior
sep	":"	Separator between y-data-field name and 'in' parameter values , etc.
format	None	Naming template for output series columns represented as $AGG$<sep>$VAL$ . For each data row pivot value for y-name-field and $AGG$ (y-data-field name). The format parameter inserts the (y-data-field name) uses “:” as the built-in separator and then pivot value. $VAL$

If format is omitted:

• Single y-data-field: output column is based row number .
• Multiple y-data-field: output column is $AGG$<sep>$VAL$ where $VAL$ represent provided pivot values in 'in' operator.

Examples

Actual data:
ppl
 source=weblogs| stats count(host) as host_cnt, count(method) as method_cnt by url, response

host_cnt	method_cnt	response	url
3	3	200	/page1
1	1	404	/page1
5	5	200	/page2
2	2	500	/page2

After xyseries transformation:
ppl

source=weblogs
| stats count(host) as host_cnt, count(method) as method_cnt by url, response
| xyseries url response in ("200","404","500") host_cnt, method_cnt

url	host_cnt: 200	host_cnt: 404	host_cnt: 500	method_cnt: 200	method_cnt: 404	method_cnt: 500
/page1	3	1	null	3	1	null
/page2	5	null	2	5	null	2

If format is not specified, by default, you’d see something like:
url:count(host) and url:count(method) as column names.

If you provide your own separator through the format option, it overrides anything defined with sep. If for example, sep is set to “-”, but format specifies “+”, and because format has higher priority, the “+” is used.
Here, $VAL$ and $AGG$ are placeholders represent and the respectively. In the output, you can see that the name field (url) and the data field count(host) may appear in the position of $VAL$ and $AGG$, depending on how the format string arranges them.

Semantics

Input shape and type rules

Support xyseries only when pivot values are explicitly provided.

• x-field and y-name-field must exist in input schema..
• y-name-field values are converted to string for output column naming.
• y-data-field One or more fields that contain the data to chart. If there are multiple fields specified, separate the field names with commas.

Null handling

• Rows with null in a given y-data-field do not contribute a value for that generated series column.

Implementation Plan

Validate required fields/options and defaults.
Project to x, y_name selected from provided pivot values in 'in' operator, and selected y_data fields.
Build generated series column name using format or default naming with sep .
Pivot to wide schema based on provided pivot values passed as parameter.

Today our direction is to keep PPL commands translatable to SQL/Calcite plans as much as possible. We also plan to run PPL on Spark; SQL-native xyseries (with explicit in (...) values) is portable to Spark SQL, while post-processing would require separate Spark-side custom implementation.
Composability: if xyseries is implemented after query execution, it effectively must be terminal and cannot be reliably chained with downstream commands. (Reference: Comments from penghuo )

out of scope

grouping option which does not apply for multifile input in OpenSearch.
Alternative
chart , timeseries can be used for similar use cases but not exactly same.

Limitations

As the rows will be transposed to columns, a limit is required field as calcite planning phase number of rows are not known.