JSON Parsing Function
Last Updated：2021-04-13
Currently, Palo supports three JSON parsing functions:
1.get_json_int
2.get_json_string
3.get_json_double
GET_JSON_INT
Description
get_json_int(VARCHAR json_str, VARCHAR json_path)- Function: parse and get the integer content of the specified path in JSON string. The first parameter is json string, and the second parameter is the path in json.
json_pathmust start with$symbol and use.as path separator. If the path contains., it can be enclosed with double quotation marks. Use[]to indicate the array subscript, started with 0. The content ofpathcannot contain",[,]. If the format ofjson_stringis not correct, or the format ofjson_pathis not correct, or no match item can be found, then return NULL. - Return type: int type or NULL.
Example
mysql> select get_json_int('{"col1":100, "col2":"string", "col3":1.5}', "$.col1");
+---------------------------------------------------------------------+
| get_json_int('{"col1":100, "col2":"string", "col3":1.5}', '$.col1') |
+---------------------------------------------------------------------+
| 100 |
+---------------------------------------------------------------------+Keywords
GET_JSON_INT, JSONGET_JSON_STRING
Description
get_json_string(VARCHAR json_str, VARCHAR json_path)- Function: parse and obtain the string content of the specified path in JSON string. The
json_pathmust start with$symbol and use.as path separator. If the path contains., it can be enclosed with double quotation marks. Use[]to indicate the array subscript, started with 0. The content ofpathcannot contain",[,]. If the format ofjson_stringis not correct, or the format ofjson_pathis not correct, or no match item can be found, then return NULL. - Return type: string type or NULL.
Example
mysql> select get_json_string('{"col1":100, "col2":"string", "col3":1.5}', "$.col2");
+------------------------------------------------------------------------+
| get_json_string('{"col1":100, "col2":"string", "col3":1.5}', '$.col2') |
+------------------------------------------------------------------------+
| string |
+------------------------------------------------------------------------+Keywords
GET_JSON_STRING, JSONGET_JSON_DOUBLE
Description
get_json_double(VARCHAR json_str, VARCHAR json_path)- Function: parse and obtain the floating point content of the specified path in JSON string. The
json_pathmust start with$as path separator. If the path contains.as path separator. If the path contains., it can be enclosed with double quotation marks. Use[]to indicate the array subscript, started with 0. The content ofpathcannot contain",[,]. If the format ofjson_stringis not correct, or the format ofjson_pathis not correct, or no match item can be found, then return NULL. - Return type: double type or NULL.
Example
mysql> select get_json_double('{"col1":100, "col2":"string", "col3":1.5}', "$.col3");
+------------------------------------------------------------------------+
| get_json_double('{"col1":100, "col2":"string", "col3":1.5}', '$.col3') |
+------------------------------------------------------------------------+
| 1.5 |
+------------------------------------------------------------------------+
mysql> select get_json_double('{"col1":100, "col2":"string", "col3":1.5}', "$.col5");
+------------------------------------------------------------------------+
| get_json_double('{"col1":100, "col2":"string", "col3":1.5}', '$.col5') |
+------------------------------------------------------------------------+
| NULL |
+------------------------------------------------------------------------+Keywords
GET_JSON_DOUBLE, JSON