Categories:

Semi-structured Data Functions (Extraction)

JSON_EXTRACT_PATH_TEXT

Parses the first argument as a JSON string and returns the value of the element pointed to by the path in the second argument. This is equivalent to TO_VARCHAR(GET_PATH(PARSE_JSON(JSON), PATH))

Syntax

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.

Returns

The data type of the returned value is VARCHAR.

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 (json_data VARCHAR);
INSERT INTO demo1 SELECT
   '{"level_1_key": "level_1_value"}';
INSERT INTO demo1 SELECT
   '{"level_1_key": {"level_2_key": "level_2_value"}}';
INSERT INTO demo1 SELECT
   '{"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;
+--------------------------------------+--------------------------------------+
| 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;
+----------------------+------------------------+
| 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;
+---------+------------------------+
| OLD_WAY | JSON_EXTRACT_PATH_TEXT |
|---------+------------------------|
| NULL    | NULL                   |
| NULL    | NULL                   |
| one     | one                    |
+---------+------------------------+