Array#
Function |
Description |
|---|---|
Aggregate a column/expression values as an array. |
|
Returns true if the array contains the value. |
|
Returns the value at the given index in the array. |
|
Returns the inner product of two fixed-size arrays. Alias: |
|
Returns the length of the array (optional: the dimension, which must be 1). |
|
Returns the lower bound (min value) in an array. |
|
Returns the mean of all values in an array. |
|
Returns the array with the elements in reverse order. |
|
Returns the sum of all values in an array. |
|
Takes all elements of the array and joins them into one string. |
|
Returns the array with the unique elements. |
|
Returns the upper bound (max value) in an array. |
|
Returns the number of elements in the array. |
|
Returns an array of values from start to stop, stop included. |
|
Returns an array of values from start to stop, stop not included. |
|
Unnests (explodes) an array column into multiple rows. |
ARRAY_AGG#
Aggregate a column/expression as an array (equivalent to implode).
Supports optional inline ORDER BY and LIMIT clauses.
Example:
df = pl.DataFrame({"foo": [1, 2, 3], "bar": [4, 5, 6]})
df.sql("""
SELECT
ARRAY_AGG(foo ORDER BY foo DESC) AS arr_foo,
ARRAY_AGG(bar LIMIT 2) AS arr_bar
FROM self
""")
# shape: (1, 2)
# โโโโโโโโโโโโโฌโโโโโโโโโโโโ
# โ arr_foo โ arr_bar โ
# โ --- โ --- โ
# โ list[i64] โ list[i64] โ
# โโโโโโโโโโโโโชโโโโโโโโโโโโก
# โ [3, 2, 1] โ [4, 5] โ
# โโโโโโโโโโโโโดโโโโโโโโโโโโ
ARRAY_CONTAINS#
Returns true if the array contains the value.
Example:
df = pl.DataFrame({"foo": [[1, 2], [4, 3]]})
df.sql("""
SELECT foo, ARRAY_CONTAINS(foo, 2) AS has_two FROM self
""")
# shape: (2, 2)
# โโโโโโโโโโโโโฌโโโโโโโโโโ
# โ foo โ has_two โ
# โ --- โ --- โ
# โ list[i64] โ bool โ
# โโโโโโโโโโโโโชโโโโโโโโโโก
# โ [1, 2] โ true โ
# โ [4, 3] โ false โ
# โโโโโโโโโโโโโดโโโโโโโโโโ
ARRAY_GET#
Returns the value at the given index in the array.
Example:
df = pl.DataFrame(
{
"foo": [[1, 2], [4, 3, 2]],
"bar": [[6, 7], [8, 9, 10]]
}
)
df.sql("""
SELECT
foo, bar,
ARRAY_GET(foo, 1) AS foo_at_1,
ARRAY_GET(bar, 3) AS bar_at_2
FROM self
""")
# shape: (2, 4)
# โโโโโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโโโโโฌโโโโโโโโโโโ
# โ foo โ bar โ foo_at_1 โ bar_at_2 โ
# โ --- โ --- โ --- โ --- โ
# โ list[i64] โ list[i64] โ i64 โ i64 โ
# โโโโโโโโโโโโโชโโโโโโโโโโโโโชโโโโโโโโโโโชโโโโโโโโโโโก
# โ [1, 2] โ [6, 7] โ 1 โ null โ
# โ [4, 3, 2] โ [8, 9, 10] โ 4 โ 10 โ
# โโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโดโโโโโโโโโโโ
ARRAY_INNER_PRODUCT#
Returns the inner product of two fixed-size arrays of equal width. Their inner
data types are cast to a common supertype, which must be an integer, Float32,
or Float64. ARRAY_DOT_PRODUCT is an alias.
Inputs may be fixed-size Array expressions or direct, known-width SQL array literals. Direct literals are interpreted as scalar fixed-size Arrays and broadcast against the other input. Other expressions with variable-size List dtype are not implicitly converted.
Coordinates where either array element is null do not contribute to the result. A non-null row with no valid coordinate pairs returns zero. If either input Array is null for a row, the result for that row is null.
Example:
dtype = pl.Array(pl.Float64, 2)
df = pl.DataFrame(
{
"lhs": [[1.0, 2.0], [3.0, 4.0]],
"rhs": [[10.0, 20.0], [30.0, 40.0]],
},
schema={"lhs": dtype, "rhs": dtype},
)
df.sql("""
SELECT ARRAY_INNER_PRODUCT(lhs, rhs) AS dot FROM self
""")
# shape: (2, 1)
# โโโโโโโโโ
# โ dot โ
# โ --- โ
# โ f64 โ
# โโโโโโโโโก
# โ 50.0 โ
# โ 250.0 โ
# โโโโโโโโโ
df.sql("""
SELECT ARRAY_INNER_PRODUCT(lhs, [10.0, 20.0]) AS dot FROM self
""")
# shape: (2, 1)
# โโโโโโโโโ
# โ dot โ
# โ --- โ
# โ f64 โ
# โโโโโโโโโก
# โ 50.0 โ
# โ 110.0 โ
# โโโโโโโโโ
ARRAY_LENGTH#
Returns the length of the array. An optional second argument names the dimension, which must be 1.
Example:
df = pl.DataFrame({"foo": [[1, 2], [4, 3, 2]]})
df.sql("""
SELECT foo, ARRAY_LENGTH(foo) AS n_elems FROM self
""")
# shape: (2, 2)
# โโโโโโโโโโโโโฌโโโโโโโโโโ
# โ foo โ n_elems โ
# โ --- โ --- โ
# โ list[i64] โ u32 โ
# โโโโโโโโโโโโโชโโโโโโโโโโก
# โ [1, 2] โ 2 โ
# โ [4, 3, 2] โ 3 โ
# โโโโโโโโโโโโโดโโโโโโโโโโ
ARRAY_LOWER#
Returns the lower bound (min value) in an array.
Example:
df = pl.DataFrame({"foo": [[1, 2], [4, -2, 8]]})
df.sql("""
SELECT foo, ARRAY_LOWER(foo) AS min_elem FROM self
""")
# shape: (2, 2)
# โโโโโโโโโโโโโโฌโโโโโโโโโโโ
# โ foo โ min_elem โ
# โ --- โ --- โ
# โ list[i64] โ i64 โ
# โโโโโโโโโโโโโโชโโโโโโโโโโโก
# โ [1, 2] โ 1 โ
# โ [4, -2, 8] โ -2 โ
# โโโโโโโโโโโโโโดโโโโโโโโโโโ
ARRAY_MEAN#
Returns the mean of all values in an array.
Example:
df = pl.DataFrame({"foo": [[1, 2], [4, 3, -1]]})
df.sql("""
SELECT foo, ARRAY_MEAN(foo) AS foo_mean FROM self
""")
# shape: (2, 2)
# โโโโโโโโโโโโโโฌโโโโโโโโโโโ
# โ foo โ foo_mean โ
# โ --- โ --- โ
# โ list[i64] โ f64 โ
# โโโโโโโโโโโโโโชโโโโโโโโโโโก
# โ [1, 2] โ 1.5 โ
# โ [4, 3, -1] โ 2.0 โ
# โโโโโโโโโโโโโโดโโโโโโโโโโโ
ARRAY_REVERSE#
Returns the array with the elements in reverse order.
Example:
df = pl.DataFrame(
{
"foo": [[1, 2], [4, 3, 2]],
"bar": [[6, 7], [8, 9, 10]]
}
)
df.sql("""
SELECT
foo,
ARRAY_REVERSE(foo) AS oof,
ARRAY_REVERSE(bar) AS rab
FROM self
""")
# shape: (2, 3)
# โโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโโโโ
# โ foo โ oof โ rab โ
# โ --- โ --- โ --- โ
# โ list[i64] โ list[i64] โ list[i64] โ
# โโโโโโโโโโโโโชโโโโโโโโโโโโชโโโโโโโโโโโโโก
# โ [1, 2] โ [2, 1] โ [7, 6] โ
# โ [4, 3, 2] โ [2, 3, 4] โ [10, 9, 8] โ
# โโโโโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโโโโโ
ARRAY_SUM#
Returns the sum of all values in an array.
Example:
df = pl.DataFrame({"foo": [[1, -2], [10, 3, -2]]})
df.sql("""
SELECT
foo,
ARRAY_SUM(foo) AS foo_sum
FROM self
""")
# shape: (2, 2)
# โโโโโโโโโโโโโโโฌโโโโโโโโโโ
# โ foo โ foo_sum โ
# โ --- โ --- โ
# โ list[i64] โ i64 โ
# โโโโโโโโโโโโโโโชโโโโโโโโโโก
# โ [1, -2] โ -1 โ
# โ [10, 3, -2] โ 11 โ
# โโโโโโโโโโโโโโโดโโโโโโโโโโ
ARRAY_TO_STRING#
Takes all elements of the array and joins them into one string.
Example:
df = pl.DataFrame(
{
"foo": [["a", "b"], ["c", "d", "e"]],
"bar": [[8, None, 8], [3, 2, 1, 0]],
}
)
df.sql("""
SELECT
ARRAY_TO_STRING(foo,':') AS s_foo,
ARRAY_TO_STRING(bar,':') AS s_bar
FROM self
""")
# shape: (2, 2)
# โโโโโโโโโฌโโโโโโโโโโ
# โ s_foo โ s_bar โ
# โ --- โ --- โ
# โ str โ str โ
# โโโโโโโโโชโโโโโโโโโโก
# โ a:b โ 8:8 โ
# โ c:d:e โ 3:2:1:0 โ
# โโโโโโโโโดโโโโโโโโโโ
ARRAY_UNIQUE#
Returns the array with the unique elements.
Example:
df = pl.DataFrame({"foo": [["a", "b"], ["b", "b", "e"]]})
df.sql("""
SELECT ARRAY_UNIQUE(foo) AS foo_unique FROM self
""")
# shape: (2, 1)
# โโโโโโโโโโโโโโ
# โ foo_unique โ
# โ --- โ
# โ list[str] โ
# โโโโโโโโโโโโโโก
# โ ["a", "b"] โ
# โ ["e", "b"] โ
# โโโโโโโโโโโโโโ
ARRAY_UPPER#
Returns the upper bound (max value) in an array.
Example:
df = pl.DataFrame({"foo": [[5, 0], [4, 8, -2]]})
df.sql("""
SELECT foo, ARRAY_UPPER(foo) AS max_elem FROM self
""")
# shape: (2, 2)
# โโโโโโโโโโโโโโฌโโโโโโโโโโโ
# โ foo โ max_elem โ
# โ --- โ --- โ
# โ list[i64] โ i64 โ
# โโโโโโโโโโโโโโชโโโโโโโโโโโก
# โ [5, 0] โ 5 โ
# โ [4, 8, -2] โ 8 โ
# โโโโโโโโโโโโโโดโโโโโโโโโโโ
CARDINALITY#
Returns the number of elements in the array.
Example:
df = pl.DataFrame({"foo": [[1, 2], [4, 3, 2]]})
df.sql("""
SELECT foo, CARDINALITY(foo) AS n_elems FROM self
""")
# shape: (2, 2)
# โโโโโโโโโโโโโฌโโโโโโโโโโ
# โ foo โ n_elems โ
# โ --- โ --- โ
# โ list[i64] โ u32 โ
# โโโโโโโโโโโโโชโโโโโโโโโโก
# โ [1, 2] โ 2 โ
# โ [4, 3, 2] โ 3 โ
# โโโโโโโโโโโโโดโโโโโโโโโโ
GENERATE_SERIES#
Returns an array of values from start to stop, stop included. Takes (stop),
(start, stop) or (start, stop, step). Integers return Int64 values, with a
start of 0 and a step of 1 by default. Dates and timestamps take an INTERVAL step and
return timestamps. Also available as a table function, see
GENERATE_SERIES.
Example:
df = pl.DataFrame({"lo": [1, 2], "hi": [3, 5]})
df.sql("""
SELECT lo, hi, GENERATE_SERIES(lo, hi) AS gs FROM self
""")
# shape: (2, 3)
# โโโโโโโฌโโโโโโฌโโโโโโโโโโโโโโ
# โ lo โ hi โ gs โ
# โ --- โ --- โ --- โ
# โ i64 โ i64 โ list[i64] โ
# โโโโโโโชโโโโโโชโโโโโโโโโโโโโโก
# โ 1 โ 3 โ [1, 2, 3] โ
# โ 2 โ 5 โ [2, 3, โฆ 5] โ
# โโโโโโโดโโโโโโดโโโโโโโโโโโโโโ
pl.sql("""
SELECT UNNEST(
GENERATE_SERIES(DATE '2026-01-01', DATE '2026-01-03', INTERVAL '1 day')
) AS d
""").collect()
# shape: (3, 1)
# โโโโโโโโโโโโโโโโโโโโโโโ
# โ d โ
# โ --- โ
# โ datetime[ฮผs] โ
# โโโโโโโโโโโโโโโโโโโโโโโก
# โ 2026-01-01 00:00:00 โ
# โ 2026-01-02 00:00:00 โ
# โ 2026-01-03 00:00:00 โ
# โโโโโโโโโโโโโโโโโโโโโโโ
RANGE#
Like GENERATE_SERIES, but stop is not included. Also available as a table function, see RANGE.
Example:
df = pl.DataFrame({"lo": [1, 2], "hi": [3, 5]})
df.sql("""
SELECT lo, hi, RANGE(lo, hi) AS r FROM self
""")
# shape: (2, 3)
# โโโโโโโฌโโโโโโฌโโโโโโโโโโโโ
# โ lo โ hi โ r โ
# โ --- โ --- โ --- โ
# โ i64 โ i64 โ list[i64] โ
# โโโโโโโชโโโโโโชโโโโโโโโโโโโก
# โ 1 โ 3 โ [1, 2] โ
# โ 2 โ 5 โ [2, 3, 4] โ
# โโโโโโโดโโโโโโดโโโโโโโโโโโโ
UNNEST#
Unnest/explode an array column into multiple rows.
Example:
df = pl.DataFrame(
{
"foo": [["a", "b", "c"], ["d", "e"]],
"bar": [[6, 7, 8], [9, 10]]
}
)
df.sql("""
SELECT
UNNEST(foo) AS f,
UNNEST(bar) AS b
FROM self
""")
# shape: (5, 2)
# โโโโโโโฌโโโโโโ
# โ f โ b โ
# โ --- โ --- โ
# โ str โ i64 โ
# โโโโโโโชโโโโโโก
# โ a โ 6 โ
# โ b โ 7 โ
# โ c โ 8 โ
# โ d โ 9 โ
# โ e โ 10 โ
# โโโโโโโดโโโโโโ
