- 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, usingv: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 thename
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;