rust/greptimedb
1
0
Fork 0
mirror of https://github.com/GreptimeTeam/greptimedb.git synced 2026-01-13 08:43:00 +00:00
Code Issues Packages Projects Releases Wiki Activity
Files
main
greptimedb/tests/cases/standalone/common/function/json/json_get.result
LFC c02754b44c feat: udf json_get_object (#7241) 
Signed-off-by: luofucong <luofc@foxmail.com>
2025-11-21 04:50:38 +00:00

388 lines
15 KiB
Plaintext
Raw Permalink Blame History

-- json_get functions --
SELECT json_get_int(parse_json('{"a": {"b": {"c": 1}}}'), 'a.b.c');
+------------------------------------------------------------------------+
| json_get_int(parse_json(Utf8("{"a": {"b": {"c": 1}}}")),Utf8("a.b.c")) |
+------------------------------------------------------------------------+
| 1 |
+------------------------------------------------------------------------+
SELECT json_get_float(parse_json('{"a": {"b": {"c": 1.234}}}'), 'a:b.c');
+------------------------------------------------------------------------------+
| json_get_float(parse_json(Utf8("{"a": {"b": {"c": 1.234}}}")),Utf8("a:b.c")) |
+------------------------------------------------------------------------------+
| 1.234 |
+------------------------------------------------------------------------------+
SELECT json_get_string(parse_json('{"a": {"b": {"c": "foo"}}}'), 'a.b:c');
+-------------------------------------------------------------------------------+
| json_get_string(parse_json(Utf8("{"a": {"b": {"c": "foo"}}}")),Utf8("a.b:c")) |
+-------------------------------------------------------------------------------+
| foo |
+-------------------------------------------------------------------------------+
SELECT json_get_bool(parse_json('{"a": {"b": {"c": true}}}'), 'a.b["c"]');
+-------------------------------------------------------------------------------+
| json_get_bool(parse_json(Utf8("{"a": {"b": {"c": true}}}")),Utf8("a.b["c"]")) |
+-------------------------------------------------------------------------------+
| true |
+-------------------------------------------------------------------------------+
SELECT json_get_int(parse_json('{"a": {"b": {"c": {"d": 1}}}}'), 'a.b');
+-----------------------------------------------------------------------------+
| json_get_int(parse_json(Utf8("{"a": {"b": {"c": {"d": 1}}}}")),Utf8("a.b")) |
+-----------------------------------------------------------------------------+
| |
+-----------------------------------------------------------------------------+
SELECT json_get_string(parse_json('{"a": {"b": {"c": {"d": 1}}}}'), 'a.b');
+--------------------------------------------------------------------------------+
| json_get_string(parse_json(Utf8("{"a": {"b": {"c": {"d": 1}}}}")),Utf8("a.b")) |
+--------------------------------------------------------------------------------+
| |
+--------------------------------------------------------------------------------+
SELECT json_to_string(json_get_object(parse_json('{"a": {"b": {"c": {"d": 42}}}}'), 'a.b.c'));
+---------------------------------------------------------------------------------------------------+
| json_to_string(json_get_object(parse_json(Utf8("{"a": {"b": {"c": {"d": 42}}}}")),Utf8("a.b.c"))) |
+---------------------------------------------------------------------------------------------------+
| {"d":42} |
+---------------------------------------------------------------------------------------------------+
SELECT json_get_int(json_get_object(parse_json('{"a": {"b": {"c": {"d": 42}}}}'), 'a.b.c'), 'd');
+-----------------------------------------------------------------------------------------------------------+
| json_get_int(json_get_object(parse_json(Utf8("{"a": {"b": {"c": {"d": 42}}}}")),Utf8("a.b.c")),Utf8("d")) |
+-----------------------------------------------------------------------------------------------------------+
| 42 |
+-----------------------------------------------------------------------------------------------------------+
SELECT json_get_object(parse_json('{"a": {"b": {"c": {"d": 42}}}}'), 'a.e');
+---------------------------------------------------------------------------------+
| json_get_object(parse_json(Utf8("{"a": {"b": {"c": {"d": 42}}}}")),Utf8("a.e")) |
+---------------------------------------------------------------------------------+
| |
+---------------------------------------------------------------------------------+
-- test functions with table rows --
CREATE TABLE jsons(j JSON, ts timestamp time index);
Affected Rows: 0
INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": 1}}}'), 1);
Affected Rows: 1
INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": 1.234}}}'), 2);
Affected Rows: 1
INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": "foo"}}}'), 3);
Affected Rows: 1
INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": true}}}'), 4);
Affected Rows: 1
SELECT json_get_int(j, 'a.b.c') FROM jsons;
+-------------------------------------+
| json_get_int(jsons.j,Utf8("a.b.c")) |
+-------------------------------------+
| 1 |
| |
| |
| 1 |
+-------------------------------------+
SELECT json_get_float(j, 'a["b"].c') FROM jsons;
+------------------------------------------+
| json_get_float(jsons.j,Utf8("a["b"].c")) |
+------------------------------------------+
| 1.0 |
| 1.234 |
| |
| 1.0 |
+------------------------------------------+
SELECT json_get_string(j, 'a.b.c?(@ == 1)') FROM jsons;
+-------------------------------------------------+
| json_get_string(jsons.j,Utf8("a.b.c?(@ == 1)")) |
+-------------------------------------------------+
| 1 |
| |
| |
| |
+-------------------------------------------------+
SELECT json_get_bool(j, 'a.b.c') FROM jsons;
+--------------------------------------+
| json_get_bool(jsons.j,Utf8("a.b.c")) |
+--------------------------------------+
| |
| |
| |
| true |
+--------------------------------------+
SELECT json_get_int(j, 'a.b["c"]') FROM jsons;
+----------------------------------------+
| json_get_int(jsons.j,Utf8("a.b["c"]")) |
+----------------------------------------+
| 1 |
| |
| |
| 1 |
+----------------------------------------+
SELECT json_to_string(json_get_object(j, 'a.b')) FROM jsons;
+------------------------------------------------------+
| json_to_string(json_get_object(jsons.j,Utf8("a.b"))) |
+------------------------------------------------------+
| {"c":1} |
| {"c":1.234} |
| {"c":"foo"} |
| {"c":true} |
+------------------------------------------------------+
SELECT json_get_string(json_get_object(j, 'a.b'), 'c') FROM jsons;
+-----------------------------------------------------------------+
| json_get_string(json_get_object(jsons.j,Utf8("a.b")),Utf8("c")) |
+-----------------------------------------------------------------+
| 1 |
| 1.234 |
| foo |
| true |
+-----------------------------------------------------------------+
SELECT json_get_object(j, 'a.x') FROM jsons;
+--------------------------------------+
| json_get_object(jsons.j,Utf8("a.x")) |
+--------------------------------------+
| |
| |
| |
| |
+--------------------------------------+
DROP TABLE jsons;
Affected Rows: 0
-- test functions with arrays --
CREATE TABLE jsons(j JSON, ts timestamp time index);
Affected Rows: 0
INSERT INTO jsons VALUES(parse_json('["a", "bcde", "", "Long time ago, there is a little pig flying in the sky"]'), 1);
Affected Rows: 1
INSERT INTO jsons VALUES(parse_json('[true, false, false, false]'), 2);
Affected Rows: 1
INSERT INTO jsons VALUES(parse_json('[1, 0, -2147483649, 2147483648]'), 3);
Affected Rows: 1
INSERT INTO jsons VALUES(parse_json('[1.2, 3.1415926535897932384626, -3e123, 1e100]'), 4);
Affected Rows: 1
INSERT INTO jsons VALUES(parse_json('[{"a": {"i": 1}}, {"a": {"i": 2}}, {"a": {"i": 3}}]'), 5);
Affected Rows: 1
SELECT json_get_int(j, '[0]') FROM jsons;
+-----------------------------------+
| json_get_int(jsons.j,Utf8("[0]")) |
+-----------------------------------+
| |
| 1 |
| 1 |
| |
| |
+-----------------------------------+
SELECT json_get_float(j, '[1]') FROM jsons;
+-------------------------------------+
| json_get_float(jsons.j,Utf8("[1]")) |
+-------------------------------------+
| |
| 0.0 |
| 0.0 |
| 3.141592653589793 |
| |
+-------------------------------------+
SELECT json_get_bool(j, '[2]') FROM jsons;
+------------------------------------+
| json_get_bool(jsons.j,Utf8("[2]")) |
+------------------------------------+
| |
| false |
| |
| |
| |
+------------------------------------+
SELECT json_get_string(j, '[3]') FROM jsons;
+--------------------------------------------------------+
| json_get_string(jsons.j,Utf8("[3]")) |
+--------------------------------------------------------+
| Long time ago, there is a little pig flying in the sky |
| false |
| 2147483648 |
| 1e100 |
| |
+--------------------------------------------------------+
SELECT json_to_string(json_get_object(j, '[0]')) FROM jsons;
+------------------------------------------------------+
| json_to_string(json_get_object(jsons.j,Utf8("[0]"))) |
+------------------------------------------------------+
| |
| |
| |
| |
| {"a":{"i":1}} |
+------------------------------------------------------+
SELECT json_get_int(json_get_object(j, '[0]'), 'a.i') FROM jsons;
+----------------------------------------------------------------+
| json_get_int(json_get_object(jsons.j,Utf8("[0]")),Utf8("a.i")) |
+----------------------------------------------------------------+
| |
| |
| |
| |
| 1 |
+----------------------------------------------------------------+
SELECT json_get_int(json_get_object(j, '[9]'), 'a.i') FROM jsons;
+----------------------------------------------------------------+
| json_get_int(json_get_object(jsons.j,Utf8("[9]")),Utf8("a.i")) |
+----------------------------------------------------------------+
| |
| |
| |
| |
| |
+----------------------------------------------------------------+
DROP TABLE jsons;
Affected Rows: 0
-- test functions in WHERE clause --
CREATE TABLE jsons(j JSON, ts timestamp time index);
Affected Rows: 0
INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": 1}}}'), 1);
Affected Rows: 1
INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": 1.234}}}'), 2);
Affected Rows: 1
INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": "foo"}}}'), 3);
Affected Rows: 1
INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": true}}}'), 4);
Affected Rows: 1
SELECT json_to_string(j) FROM jsons WHERE json_get_int(j, 'a.b.c') = 1;
+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":1}}} |
| {"a":{"b":{"c":true}}} |
+-------------------------+
SELECT json_to_string(j) FROM jsons WHERE json_get_float(j, 'a.b.c') = 1.234;
+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":1.234}}} |
+-------------------------+
SELECT json_to_string(j) FROM jsons WHERE json_get_string(j, 'a.b.c') = 'foo';
+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":"foo"}}} |
+-------------------------+
SELECT json_to_string(j) FROM jsons WHERE json_get_bool(j, 'a.b.c') = true;
+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":true}}} |
+-------------------------+
SELECT json_to_string(j) FROM jsons WHERE CAST(json_get_int(j, 'a.b.c') AS BOOLEAN);
+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":1}}} |
| {"a":{"b":{"c":true}}} |
+-------------------------+
SELECT json_to_string(j) FROM jsons WHERE json_get_string(json_get_object(j, 'a.b'), 'c') == 'foo';
+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":"foo"}}} |
+-------------------------+
SELECT json_to_string(j) FROM jsons WHERE json_to_string(json_get_object(j, 'a.b')) == '{"c":1}';
+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":1}}} |
+-------------------------+
SELECT json_to_string(j) FROM jsons WHERE json_get_string(json_get_object(j, 'a.x'), 'c') == 'foo';
++
++
DROP TABLE jsons;
Affected Rows: 0
Reference in New Issue View Git Blame Copy Permalink