- Semi-structured Data Functions (Extraction)
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.
GET_PATH( <column_identifier> , '<path_name>' ) <column_identifier>:<path_name> :( <column_identifier> , '<path_name>' )
GET_PATH is equivalent to a chain of GET functions. It returns NULL if the path name does not correspond to any element.
.) and index operators (e.g.
- 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.
:operator is used, any integer or string sub-expressions can be included within
Extract the field
name from the
SELECT GET_PATH(v, 'attr.name') FROM vartab;
Same as first example, but using
: as the extraction operator:
SELECT v:attr.name FROM vartab;
Note that the extraction operator
:is left-associative, i.e. in the example above, using
v:attr:namehas the same effect as the left
:, producing a variant value (containing an object) from which the right extraction operator
:extracts the value of the
Same as first example, but using SQL-style double-quoted identifiers and
: as the path separator:
SELECT GET_PATH('v:"attr":"name"') FROM vartab;