GET_PATH , :¶

Syntax¶

GET_PATH( <column_identifier> , '<path_name>' )

<column_identifier>:<path_name>

:( <column_identifier> , '<path_name>' )

Arguments¶

column_identifier

An expression that evaluates to an OBJECT, ARRAY, or VARIANT column .

path_name

An expression that evaluates to a VARCHAR. This specifies the path to the field or element that you want to extract.

For structured types, you must specify a string constant.

Returns¶

• The returned value is the specified element of the ARRAY, or the value that corresponds to the specified key of a key-value pair in the OBJECT.

• If the input object is a semi-structured OBJECT, ARRAY, or VARIANT, the function returns a VARIANT. The data type is VARIANT because:

  • In an ARRAY, each element is of type VARIANT.

  • In an OBJECT, the value in each key-value pair is of type VARIANT.

• If the input object is a structured OBJECT, structured ARRAY, or MAP, the function returns a value of the type specified for the object.

  For example, if the type of the input object is ARRAY(NUMBER), the function returns a NUMBER.

Usage notes¶

• GET_PATH is equivalent to a chain of GET functions. It returns NULL if the path name does not correspond to any element.

• The path name syntax is standard JavaScript notation; it consists of a concatenation of field names (identifiers) preceded by periods (e.g. .) and index operators (e.g. [<index>]):

  • The first field name does not require the leading period to be specified.

  • The index values in the index operators can be non-negative decimal numbers (for arrays) or single or double-quoted string literals (for object fields).

  For more details, see Querying Semi-structured Data.

• GET_PATH also supports a syntactic shortcut using the : character as the extraction operator separating the column name (which can contain periods) from the path specifier.

  To maintain syntactic consistency, the path notation also supports SQL-style double-quoted identifiers, and use of : as path separators.

  When the : operator is used, any integer or string sub-expressions can be included within [].

Examples¶

Extract the field name from the vartab table:

SELECT GET_PATH(v, 'attr[0].name') FROM vartab;

Copy

This is the same as the first example, but uses : as the extraction operator:

SELECT v:attr[0].name FROM vartab;

Copy

The extraction operator : is left-associative. In the example above, using v:attr[0]:name has the same effect as the left-hand :, producing a variant value (containing an object) from which the right-hand extraction operator : extracts the value of the name field.

This is the same as the first example, but uses SQL-style double-quoted identifiers and : as the path separator:

SELECT GET_PATH('v:"attr"[0]:"name"') FROM vartab;

Copy