Unnesting

Unnesting expands an array-valued field into a stream of rows, one per array element. The unnesting features in FDB Record Layer are aligned with the PartiQL notation, in which an array-typed reference appears as a table source in the FROM clause.

Important

FDB Record Layer does not support the standard SQL UNNEST() table function.

Basic Syntax

To unnest an array field, reference it as a table source after the row source that contains it:

SELECT table.*, element FROM table, table.array_column AS element

For each row of table, the array is expanded and element is bound, in turn, to each element. The result is one row per input-row/element pair. If the array is NULL or empty in a row, that row contributes no output rows. In other words, the semantics of unnesting are comparable to an inner join.

Important

FDB Record Layer does not support a LEFT OUTER JOIN variant for unnesting with outer join semantics.

As with other table sources, the AS keyword is optional:

SELECT table.*, element FROM table, table.array_column element

If the array element type is a STRUCT, element refers to the struct and its fields can be accessed via qualified names (element.field) or by star expansion (element.*). If the element type is a scalar, element refers directly to the scalar value and writing element.* is not allowed.

The query above shows the PartiQL notation for unnesting. An equivalent but more verbose way to express this unnesting operation is with a lateral subquery in the FROM clause:

SELECT table.*, elements.element FROM table, (SELECT element FROM table.array_column) AS elements

The planner produces the same plan as with the PartiQL form.

Generating Ordinals with the AT Clause

In addition to the array elements themselves, unnesting can generate the 1-based position of each element in its original array. To do so, add an AT clause after the element alias:

SELECT table.*, element, ordinal FROM table, table.array_column AS element AT ordinal

ordinal is an INTEGER bound to the position of the element. The position is 1-based.

The element alias is optional when AT is used. In the rare case where only the ordinals are needed, you can leave it out:

SELECT table.*, ordinal FROM table, table.array_column AT ordinal

The AT clause is only valid on an array-typed source. Applying it to a base table, view, or CTE raises a WRONG_OBJECT_TYPE error (SQLSTATE 42809).

Note

Sorting on the AT ordinal is not supported.

Examples

Setup

For these examples, assume we have the following type and table:

CREATE TYPE AS STRUCT line_item (sku STRING, qty INTEGER)

CREATE TABLE orders (
    order_id BIGINT,
    tags STRING ARRAY,
    items line_item ARRAY,
    prices DOUBLE ARRAY,
    PRIMARY KEY (order_id)
)

INSERT INTO orders VALUES
    (1, ['priority', 'gift'], [('A1', 2), ('A2', 1)],            [9.99, 19.99]),
    (2, ['backorder'],        [('B1', 3)],                       [4.50]),
    (3, NULL,                 [('C1', 1), ('C2', 2), ('C3', 1)], [29.99, 14.50, 5.00]),
    (4, [],                   [],                                [])

The prices array is positionally aligned with the items array: prices[i] is the unit price of items[i].

Unnesting a Scalar Array

The following query unnests the tags array so that each tag becomes its own row:

SELECT order_id, tag FROM orders, orders.tags AS tag

order_id	tag
1	"priority"
1	"gift"
2	"backorder"

Orders 3 and 4 contribute no rows because their tags value is NULL and [], respectively.

Unnesting a STRUCT Array

The following query unnests items and accesses the fields of the line_item struct via qualified names:

SELECT order_id, item.sku, item.qty FROM orders, orders.items AS item

order_id	sku	qty
1	"A1"	2
1	"A2"	1
2	"B1"	3
3	"C1"	1
3	"C2"	2
3	"C3"	1

Star Expansion on a STRUCT Element

Because each items element is a STRUCT, item.* expands to one field per struct field:

SELECT order_id, item.* FROM orders, orders.items AS item

This produces the same result as the previous example.

Important

The alias.* notation is rejected when the array element type is a scalar (such as STRING or INTEGER) or a VECTOR, because there are no fields to expand. For instance, SELECT tag.* FROM orders, orders.tags AS tag is invalid.

In the unusual case where you need a one-field record wrapping a scalar element, you can build one explicitly with a subquery:

SELECT order_id, sq.*
FROM orders, (SELECT (tag) AS wrapped FROM orders.tags AS tag) AS sq

Unnesting in a Lateral Subquery

The struct-array example above can equivalently be written with a lateral subquery:

SELECT order_id, item.sku, item.qty FROM orders, (SELECT sku, qty FROM orders.items) AS item

The result is identical and the planner produces the same plan.

Element Ordinals with AT

Use AT to query the 1-based ordinal of each element alongside its value:

SELECT order_id, tag, at FROM orders, orders.tags AS tag AT at

order_id	tag	at
1	"priority"	1
1	"gift"	2
2	"backorder"	1

If you only care about the positions, omit the element alias:

SELECT order_id, at FROM orders, orders.items AT at

order_id	at
1	1
1	2
2	1
3	1
3	2
3	3

This does not bind any alias to the elements themselves; only the at ordinal is in scope.

Ordinals and Filtering

When you combine AT with a WHERE predicate to filter the elements, the elements keep the ordinals they had in the original array.

SELECT order_id, item.sku, item.qty, at
  FROM orders, orders.items AS item AT at
 WHERE item.qty >= 2

order_id	sku	qty	at
1	"A1"	2	1
2	"B1"	3	1
3	"C2"	2	2

The row of Order 3 (C1, 1) is filtered out, but (C2, 2) keeps its original ordinal 2, not 1.

Unnesting Two Arrays in Parallel using AT as a Subscript

When two arrays in the same row are positionally aligned, you can use the AT ordinal as a subscript for the second array to produce one row per element pair.

SELECT order_id, item.sku, item.qty, orders.prices[at] AS price
FROM orders, orders.items AS item AT at

order_id	sku	qty	price
1	"A1"	2	9.99
1	"A2"	1	19.99
2	"B1"	3	4.50
3	"C1"	1	29.99
3	"C2"	2	14.50
3	"C3"	1	5.00

Computing the Cross Product of Two Unnested Arrays

Two array sources in FROM produce a cross product within each row. The AT ordinals on each side are independent.

SELECT order_id, tag, item.sku, at_t, at_i
FROM orders,
     orders.tags AS tag AT at_t,
     orders.items AS item AT at_i

For each order/tag pair, at_i restarts at 1 in the result:

order_id	tag	sku	at_t	at_i
1	"priority"	"A1"	1	1
1	"priority"	"A2"	1	2
1	"gift"	"A1"	2	1
1	"gift"	"A2"	2	2
2	"backorder"	"B1"	1	1

Orders 3 and 4 produce no rows because at least one of their arrays is [] or NULL.

AT in a Correlated Subquery

Both the element alias and the AT alias can be referenced from a later FROM item, including a lateral subquery that unnests another array:

SELECT order_id, item.sku, sq.matched_price
  FROM orders,
       orders.items AS item AT at,
       (SELECT price AS matched_price
          FROM orders.prices AS price AT at2
         WHERE at2 = at AND price >= 15.0
       ) AS sq

For each item, the subquery looks up the price at the same ordinal and returns a row only if that price is at least 15.0:

order_id	sku	matched_price
1	"A2"	19.99
3	"C1"	29.99

See Also

• Subqueries - Subqueries and correlated subqueries

• Joins - Joining multiple tables

• Indexes - Indexing nested fields and unnested arrays

• SELECT - SELECT statement syntax