MDEV-9143 JSON_xxx functions.

        The rest of mysql/json functions implemented.
        CAST AS JSON implemented.

4 files changed, 160 insertions, 24 deletions

diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result
index 105123e2f1e..5b3ab44c19f 100644
--- a/mysql-test/r/dyncol.result
+++ b/mysql-test/r/dyncol.result
@@ -1731,36 +1731,36 @@ column_json(column_create(1, "val", 2, column_create(3, "val2")))
 # Time encoding
 #
 select hex(column_create("t", "800:46:06.23434" AS time)) as hex,
-column_json(column_create("t", "800:46:06.23434" AS time)) as json;
-hex	json
+column_json(column_create("t", "800:46:06.23434" AS time)) as js;
+hex	js
 04010001000000070074649363B82003	{"t":"800:46:06.234340"}
 select hex(column_create(1, "800:46:06.23434" AS time)) as hex,
-column_json(column_create(1, "800:46:06.23434" AS time)) as json;
-hex	json
+column_json(column_create(1, "800:46:06.23434" AS time)) as js;
+hex	js
 000100010007649363B82003	{"1":"800:46:06.234340"}
 select hex(column_create("t", "800:46:06" AS time)) as hex,
-column_json(column_create("t", "800:46:06" AS time)) as json;
-hex	json
+column_json(column_create("t", "800:46:06" AS time)) as js;
+hex	js
 04010001000000070074860B32	{"t":"800:46:06"}
 select hex(column_create(1, "800:46:06" AS time)) as hex,
-column_json(column_create(1, "800:46:06" AS time)) as json;
-hex	json
+column_json(column_create(1, "800:46:06" AS time)) as js;
+hex	js
 000100010007000060B82003	{"1":"800:46:06"}
 select hex(column_create("t", "2012-12-21 10:46:06.23434" AS datetime)) as hex,
-column_json(column_create("t", "2012-12-21 10:46:06.23434" AS datetime)) as json;
-hex	json
+column_json(column_create("t", "2012-12-21 10:46:06.23434" AS datetime)) as js;
+hex	js
 0401000100000005007495B90F649363B80A00	{"t":"2012-12-21 10:46:06.234340"}
 select hex(column_create(1, "2012-12-21 10:46:06.23434" AS datetime)) as hex,
-column_json(column_create(1, "2012-12-21 10:46:06.23434" AS datetime)) as json;
-hex	json
+column_json(column_create(1, "2012-12-21 10:46:06.23434" AS datetime)) as js;
+hex	js
 00010001000595B90F649363B80A00	{"1":"2012-12-21 10:46:06.234340"}
 select hex(column_create("t", "2012-12-21 10:46:06" AS datetime)) as hex,
-column_json(column_create("t", "2012-12-21 10:46:06" AS datetime)) as json;
-hex	json
+column_json(column_create("t", "2012-12-21 10:46:06" AS datetime)) as js;
+hex	js
 0401000100000005007495B90F86AB00	{"t":"2012-12-21 10:46:06"}
 select hex(column_create(1, "2012-12-21 10:46:06" AS datetime)) as hex,
-column_json(column_create(1, "2012-12-21 10:46:06" AS datetime)) as json;
-hex	json
+column_json(column_create(1, "2012-12-21 10:46:06" AS datetime)) as js;
+hex	js
 00010001000595B90F000060B80A00	{"1":"2012-12-21 10:46:06"}
 #
 # MDEV-4849: Out of memory error and valgrind warnings on COLUMN_ADD
diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result
index 1a47dd79fe8..6ee49d68787 100644
--- a/mysql-test/r/func_json.result
+++ b/mysql-test/r/func_json.result
@@ -46,6 +46,18 @@ json_array_append('["a", "b"]', '$', FALSE)
 select json_array_append('{"k1":1, "k2":["a", "b"]}', '$.k2', 2);
 json_array_append('{"k1":1, "k2":["a", "b"]}', '$.k2', 2)
 {"k1":1, "k2":["a", "b", 2]}
+SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x');
+JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x')
+["a", "x", {"b": [1, 2]}, [3, 4]]
+SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[2]', 'x');
+JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[2]', 'x')
+["a", {"b": [1, 2]}, "x", [3, 4]]
+SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[3]', 'x');
+JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[3]', 'x')
+["a", {"b": [1, 2]}, [3, 4], "x"]
+SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[4]', 'x');
+JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[4]', 'x')
+["a", {"b": [1, 2]}, [3, 4], "x"]
 select json_contains('{"k1":123, "k2":345}', '123', '$.k1');
 json_contains('{"k1":123, "k2":345}', '123', '$.k1')
 1
