[RFC] fieldformat command implementation as alias to eval as in PPL roadmap [#4287]

[asifabashar]

Problem Statement
Currently there is no fieldformat comamnd in OpenSearch SQL plugin that exists in Splunk SPL.

Current State
The Roadmap [#4287 ] provides list of commands that are not implemented.

Long-Term Goals

• fieldformat works the same way as Splunk SPL fieldformat command.

• How will this solution ensure sustainability or scalability over time?
  Use existing calcite framework where applicable.

• Consider whether the solution addresses the root problem and aligns with the overall goals.
  The Roadmap [RFC : PPL Query Engine Status and Roadmap for 2025/2026 #4287 ] is the goal and this solution provides the command fulfills one of the missing commands.

• How confident are we that this solution solves the problem?
  We are confident it will fulfill one the of the missing command.

Proposal

PPL Comamnd fieldformat:

fieldformat command can use an changes the format of a field value. Because commands that come later in the search pipeline will be impacted by format change, fieldformat command should be used as late PPL pipeline.

Syntax

fieldformat <field>=<eval-expression>[,<field>=<eval-expression>]
Required arguments: <field>
Description: The name of a new or existing field which is not wildcarded. The output will contain the field wiht value of the eval expression.

<eval-expression>: Syntax: <string>
Description: A combination of values, variables, operators, and functions that represent the value of your output field. Only one with the fieldformat command can be specified. To change mulitple formats, multiple fieldformat commands need to be used.

Example:
Original data:

os>source=accounts
| fields account_number, firstname, balance

fetched rows / total rows = 4/4

account_number	firstname	balance
1	Amber	39225
6	Hattie	5686
13	Nanette	32838

Formatting changes with fieldformat command:

os>source=accounts
| fieldformat balance = tostring(balance, "commas")
| fields account_number, firstname, balance

fetched rows / total rows = 4/4

account_number	firstname	balance
1	Amber	39,225
6	Hattie	5,686
13	Nanette	32,838

Concatenation Operation with dot:
To concatenate a string specify the prefix in quotes followed by period character as concatenation operator, followed by a function that returns string. Following example displays commas in the currency values with currency symbol.
...| fieldformat balance="$".tostring(balance,"commas")

Approach
Implement the plan in visitFieldFormat in CalciteRelNodeVisitor and store the parameters in FieldFormat class. The command will be just an alias of eval command.
Only exception to eval is to have an additional functionality where string in quotes followed by period character will behave as concatenation operator and it expects the next part after dot is string type.

Functions that can be used are those that work with eval commands:

Date time functions
Condition functions
Mathematical functions
JSON functions
IP address functions
String functions
Statistical functions
System functions

https://github.com/opensearch-project/sql/tree/main/docs/user/ppl/functions.

Alternative
Current alternative is eval command which can do everything fieldformat does except dot operator for string concatenation , but to migrate SPL query that uses fieldformat need a same named command so it minimizes migration effort.

Implementation Discussion

• For functionality that can be achieved calling eval command, it will call eval command functions,
• Dot operator for concatenation need to be implemented and parsed where it matches parameter pattern.

Is your feature request related to a problem?
Roadmap [#4287 ]

Do you have any additional context?
N/A

[asifabashar]

@LantaoJin @penghuo please review and assign this to me if it makes sense and no one else is working on it.

[dai-chen]

@asifabashar Could you follow the RFC template and add similar missing section as commented here: #4766 (comment)

[asifabashar]

@dai-chen please reivew