.
(dot sign) operator
Applies to: Databricks SQL Databricks Runtime
Returns a fieldIdentifier
value in an STRUCT
or a value by keyIdentifier
in a MAP
.
Arguments
structExpr
: ASTRUCT
expression.fieldIdentifier
: An identifier for field withinstructExpr
.mapExpr
: AMAP
expression with keys of typeSTRING
.keyIdentifier
: An identifier matching a key value in themapExpr
.
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