[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>...] [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>... declares the output columns by name. 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.

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:

• Precise types for structured modes. Returning the mapped scalar type (for example procs.pid
  as BIGINT) instead of ANY requires OpenSearchTypeFactory to emit precise ROW/element types
  for object/nested fields (a standing TODO in the type layer), which is a core, cross-command
  change well beyond multikv. Until then, structured columns surface as ANY and are pinned with
  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) tracked in [RFC] Support Schema-on-Read in PPL #4984 — 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/ copies only; new Multikv AST node carrying inField (default _raw),
  the declared fields, forceHeader, noHeader, rmOrig.
• 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.
• 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

• [RFC] Support Schema-on-Read in PPL #4984 — schema-on-read / field resolution (_MAP catch-all). Related: the substrate the
  deferred v2 auto-header mode will build on. Not a dependency of v1.
• Splunk multikv command — behavioral reference for the text-parsing mode.
• makeresults (merged) — shares the inline typing / nested-container-to-JSON-string convention and
  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?