`.` (dot sign) operator

Applies to: check marked yes Databricks SQL 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, Databricks will resolve the longest possible qualified name. If the resolved name is a MAP or STRUCT Databricks will interpret the remaining identifiers using the dot sign operator.

When used with a STRUCT, 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, 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

. (dot sign) operator