Nested fields and arrays in Elasticsearch SQL

Object and nested fields arrive in the engine as JSON. Read one key with col:field, or expand an array into one row per element with LATERAL FLATTEN.

Object versus nested#

An object field is flattened by Elasticsearch into dotted paths (address.city) and queried like any other field. A nested field keeps each array element as its own hidden document, so the cluster can match sku = 'A1' AND qty > 5 within one element. The mapping type changes how the cluster indexes the data; in FlatQL SQL both arrive as a JSON column named after the root field, with the structure intact.

DESCRIBE orders shows items as nested and its children as items.sku, items.qty, items.price. The type mapping page lists what each type can do on the cluster.

Reading one key#

Snowflake-style JSON access works on any JSON column: col:field, col['field'] and col:a.b are equivalent. Comparing a JSON value with a string literal (f.value:sku = 'A1') extracts it as text automatically; add ::integer or ::double when you need arithmetic. For an object field the dotted path is also accepted as a column, so address.city and address:city return the same value, and a filter on address.city is pushed to the cluster by the child's own mapped type. For an array such as items the key lives on each element, which is where FLATTEN comes in.

Expanding an array with LATERAL FLATTEN#

SELECT o.order_id,
       o.placed_at,
       f.index               AS line_no,
       f.value:sku::varchar  AS sku,
       f.value:qty::integer  AS qty,
       f.value:price::double AS unit_price
FROM orders o,
     LATERAL FLATTEN(input => o.items) f
WHERE o.status = 'paid'
  AND o.placed_at >= '2026-10-01'
ORDER BY o.placed_at DESC
LIMIT 200;

One row per element of items. The columns f exposes:

TABLE(FLATTEN(input => items)) without LATERAL is accepted too. The WHERE on o.status and o.placed_at is pushed to the cluster as term and range filters; the expansion happens afterwards in the engine on the documents that came back. Everything about pushdown still applies to the parent table.

Aggregating over elements#

Once the lines are rows, normal SQL applies:

SELECT f.value:sku::varchar AS sku,
       SUM(f.value:qty::integer) AS units,
       SUM(f.value:qty::integer * f.value:price::double) AS revenue,
       COUNT(DISTINCT o.customer_id) AS buyers
FROM orders o,
     LATERAL FLATTEN(input => o.items) f
WHERE o.status = 'paid'
  AND o.placed_at >= '2026-10-01'
GROUP BY 1
ORDER BY revenue DESC
LIMIT 50;

This is the SQL equivalent of a nested aggregation with a terms bucket and two sum sub-aggregations, with the join back to the parent's customer_id thrown in. The scan is bounded by the pushed filters and the 50,000-row cap per index, so keep the date range tight on large order histories.

Filtering on element values#

A condition on f.value runs in the engine, after the fetch:

SELECT o.order_id, f.value:qty::integer AS qty
FROM orders o, LATERAL FLATTEN(input => o.items) f
WHERE o.status = 'paid' AND f.value:qty::integer >= 10;

To let the cluster narrow the parents first, add a predicate on a top-level field (status, date, customer) or filter on a child path and check the Show as DSL tab to see what went to the cluster.

When the question is really an Elasticsearch nested query (match the same element on two conditions, with scoring), write the request directly in the worksheet; Ask FlatQL produces it in Elastic DSL mode.

Arrays of scalars#

A keyword field that holds several values (["eu", "uk"]) needs no FLATTEN for the common cases. ARRAY_SIZE(col), ARRAY_CONTAINS('uk'::variant, col), ARRAY_POSITION and ARRAYS_OVERLAP accept the column whatever shape the documents use; a scalar is treated as a one-element array. For the cluster side, col = 'uk' is pushed as a plain term filter and matches documents whose array contains the value, which is how Elasticsearch treats multi-valued fields.

Joining flattened rows to another index#

Flattened rows join like any table, so a line-level result can be enriched from a second index with a normal JOIN ... ON. When the join key is a plain column on both sides, FlatQL fetches the smaller side first and sends its keys to the other as a terms filter; when the key is an expression such as f.value:supplier::varchar, that pushdown does not apply and the other side is scanned up to the cap. The joins page explains the ordering, and joins across indices walks through a full example with the DSL it generates.