Share via

[ ] (bracket sign) operator

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns an array element or map value given an index or key.

Syntax

expr [ keyExpr ]

Note that the use of brackets here is as literal text and not indicating optional syntax.

Arguments

  • expr: An ARRAY or MAP expression.
  • keyExpr: If expr is an ARRAY an integral numeric. Otherwise an expression matching the type of the key of the MAP.

Returns

The result type is the element type of the ARRAY or the value type of the MAP.

The first element of an ARRAY is at index 0.

If the keyExpr is not a valid key for the MAP expr Azure Databricks returns null.

If the keyExpr is out of bound for the ARRAY expr Azure Databricks raises a INVALID_ARRAY_INDEXerror.

Note

In Databricks Runtime, if spark.sql.ansi.enabled is false, the operator returns NULL instead of an out of bounds error.

Examples

> SELECT a[2] FROM VALUES(array(10, 20, 30)) AS T(a);
  30

> SELECT m[1] FROM VALUES(map(1, 'Hello', 2, 'World')) AS T(m);
  Hello