JSON_EXTRACT_PATH_TEXT( <column_identifier> , '<path_name>' )
Arguments
column_identifier
The name of the column with the data that you want to extract.
path_name
A string that contains the path to the element that you want to extract.
Usage Notes
The function 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 integers (for arrays) or single or
double-quoted string literals (for object fields).
For more details, see Querying Semi-structured Data.
To maintain syntactic consistency, the path notation also supports SQL-style double-quoted identifiers, and use of
:
as path separators.
Examples
Create a table and insert values:
CREATE TABLE demo1 (id INTEGER, json_data VARCHAR);
INSERT INTO demo1 SELECT
1, '{"level_1_key": "level_1_value"}';
INSERT INTO demo1 SELECT
2, '{"level_1_key": {"level_2_key": "level_2_value"}}';
INSERT INTO demo1 SELECT
3, '{"level_1_key": {"level_2_key": ["zero", "one", "two"]}}';
Use JSON_EXTRACT_PATH_TEXT to extract a value from a simple 1-level string:
SELECT
TO_VARCHAR(GET_PATH(PARSE_JSON(json_data), 'level_1_key'))
AS OLD_WAY,
JSON_EXTRACT_PATH_TEXT(json_data, 'level_1_key')
AS JSON_EXTRACT_PATH_TEXT
FROM demo1
ORDER BY id;
+--------------------------------------+--------------------------------------+
| OLD_WAY | JSON_EXTRACT_PATH_TEXT |
|--------------------------------------+--------------------------------------|
| level_1_value | level_1_value |
| {"level_2_key":"level_2_value"} | {"level_2_key":"level_2_value"} |
| {"level_2_key":["zero","one","two"]} | {"level_2_key":["zero","one","two"]} |
+--------------------------------------+--------------------------------------+
Use JSON_EXTRACT_PATH_TEXT to extract a value from a 2-level string using a 2-level path:
SELECT
TO_VARCHAR(GET_PATH(PARSE_JSON(json_data), 'level_1_key.level_2_key'))
AS OLD_WAY,
JSON_EXTRACT_PATH_TEXT(json_data, 'level_1_key.level_2_key')
AS JSON_EXTRACT_PATH_TEXT
FROM demo1
ORDER BY id;
+----------------------+------------------------+
| OLD_WAY | JSON_EXTRACT_PATH_TEXT |
|----------------------+------------------------|
| NULL | NULL |
| level_2_value | level_2_value |
| ["zero","one","two"] | ["zero","one","two"] |
+----------------------+------------------------+
This example contains an array:
SELECT
TO_VARCHAR(GET_PATH(PARSE_JSON(json_data), 'level_1_key.level_2_key[1]'))
AS OLD_WAY,
JSON_EXTRACT_PATH_TEXT(json_data, 'level_1_key.level_2_key[1]')
AS JSON_EXTRACT_PATH_TEXT
FROM demo1
ORDER BY id;
+---------+------------------------+
| OLD_WAY | JSON_EXTRACT_PATH_TEXT |
|---------+------------------------|
| NULL | NULL |
| NULL | NULL |
| one | one |
+---------+------------------------+