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;
This is the same as the first example, but uses
: as the extraction operator:
SELECT v:attr.name FROM vartab;
The extraction operator
:is left-associative. In the example above, using
v:attr:namehas 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
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":"name"') FROM vartab;