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
Arguments | Description |
---|---|
<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)