JSON_EXTRACT_PATH_TEXT

Extracts value from a Json string by path_name. The value is returned as a String or NULL if either of the arguments is NULL. This function is equivalent to to_varchar(GET_PATH(PARSE_JSON(JSON), PATH_NAME)).

Analyze Syntax

func.json_extract_path_text(<expr>, <path_name>)

Analyze Example

func.json_extract_path_text('{"k1":[0,1,2], "k2":{"k3":3,"k4":4}}', 'k2.k4')
+------------------------------------------------------------------------------+
| func.json_extract_path_text('{"k1":[0,1,2], "k2":{"k3":3,"k4":4}}', 'k2.k4') |
+------------------------------------------------------------------------------+
| 4                                                                            |
+------------------------------------------------------------------------------+

SQL Syntax

JSON_EXTRACT_PATH_TEXT( <expr>, <path_name> )

Arguments

ArgumentsDescription
<expr>The Json String value
<path_name>The String value that consists of a concatenation of field names

Return Type

String

SQL Examples

SELECT json_extract_path_text('{"k1":[0,1,2], "k2":{"k3":3,"k4":4}}', 'k1[0]');
+-------------------------------------------------------------------------+
| json_extract_path_text('{"k1":[0,1,2], "k2":{"k3":3,"k4":4}}', 'k1[0]') |
+-------------------------------------------------------------------------+
| 0                                                                       |
+-------------------------------------------------------------------------+

SELECT json_extract_path_text('{"k1":[0,1,2], "k2":{"k3":3,"k4":4}}', 'k2:k3');
+-------------------------------------------------------------------------+
| json_extract_path_text('{"k1":[0,1,2], "k2":{"k3":3,"k4":4}}', 'k2:k3') |
+-------------------------------------------------------------------------+
| 3                                                                       |
+-------------------------------------------------------------------------+

SELECT json_extract_path_text('{"k1":[0,1,2], "k2":{"k3":3,"k4":4}}', 'k2.k4');
+-------------------------------------------------------------------------+
| json_extract_path_text('{"k1":[0,1,2], "k2":{"k3":3,"k4":4}}', 'k2.k4') |
+-------------------------------------------------------------------------+
| 4                                                                       |
+-------------------------------------------------------------------------+

SELECT json_extract_path_text('{"k1":[0,1,2], "k2":{"k3":3,"k4":4}}', 'k2.k5');
+-------------------------------------------------------------------------+
| json_extract_path_text('{"k1":[0,1,2], "k2":{"k3":3,"k4":4}}', 'k2.k5') |
+-------------------------------------------------------------------------+
| NULL                                                                    |
+-------------------------------------------------------------------------+
Last modified June 12, 2024 at 4:51 PM EST: adding JSON method updates (a7607f7)