[RFC] PPL multikv command — extract rows from a field

[noCharger]

[RFC] PPL multikv command — extract rows from a field

Problem

Operational and log data frequently arrives with a table packed inside a single field, or with
records nested inside a document, and PPL has no terse way to turn either into rows:

• Table-formatted text. Command output such as ps, top, netstat, or df is captured as
  one text blob (for example a message field). It has a header row and one record per line, but
  PPL sees it as a single opaque string. Extracting the columns today means hand-rolling rex /
  parse regexes per tool, which is brittle against variable whitespace.
• Structured records. An array of objects (procs = [{pid,cpu}, ...]) or a single object must
  be exploded and projected with a mvexpand + eval field.sub + fields chain, which is verbose
  and easy to get wrong.

Splunk solves the first case with multikv. There is no PPL equivalent, and no single command that
covers both the text and the structured shapes with one interface.

Proposal

Add a streaming (mid-pipeline) command multikv that reads an input field and emits one row per
source record — a one-to-many, row-multiplying command. It runs on the coordinating node and
requires the Calcite (v3) engine.

multikv [field=<name>] [fields <col>[:<type>]...] [forceheader=<int>] [noheader=<bool>]

• field=<name> selects the input field. Defaults to _raw. OpenSearch has no implicit _raw
  field, so callers either pass field=<name> or place text in _raw first with eval _raw=<f>.
• fields <col>[:<type>]... declares the output columns by name; an optional :<type>
  (string|boolean|int|integer|long|float|double) types the column at plan time (see Column
  typing). This is the only form that yields named columns, and it is what makes v1 fixed-schema
  (see Scope).
• forceheader=<int> uses the given 1-based line as the header, skipping banner lines above it.
• noheader=<bool> performs row explosion only (no named columns), for example to count data lines.