@@ -91,6 +103,44 @@ json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2")
 select json_extract('{"key0":true, "key1":"qwe"}', "$.key1");
 json_extract('{"key0":true, "key1":"qwe"}', "$.key1")
 qwe
+select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word');
+json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word')
+{"a":1, "b":{"c":1, "k1":"word"}, "d":[1, 2]}
+select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3);
+json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3)
+{"a":1, "b":{"c":1}, "d":[1, 2, 3]}
+select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2);
+json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2)
+{"a":[1, 2], "b":{"c":1}, "d":[1, 2]}
+select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word');
+json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word')
+{"a":1, "b":{"c":1}, "d":[1, 2]}
+select json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
+json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]')
+{ "a": 10, "b": [2, 3], "c":"[true, false]"}
+select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
+json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]')
+{ "a": 10, "b": [2, 3]}
+select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]');
+json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]')
+{ "a": 10, "b": "[true, false]"}
+set @j = '["a", ["b", "c"], "d"]';
+select json_remove(@j, '$[0]');
+json_remove(@j, '$[0]')
+[ ["b", "c"], "d"]
+select json_remove(@j, '$[1]');
+json_remove(@j, '$[1]')
+["a" "d"]
+select json_remove(@j, '$[2]');
+json_remove(@j, '$[2]')
+["a", ["b", "c"]]
+set @j = '{"a": 1, "b": [2, 3]}';
+select json_remove(@j, '$.b');
+json_remove(@j, '$.b')
+{"a": 1}
+select json_remove(@j, '$.a');
+json_remove(@j, '$.a')
+{"b": [2, 3]}
 select json_object("ki", 1, "mi", "ya");
 json_object("ki", 1, "mi", "ya")
 {"ki": 1, "mi": "ya"}
@@ -121,3 +171,46 @@ BOOLEAN
 select json_type('123');
 json_type('123')
 NUMBER
+select json_keys('{"a":{"c":1, "d":2}, "b":2}');
+json_keys('{"a":{"c":1, "d":2}, "b":2}')
+["a", "b"]
+select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.a");
+json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.a")
+["c", "d"]
+select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.b");
+json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.b")
+NULL
+SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
+select json_search(@j, 'one', 'abc');
+json_search(@j, 'one', 'abc')
+"$[0]"
+select json_search(@j, 'all', 'abc');
+json_search(@j, 'all', 'abc')
+["$[0]", "$[2].x"]
+select json_search(@j, 'all', 'abc', NULL, '$[2]');
+json_search(@j, 'all', 'abc', NULL, '$[2]')
+"$[2].x"
+select json_search(@j, 'all', 'abc', NULL, '$');
+json_search(@j, 'all', 'abc', NULL, '$')
+["$[0]", "$[2].x"]
+select json_search(@j, 'all', '10', NULL, '$');
+json_search(@j, 'all', '10', NULL, '$')
+"$[1][0].k"
+select json_search(@j, 'all', '10', NULL, '$[*]');
+json_search(@j, 'all', '10', NULL, '$[*]')
+"$[1][0].k"
+select json_search(@j, 'all', '10', NULL, '$[*][0].k');
+json_search(@j, 'all', '10', NULL, '$[*][0].k')
+"$[1][0].k"
+select json_unquote('"abc"');
+json_unquote('"abc"')
+abc
+select json_object("a", json_object("b", "abcd"));
+json_object("a", json_object("b", "abcd"))
+{"a": {"b": "abcd"}}
+select json_object("a", '{"b": "abcd"}');
+json_object("a", '{"b": "abcd"}')
+{"a": "{\"b\": \"abcd\"}"}
+select json_object("a", cast('{"b": "abcd"}' as json));
+json_object("a", cast('{"b": "abcd"}' as json))
+{"a": {"b": "abcd"}}
diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test
index 49b2c5542d3..f8f198be445 100644
--- a/mysql-test/t/dyncol.test
+++ b/mysql-test/t/dyncol.test
@@ -805,24 +805,24 @@ select column_json(column_create(1, "val", 2, column_create(3, "val2")));
 --echo # Time encoding
 --echo #
 select hex(column_create("t", "800:46:06.23434" AS time)) as hex,
