Share via

. (dot sign) operator

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

Returns a fieldIdentifier value in an STRUCT or a value by keyIdentifier in a MAP.

Syntax

structExpr . fieldIdentifier mapExpr . keyIdentifier

Arguments

  • structExpr: A STRUCT expression.
  • fieldIdentifier: An identifier for field within structExpr.
  • mapExpr: A MAP expression with keys of type STRING.
  • keyIdentifier: An identifier matching a key value in the mapExpr.

Returns

A type matching that of the fieldIdentifier or the type of the mapExpr values.

Resolution of names takes precedence over resolution of this operator. That is, given a series of identifiers separated by dots, Azure Databricks will resolve the longest possible qualified name. If the resolved name is a MAP or STRUCT Azure Databricks will interpret the remaining identifiers using the dot sign operator.

When used with a STRUCT, Azure Databricks verifies the existence of the fieldIdentifier in the struct when the statement is compiled.

When used with a MAP, and there is no key that matches keyIdentifier, Azure Databricks returns null. To return NULL instead use the try_element_at function.

Warning

In Databricks Runtime, if spark.sql.ansi.enabled is false, the result is NULL if no matching key is found for the mapExpr.

Examples

-- Names take precedence over the dot sign operator > CREATE SCHEMA a; > CREATE TABLE a.a(a struct<a INT, b STRING>); > INSERT INTO a.a VALUES (named_struct('a', 5, 'b', 'Spark')); -- Column `a` in table `a` > SELECT a.a FROM a.a; {"a":5,"b":"Spark"} -- Field `b` in column `a` > SELECT a.b FROM a.a; Spark -- Column `a` in table `a.a` > SELECT a.a.a FROM a.a; {"a":5,"b":"Spark"} -- Field `a` in column `a` in table `a.a` > SELECT a.a.a.a FROM a.a; 5 -- Resolving a map value: > SELECT map('three', 3).three; 3 -- Resolving a map value using the [ ] notation: > SELECT map('three', 3)['three'] 3 -- Resolving a map value using back quotes: > SELECT map('서울시', 'Seoul').`서울시`; Seoul -- Cannot resolve a non existing key > SELECT map('three', 3).four; NULL