Input modes (dispatch on the input field's type)

multikv inspects the plan-time type of field=<name> and routes to one of three paths. This is
the key design point: the same field= ... fields ... interface works whether the field is raw
text or already structured.

Input field type	Calcite type	Behavior
Text	VARCHAR	Parse the table text: a header row names the columns, each following line becomes a row. Extracted values are string.
Array of objects	ARRAY<ANY>	Explode into one row per element; read each declared column from the element. Values surface as ANY (see Column typing).
Single object	MAP<VARCHAR,ANY>	Read each declared column from the object; emits one row. Values surface as ANY (see Column typing).

For the structured modes, OpenSearchTypeFactory collapses object to MAP<VARCHAR,ANY> and
nested/array-of-objects to ARRAY<ANY>, so neither the sub-field names nor their mapped types
survive into the Calcite type; the container is a bag of ANY. The declared fields clause is
therefore what names the output columns (exactly as in the text mode), and reading a column via
ITEM/INTERNAL_ITEM yields ANY, not the mapped scalar type. Nested container values are
returned as-is (serialized JSON string), matching the merged makeresults convention
(isObject()||isArray()); extract deeper fields downstream with spath or another
multikv field=<subfield>.

Column typing

• Text mode: every extracted column is string, because values come from splitting text and
  there is no mapping to infer from. The typed engine coerces per operation, so stats avg(pctIdle)
  and where pctIdle > 100 work numerically; pin a hard type with cast downstream.
• Structured modes: extracted columns surface as ANY, not the mapped scalar type. A mapped
  sub-field does have a known type (for example procs.pid BIGINT, procs.cpu DOUBLE in the
  flattened mapping), but that type never reaches the command: OpenSearchTypeFactory collapses
  object/nested to MAP<VARCHAR,ANY>/ARRAY<ANY> at the type layer, and mvexpand then drops
  the typed dotted fields (tryToRemoveNestedFields, and Uncollect of ARRAY<ANY> produces
  ANY), so even a mvexpand field | fields field.sub chain returns ANY. Pin a type with cast
  downstream. Nested containers serialize to a JSON string.
• Inline typing (fields <col>:<type>). Declaring a type in the fields clause types the
  column at plan time instead of leaving the default, in both modes. It is lowered as a plan-time
  cast: text mode casts the string extraction to the declared type; structured mode casts the
  ANY extraction to it. This is exactly ... | eval col = cast(col as <type>) hoisted into the
  command, so it composes with both shapes and gives structured columns an explicit typed form
  without the core type-layer change (see Scope). :<type> reuses the shared makeresults scalar
  vocabulary (string|boolean|int|integer|long|float|double); the UDT types
  (date|time|timestamp|ip|json) are rejected with "use string and cast". The cast is a safe cast,
  so an unparseable value yields null rather than an error. Note: because the case-insensitive PPL
  lexer folds col: into the cross-cluster prefix token, a typed column name must start with a
  letter or *; for other names (for example _raw) declare it untyped and cast downstream.

Validation

• A bare multikv (no fields, no noheader=true) cannot resolve output column names at plan time
  and is rejected with actionable guidance ("add an explicit fields clause"). This covers the
  overwhelming majority of real usage, which is explicit-schema.
• The structured modes never feed a non-string value into the text-split path, so no runtime type
  error is possible from pointing field= at an object or array.

Scope

In scope (v1): the text-parsing mode, the array-of-objects mode, and the single-object mode
above, selected by field=<name> with an explicit fields clause (or noheader=true), on the
Calcite (v3) engine. forceheader and noheader are supported.

Out of scope / future:

• Automatic (implicit) types for structured modes. With no :<type>, structured columns still
  surface as ANY; recovering the mapped scalar type automatically (for example procs.pid as
  BIGINT without an explicit :long) requires OpenSearchTypeFactory to emit precise ROW/element
  types for object/nested fields (a standing TODO in the type layer), a core, cross-command
  change well beyond multikv. Until then, declare the type inline (fields pid:long) or cast
  downstream.
• Runtime auto-header (bare multikv). Detecting column names from the table's header row at
  runtime and materializing them dynamically, so no fields clause is needed. Deferred to a later
  version that reuses the schema-on-read / field-resolution substrate (_MAP catch-all + ITEM
  access), with no new API. v1 rejects the bare form with guidance instead.
• Aligned-offset (fixed-width) column parsing. v1 splits text on whitespace; detecting column
  boundaries from header character offsets (to handle values containing spaces and right-aligned
  numerics) is a later parsing-fidelity improvement.
• filter, rmorig, multitable, copyattrs, conf options. Wired in the AST/UDF where
  applicable but not exposed in grammar; deferred pending demand.
• parse-to-MAP performance optimization. v1 text mode re-scans the serialized record once per
  declared column (O(cols × record length)). A follow-up can parse each record once into a
  MAP and switch column access to native ITEM (O(1) per column). This shares the per-row MAP
  substrate with the v2 auto-header work above, so the two are natural to do together.
• The v2 (pre-Calcite) engine. multikv requires plugins.calcite.enabled=true; v2 returns the
  standard "supported only when Calcite is enabled" error, matching the merged makeresults
  convention.

Note on row limits: the makeresults compile-time inline caps (row/cell/char budgets from the
JVM 64KB per-method bytecode limit) do not apply here. multikv explodes at runtime via
mvexpand/Uncollect, with no compile-time inlined literals, so those bytecode limits are not hit.

Alternatives considered

• rex / parse regex per tool (text) + mvexpand + eval chain (structured). The current
  workarounds. They work but are verbose and tool-specific; multikv is the terse, unified command
  that lowers to the same primitives (mvexpand, native field access) — it is syntax sugar with a
  single interface over both the text and structured shapes.
• Two separate commands (one for text, one for structured). Rejected: the field=<name> type
  dispatch lets one command serve both, and the declared-fields contract is identical, so a split
  would duplicate surface area for no user benefit.

Implementation sketch

• Grammar in the ppl/ copy only: a multikv-local multikvField rule adds the optional col:type
  (matched via the CLUSTER token, since the case-insensitive lexer folds col: into it; the
  column name is that token minus its trailing colon). New Multikv AST node carries inField
  (default _raw), the declared fields with a parallel per-column fieldTypes (null when
  untyped), forceHeader, noHeader, rmOrig. The :type name resolves through a shared
  PplInlineTypeResolver that makeresults also uses (one type vocabulary, one rejection policy).
• CalciteRelNodeVisitor.visitMultikv builds the child once, inspects the field= column type, and
  dispatches:
  • Text → MULTIKV_SPLIT UDF (record array) → mvexpand → MULTIKV_EXTRACT(record,'col') per
    declared column.
  • Array of objects → buildExpandRelNode (mvexpand) → INTERNAL_ITEM(field,'col') per column,
    named via project(nodes, names).
  • Single object (MAP) → skip mvexpand; INTERNAL_ITEM(field,'col') per column → one row.
  • In every mode, a column declared with :<type> wraps its extraction in a plan-time safe cast to
    the declared type (via OpenSearchTypeFactory); undeclared columns are unchanged.
• Bare-form rejection lives at the semantic layer (field resolution), not in grammar, so enabling v2
  auto-header later is "relax the rejection + wire _MAP" with no grammar change.

Reference implementation: PR on opensearch-project/sql (linked below). Unit + integration tests
cover field=, text, array-of-objects, and single-object modes.

Related

• Splunk multikv command — behavioral reference for the text-parsing mode.
• makeresults (merged) — shares the inline name:type scalar vocabulary (via the common
  PplInlineTypeResolver) and the nested-container-to-JSON-string convention, plus the
  "Calcite-only, v2 returns unsupported" convention.

[songkant-aws]

Have we discussed requiring users to explicitly specify column types in the table header? For example,

CPU:keyword pctUser:integer pctIdle:long
all 5       90
0   3       92

[songkant-aws]

Since indexed object subfields already exist in the flattened mapping with concrete types, why does structured multikv extract them from an ARRAY or MAP<VARCHAR, ANY> instead of resolving the declared fields back to their mapped paths and types?

Consider the following document:

{
  "procs": [
    {"pid": 1, "cpu": 0.5},
    {"pid": 42, "cpu": 9.1}
  ]
}

with the following mapping:

{
  "procs": {
    "type": "nested",
    "properties": {
      "pid": {"type": "long"},
      "cpu": {"type": "double"}
    }
  }
}

After ingestion, the nested mapping is flattened into a schema where each subfield already has a known type:

procs      ARRAY<ANY>
procs.pid  BIGINT
procs.cpu  DOUBLE

Given that procs.pid and procs.cpu are already mapped and typed, why does structured multikv read them again from the generic procs container using fields pid cpu? Could it instead expand the nested object while preserving the existing mapped subfield types?

[noCharger]

Both good calls.

Header types: agree on the intent; the question is where it lives. Types in the parsed table text are runtime values, so at plan time the planner sees them too late. Declaring them in the fields clause keeps it plan-time:

source=logs | multikv field=message fields pctIdle:long

This is opt-in sugar: it hoists | eval pctIdle = cast(pctIdle as long) into the command. The default stays string plus a downstream cast, which composes fine.

Structured subfield types: agree the flattened mapping carries them (procs.pid BIGINT, procs.cpu DOUBLE), but they get erased before the command runs. OpenSearchTypeFactory folds object/nested into ANY containers and mvexpand drops the typed dotted fields, so mvexpand procs | fields procs.pid also yields ANY today. Auto-recovering the mapped type is a TypeFactory-level change (core, cross-command), so multikv leaves it to a downstream cast, or the same opt-in :type:

source=hosts | multikv field=procs fields pid:long cpu:double

Net: multikv extracts (string in text mode, ANY in structured), and typing composes via cast or opt-in :type. One command stays focused on one job.