[expressions] Standard library of table manipulation functions

[wylieconlon]

Because tables are the shared exchange format of choice with expressions, table manipulation functions should work on any table from any source, and be part of a standard library. This issue is describing the standard library of functions that I would expect to exist in any table manipulation tool- if this list is incomplete, please feel free to comment.

There are several categories of functions that are needed:

• Functions that take table input and produce table output
• Functions that take tables as input but don't produce table output, such as math
• Functions that join two tables
• Functions that operate on primitive types, such as number, string, and date.

Functions that take a table and return a table

Description	Expression function	Example
Add a column with a known value	staticColumn
Add a column with row-level function	mapColumn	Divide two numbers in the same row
Rewrite a column	mapColumn	Rewrite values to "high" or "low" based on condition, can only be used with row-level context
Add a column with table-level function	?	Column is added with total number of rows
Add a column with a group-level function	ply, but could be renamed	Calculate the subtotal grouped by "category"
Remove a column	columns include="a, b" exclude="c, d"
Change the data type for a datatable	alterColumn	After rewriting the column, you need to change its data type
Change the aggregation metadata for a column	?	This only works for kibana_datatable
Filter rows based on condition	filterRows
Filter rows by position	head, tail
Group by	?	Some table structures are explicitly grouped
Add subtotal rows	?	Needs discussion, but grouping tables is an important feature in some tools
Sort rows overall	sort
Sort rows with multiple sorts	Should be added to sort
Reduce with aggregation function	?	Reduce a table by applying the SUM aggregation
Reduce and group by	?	Get the SUM of each group, then reduce
Pivot	?	Requires a grouping condition and an aggregation to use such as SUM
Calculate moving or window function	?	Moving average, cumulative sum, and others require a variable number of rows as context
Derivative	?
Add new rows using a regression function	?	For prediction purposes
Add new rows by constructing them manually	?
Rename a column and update the rows	?

Functions that take tables as input but produce different output

Name	Expression function
Take nth value of column	getCell
Count rows	rowCount

Join functions

There is probably only one table join function which accepts two tables and a condition. It joins them based on the condition for each row. When there are identical columns, they are merged. When the columns share IDs but aren't identical, we can throw an error or assign a new ID to the right side.

Functions that operate on primitive types

I don't actually think each of these functions should be implemented in the expression grammar as separate named functions. It would be really nice to have a built-in grammar that resembles Javascript.

Name	Expression function	Example
TinyMath	math	sum(c1, c2) where the object is { c1: 5, c2: 10 }
Format number	formatNumber
Parse as number
Format date	formatDate
Parse date	date
Round date	roundDate
Add interval to date	?
Split string	?
Substring	?
Get string index	?
Regex replace string	replace
Boolean logic	lte, eq, etc

[elasticmachine]

Pinging @elastic/kibana-app-arch (Team:AppArch)

[ppisljar]

Thank you for contributing to this issue, however, we are closing this issue due to inactivity as part of a backlog grooming effort. If you believe this feature/bug should still be considered, please reopen with a comment.