-       column_json(column_create("t", "800:46:06.23434" AS time)) as json;
+       column_json(column_create("t", "800:46:06.23434" AS time)) as js;
 select hex(column_create(1, "800:46:06.23434" AS time)) as hex,
-       column_json(column_create(1, "800:46:06.23434" AS time)) as json;
+       column_json(column_create(1, "800:46:06.23434" AS time)) as js;
 
 select hex(column_create("t", "800:46:06" AS time)) as hex,
-       column_json(column_create("t", "800:46:06" AS time)) as json;
+       column_json(column_create("t", "800:46:06" AS time)) as js;
 select hex(column_create(1, "800:46:06" AS time)) as hex,
-       column_json(column_create(1, "800:46:06" AS time)) as json;
+       column_json(column_create(1, "800:46:06" AS time)) as js;
 
 select hex(column_create("t", "2012-12-21 10:46:06.23434" AS datetime)) as hex,
-       column_json(column_create("t", "2012-12-21 10:46:06.23434" AS datetime)) as json;
+       column_json(column_create("t", "2012-12-21 10:46:06.23434" AS datetime)) as js;
 select hex(column_create(1, "2012-12-21 10:46:06.23434" AS datetime)) as hex,
-       column_json(column_create(1, "2012-12-21 10:46:06.23434" AS datetime)) as json;
+       column_json(column_create(1, "2012-12-21 10:46:06.23434" AS datetime)) as js;
 
 select hex(column_create("t", "2012-12-21 10:46:06" AS datetime)) as hex,
-       column_json(column_create("t", "2012-12-21 10:46:06" AS datetime)) as json;
+       column_json(column_create("t", "2012-12-21 10:46:06" AS datetime)) as js;
 select hex(column_create(1, "2012-12-21 10:46:06" AS datetime)) as hex,
-       column_json(column_create(1, "2012-12-21 10:46:06" AS datetime)) as json;
+       column_json(column_create(1, "2012-12-21 10:46:06" AS datetime)) as js;
 
 --echo #
 --echo # MDEV-4849: Out of memory error and valgrind warnings on COLUMN_ADD
diff --git a/mysql-test/t/func_json.test b/mysql-test/t/func_json.test
index 3990ff24fc4..ffe48b0f9db 100644
--- a/mysql-test/t/func_json.test
+++ b/mysql-test/t/func_json.test
@@ -19,6 +19,11 @@ select json_array(1, "text", false, null);
 select json_array_append('["a", "b"]', '$', FALSE);
 select json_array_append('{"k1":1, "k2":["a", "b"]}', '$.k2', 2);
 
+SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x');
+SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[2]', 'x');
+SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[3]', 'x');
+SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[4]', 'x');
+
 select json_contains('{"k1":123, "k2":345}', '123', '$.k1');
 select json_contains('"you"', '"you"');
 select json_contains('"youth"', '"you"');
@@ -37,6 +42,24 @@ select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2");
 select json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2");
 select json_extract('{"key0":true, "key1":"qwe"}', "$.key1");
 
+select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word');
+select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3);
+select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2);
+select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word');
+
+select json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
+
+select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
+select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]');
+
+set @j = '["a", ["b", "c"], "d"]';
+select json_remove(@j, '$[0]');
+select json_remove(@j, '$[1]');
+select json_remove(@j, '$[2]');
+set @j = '{"a": 1, "b": [2, 3]}';
+select json_remove(@j, '$.b');
+select json_remove(@j, '$.a');
+
 select json_object("ki", 1, "mi", "ya");
 
 select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2");
@@ -52,3 +75,23 @@ select json_type('[123, "k2", 345]');
 select json_type("true");
 select json_type('123');
 
+select json_keys('{"a":{"c":1, "d":2}, "b":2}');
+select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.a");
+select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.b");
+
+SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
+select json_search(@j, 'one', 'abc');
+select json_search(@j, 'all', 'abc');
+select json_search(@j, 'all', 'abc', NULL, '$[2]');
+select json_search(@j, 'all', 'abc', NULL, '$');
+select json_search(@j, 'all', '10', NULL, '$');
+select json_search(@j, 'all', '10', NULL, '$[*]');
+select json_search(@j, 'all', '10', NULL, '$[*][0].k');
+
+
+select json_unquote('"abc"');
+
+select json_object("a", json_object("b", "abcd"));
+select json_object("a", '{"b": "abcd"}');
+select json_object("a", cast('{"b": "abcd"}' as json));
+