Skip to main content

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)).

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

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 |
+-------------------------------------------------------------------------+
Explore Databend Cloud for FREE
Low-cost
Fast Analytics
Easy Data Ingestion
Elastic Scaling
Try it today