GET_PATH , :¶

Syntax¶

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

<column_identifier>:<path_name>

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

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