Categories:

Semi-structured Data Functions (Extraction)

GET_PATH , :

Extracts a value from semi-structured data using a path name.

GET_PATH is a variation of GET; it takes a VARIANT, OBJECT, or ARRAY column name as the first argument, and extracts the VARIANT value of the field or the element according to the path name provided as the second argument.

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;

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

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

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;
Language: English