Array

Function	Description
ARRAY_CONTAINS	Returns true if the array contains the value.
ARRAY_GET	Returns the value at the given index in the array.
ARRAY_LENGTH	Returns the length of the array.
ARRAY_LOWER	Returns the lower bound (min value) in an array.
ARRAY_MEAN	Returns the mean of all values in an array.
ARRAY_REVERSE	Returns the array with the elements in reverse order.
ARRAY_SUM	Returns the sum of all values in an array.
ARRAY_TO_STRING	Takes all elements of the array and joins them into one string.
ARRAY_UNIQUE	Returns the array with the unique elements.
ARRAY_UPPER	Returns the upper bound (max value) in an array.
UNNEST	Unnests (explodes) an array column into multiple rows.

ARRAY_CONTAINS

Returns true if the array contains the value.

Example:

df = pl.DataFrame({"foo": [[1, 2], [4, 3]]})
df.sql("""
  SELECT ARRAY_CONTAINS(foo, 2) FROM self
""")
# shape: (2, 1)
# ┌───────┐
# │ foo   │
# │ ---   │
# │ bool  │
# ╞═══════╡
# │ true  │
# │ 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 ARRAY_GET(foo, 1), ARRAY_GET(bar, 2) FROM self
""")
# shape: (2, 2)
# ┌─────┬──────┐
# │ foo ┆ bar  │
# │ --- ┆ ---  │
# │ i64 ┆ i64  │
# ╞═════╪══════╡
# │ 2   ┆ null │
# │ 3   ┆ 10   │
# └─────┴──────┘

ARRAY_LENGTH

Returns the length of the array.

Example:

df = pl.DataFrame({"foo": [[1, 2], [4, 3, 2]]})
df.sql("""
  SELECT ARRAY_LENGTH(foo) FROM self
""")
# shape: (2, 1)
# ┌─────┐
# │ foo │
# │ --- │
# │ u32 │
# ╞═════╡
# │ 2   │
# │ 3   │
# └─────┘

ARRAY_LOWER

Returns the lower bound (min value) in an array.

Example:

df = pl.DataFrame(
  {
    "foo": [[1, 2], [4, 3, -2]],
    "bar": [[6, 7], [8, 9, 10]]
  }
)
df.sql("""
  SELECT ARRAY_LOWER(foo), ARRAY_LOWER(bar) FROM self
""")
# shape: (2, 2)
# ┌─────┬─────┐
# │ foo ┆ bar │
# │ --- ┆ --- │
# │ i64 ┆ i64 │
# ╞═════╪═════╡
# │ 1   ┆ 6   │
# │ -2  ┆ 8   │
# └─────┴─────┘

ARRAY_MEAN

Returns the mean of all values in an array.

Example:

df = pl.DataFrame(
  {
    "foo": [[1, 2], [4, 3, -1]],
    "bar": [[6, 7], [8, 9, 10]]
  }
)
df.sql("""
  SELECT
    ARRAY_MEAN(foo) AS foo_mean,
    ARRAY_MEAN(bar) AS bar_mean
  FROM self
""")

# shape: (2, 2)
# ┌──────────┬──────────┐
# │ foo_mean ┆ bar_mean │
# │ ---      ┆ ---      │
# │ f64      ┆ f64      │
# ╞══════════╪══════════╡
# │ 1.5      ┆ 6.5      │
# │ 2.0      ┆ 9.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
    ARRAY_REVERSE(foo) AS oof,
    ARRAY_REVERSE(bar) AS rab
  FROM self
""")

# shape: (2, 2)
# ┌───────────┬────────────┐
# │ oof       ┆ rab        │
# │ ---       ┆ ---        │
# │ list[i64] ┆ list[i64]  │
# ╞═══════════╪════════════╡
# │ [2, 1]    ┆ [7, 6]     │
# │ [2, 3, 4] ┆ [10, 9, 8] │
# └───────────┴────────────┘

ARRAY_SUM

Returns the sum of all values in an array.

Example:

df = pl.DataFrame(
  {
    "foo": [[1, -2], [-4, 3, -2]],
    "bar": [[-6, 7], [8, -9, 10]]
  }
)
df.sql("""
  SELECT
    ARRAY_SUM(foo) AS foo_sum,
    ARRAY_SUM(bar) AS bar_sum
  FROM self
""")

# shape: (2, 2)
# ┌─────────┬─────────┐
# │ foo_sum ┆ bar_sum │
# │ ---     ┆ ---     │
# │ i64     ┆ i64     │
# ╞═════════╪═════════╡
# │ -1      ┆ 1       │
# │ -3      ┆ 9       │
# └─────────┴─────────┘

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"]]})
df.sql("""
  SELECT ARRAY_TO_STRING(foo,',') AS foo_str FROM self
""")
# shape: (2, 1)
# ┌─────────┐
# │ foo_str │
# │ ---     │
# │ str     │
# ╞═════════╡
# │ a,b     │
# │ c,d,e   │
# └─────────┘

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": [[1, 2], [4, 3, -2]],
    "bar": [[6, 7], [8, 9, 10]]
  }
)
df.sql("""
  SELECT
    ARRAY_UPPER(foo) AS foo_max,
    ARRAY_UPPER(bar) AS bar_max
  FROM self
""")

# shape: (2, 2)
# ┌─────────┬─────────┐
# │ foo_max ┆ bar_max │
# │ ---     ┆ ---     │
# │ i64     ┆ i64     │
# ╞═════════╪═════════╡
# │ 2       ┆ 7       │
# │ 4       ┆ 10      │
# └─────────┴─────────┘

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  │
# └─────┴─────┘