This page lists all json type related functions in GreptimeDB.
warning
The JSON feature is currently experimental and may change in future releases.
ConversionConversion between JSON and other types.
parse_json(string) to parse a JSON string into a JSON value. Illegal JSON strings will return an error.json_to_string(json) to convert a JSON value to a string.SELECT json_to_string(parse_json('{"a": 1, "b": 2}'));
+
| json_to_string(parse_json(Utf8("{\"a\": 1, \"b\": 2}"))) |
+
| {"a":1,"b":2} |
+
Extracts values with specific types from JSON values through specific paths.
json_get_bool(json, path) to extract a boolean value from a JSON value by the path.json_get_int(json, path) to extract an integer value from a JSON value by the path, while boolean values will be converted to integers.json_get_float(json, path) to extract a float value from a JSON value by the path, while integer and boolean values will be converted to floats.json_get_string(json, path) to extract a string value from a JSON value by the path. All valid JSON values will be converted to strings, including null values, objects and arrays.path is a string that select and extract elements from a json value. The following operators in the path are supported:
$ The root element $ @ The current element in the filter expression $.event?(@ == 1) .* Selecting all elements in an Object $.* .<name> Selecting element that match the name in an Object $.event :<name> Alias of .<name> $:event ["<name>"] Alias of .<name> $["event"] [*] Selecting all elements in an Array $[*] [<pos>, ..] Selecting 0-based n-th elements in an Array $[1, 2] [last - <pos>, ..] Selecting n-th element before the last element in an Array $[0, last - 1] [<pos1> to <pos2>, ..] Selecting all elements of a range in an Array $[1 to last - 2] ?(<expr>) Selecting all elements that matched the filter expression $?(@.price < 10)
If the path is invalid, the function will return a NULL value.
SELECT json_get_int(parse_json('{"a": {"c": 3}, "b": 2}'), 'a.c');
+
| json_get_int(parse_json(Utf8("{"a": {"c": 3}, "b": 2}")),Utf8("a.c")) |
+
| 3 |
+
Check the type of a JSON value.
json_is_null(json) to check whether a JSON value is a null value.json_is_bool(json) to check whether a JSON value is a boolean value.json_is_int(json) to check whether a JSON value is an integer value.json_is_float(json) to check whether a JSON value is a float value.json_is_string(json) to check whether a JSON value is a string value.json_is_object(json) to check whether a JSON value is an object value.json_is_array(json) to check whether a JSON value is an array value.SELECT json_is_array(parse_json('[1, 2, 3]'));
+
| json_is_array(parse_json(Utf8("[1, 2, 3]"))) |
+
| 1 |
+
SELECT json_is_object(parse_json('1'));
+
| json_is_object(parse_json(Utf8("1"))) |
+
| 0 |
+
json_path_exists(json, path) to check whether a path exists in a JSON value.If the path is invalid, the function will return an error.
If the path or the JSON value is NULL, the function will return a NULL value.
SELECT json_path_exists(parse_json('{"a": 1, "b": 2}'), 'a');
+
| json_path_exists(parse_json(Utf8("{"a": 1, "b": 2}")),Utf8("a")) |
+
| 1 |
+
SELECT json_path_exists(parse_json('{"a": 1, "b": 2}'), 'c.d');
+
| json_path_exists(parse_json(Utf8("{"a": 1, "b": 2}")),Utf8("c.d")) |
+
| 0 |
+
SELECT json_path_exists(parse_json('{"a": 1, "b": 2}'), NULL);
+
| json_path_exists(parse_json(Utf8("{"a": 1, "b": 2}")),NULL) |
+
| NULL |
+
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4