summaryrefslogtreecommitdiff
path: root/mysql-test/main/func_json.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/func_json.result')
-rw-r--r--mysql-test/main/func_json.result770
1 files changed, 770 insertions, 0 deletions
diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result
new file mode 100644
index 00000000000..9b41c1f9d61
--- /dev/null
+++ b/mysql-test/main/func_json.result
@@ -0,0 +1,770 @@
+select json_valid('[1, 2]');
+json_valid('[1, 2]')
+1
+select json_valid('"string"}');
+json_valid('"string"}')
+0
+select json_valid('{"key1":1, "key2":[2,3]}');
+json_valid('{"key1":1, "key2":[2,3]}')
+1
+select json_valid('[false, true, null]');
+json_valid('[false, true, null]')
+1
+select json_valid(repeat('[', 1000));
+json_valid(repeat('[', 1000))
+0
+select json_valid(repeat('{"a":', 1000));
+json_valid(repeat('{"a":', 1000))
+0
+select json_value('{"key1":123}', '$.key2');
+json_value('{"key1":123}', '$.key2')
+NULL
+select json_value('{"key1":123}', '$.key1');
+json_value('{"key1":123}', '$.key1')
+123
+select json_value('{"key1":[1,2,3]}', '$.key1');
+json_value('{"key1":[1,2,3]}', '$.key1')
+NULL
+select json_value('{"key1": [1,2,3], "key1":123}', '$.key1');
+json_value('{"key1": [1,2,3], "key1":123}', '$.key1')
+123
+select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z');
+JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z')
+Mon"t"y
+select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2');
+json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2')
+NULL
+select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1');
+json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1')
+{"a":1, "b":[1,2]}
+select json_query('{"key1": 1}', '$.key1');
+json_query('{"key1": 1}', '$.key1')
+NULL
+select json_query('{"key1":123, "key1": [1,2,3]}', '$.key1');
+json_query('{"key1":123, "key1": [1,2,3]}', '$.key1')
+[1,2,3]
+select json_query('{"key1":123, "key1": [1,2,3]}', concat('$', repeat('.k', 1000)));
+json_query('{"key1":123, "key1": [1,2,3]}', concat('$', repeat('.k', 1000)))
+NULL
+select json_array();
+json_array()
+[]
+select json_array(1);
+json_array(1)
+[1]
+select json_array(1, "text", false, null);
+json_array(1, "text", false, null)
+[1, "text", false, null]
+select json_array_append('["a", "b"]', '$', FALSE);
+json_array_append('["a", "b"]', '$', FALSE)
+["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_append('["a", ["b", "c"], "d"]', '$[0]', 2);
+json_array_append('["a", ["b", "c"], "d"]', '$[0]', 2)
+[["a", 2], ["b", "c"], "d"]
+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_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x');
+json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x')
+["a", {"b": ["x", 1, 2]}, [3, 4]]
+select json_array_insert('true', '$', 1);
+json_array_insert('true', '$', 1)
+NULL
+select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2][1]', 'y');
+json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2][1]', 'y')
+["a", {"b": [1, 2]}, [3, "y", 4]]
+select json_contains('{"k1":123, "k2":345}', '123', '$.k1');
+json_contains('{"k1":123, "k2":345}', '123', '$.k1')
+1
+select json_contains('"you"', '"you"');
+json_contains('"you"', '"you"')
+1
+select json_contains('"youth"', '"you"');
+json_contains('"youth"', '"you"')
+0
+select json_contains('[1]', '[1]', '$', '$[0]');
+ERROR 42000: Incorrect parameter count in the call to native function 'json_contains'
+select json_contains('', '', '$');
+json_contains('', '', '$')
+NULL
+Warnings:
+Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_contains'
+select json_contains('null', 'null', '$');
+json_contains('null', 'null', '$')
+1
+select json_contains('"10"', '"10"', '$');
+json_contains('"10"', '"10"', '$')
+1
+select json_contains('"10"', '10', '$');
+json_contains('"10"', '10', '$')
+0
+select json_contains('10.1', '10', '$');
+json_contains('10.1', '10', '$')
+0
+select json_contains('10.0', '10', '$');
+json_contains('10.0', '10', '$')
+1
+select json_contains('[1]', '1');
+json_contains('[1]', '1')
+1
+select json_contains('[2, 1]', '1');
+json_contains('[2, 1]', '1')
+1
+select json_contains('[2, [2, 3], 1]', '1');
+json_contains('[2, [2, 3], 1]', '1')
+1
+select json_contains('[4, [2, 3], 1]', '2');
+json_contains('[4, [2, 3], 1]', '2')
+1
+select json_contains('[2, 1]', '[1, 2]');
+json_contains('[2, 1]', '[1, 2]')
+1
+select json_contains('[2, 1]', '[1, 0, 2]');
+json_contains('[2, 1]', '[1, 0, 2]')
+0
+select json_contains('[2, 0, 3, 1]', '[1, 2]');
+json_contains('[2, 0, 3, 1]', '[1, 2]')
+1
+select json_contains('{"b":[1,2], "a":1}', '{"a":1, "b":2}');
+json_contains('{"b":[1,2], "a":1}', '{"a":1, "b":2}')
+1
+select json_contains('{"a":1}', '{}');
+json_contains('{"a":1}', '{}')
+1
+select json_contains('[1, {"a":1}]', '{}');
+json_contains('[1, {"a":1}]', '{}')
+1
+select json_contains('[1, {"a":1}]', '{"a":1}');
+json_contains('[1, {"a":1}]', '{"a":1}')
+1
+select json_contains('[{"abc":"def", "def":"abc"}]', '["foo","bar"]');
+json_contains('[{"abc":"def", "def":"abc"}]', '["foo","bar"]')
+0
+select json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]');
+json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]')
+1
+select json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}');
+json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}')
+1
+select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]");
+json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]")
+1
+select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[10]");
+json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[10]")
+0
+select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.ma");
+json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.ma")
+0
+select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1");
+json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1")
+1
+select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1", "$.ma");
+json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1", "$.ma")
+1
+select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.ma");
+json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.ma")
+0
+select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.key2");
+json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.key2")
+1
+select json_contains_path('{ "a": true }', NULL, '$.a' );
+json_contains_path('{ "a": true }', NULL, '$.a' )
+NULL
+select json_contains_path('{ "a": true }', 'all', NULL );
+json_contains_path('{ "a": true }', 'all', NULL )
+NULL
+select json_contains_path('{"a":{"b":"c"}}', 'one', '$.a.*');
+json_contains_path('{"a":{"b":"c"}}', 'one', '$.a.*')
+1
+select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1");
+json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1")
+"asd"
+select json_extract('{"key1":"asd", "key2":[2,3]}', "$.keyX", "$.keyY");
+json_extract('{"key1":"asd", "key2":[2,3]}', "$.keyX", "$.keyY")
+NULL
+select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2");
+json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2")
+["asd", [2, 3]]
+select json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2");
+json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2")
+[5, [2, 3]]
+select json_extract('{"key0":true, "key1":"qwe"}', "$.key1");
+json_extract('{"key0":true, "key1":"qwe"}', "$.key1")
+"qwe"
+select json_extract(json_object('foo', 'foobar'),'$');
+json_extract(json_object('foo', 'foobar'),'$')
+{"foo": "foobar"}
+select json_extract('[10, 20, [30, 40]]', '$[2][*]');
+json_extract('[10, 20, [30, 40]]', '$[2][*]')
+[30, 40]
+select json_extract('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]');
+json_extract('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]')
+[{"a": 3}, 30, 40]
+select json_extract('1', '$');
+json_extract('1', '$')
+1
+select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]');
+json_extract('[10, 20, [30, 40], 1, 10]', '$[1]')
+20
+select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]');
+json_extract('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]')
+[20]
+select json_extract( '[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a');
+json_extract( '[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a')
+[[3, 4]]
+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();
+json_object()
+{}
+select json_object("ki", 1, "mi", "ya");
+json_object("ki", 1, "mi", "ya")
+{"ki": 1, "mi": "ya"}
+create table t1 as select json_object('id', 87, 'name', 'carrot') as f;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `f` varchar(32) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t1;
+f
+{"id": 87, "name": "carrot"}
+drop table t1;
+select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2");
+json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2")
+1
+select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]");
+json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]")
+1
+select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]");
+json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]")
+0
+select json_quote('"string"');
+json_quote('"string"')
+"\"string\""
+create table t1 as select json_quote('foo');
+select * from t1;
+json_quote('foo')
+"foo"
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `json_quote('foo')` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+select json_merge('string');
+ERROR 42000: Incorrect parameter count in the call to native function 'json_merge'
+select json_merge('string', 123);
+json_merge('string', 123)
+NULL
+Warnings:
+Warning 4038 Syntax error in JSON text in argument 1 to function 'json_merge' at position 1
+select json_merge('"string"', 123);
+json_merge('"string"', 123)
+["string", 123]
+select json_merge('[1, 2]', '[true, false]');
+json_merge('[1, 2]', '[true, false]')
+[1, 2, true, false]
+select json_merge('{"1": 2}', '{"true": false}');
+json_merge('{"1": 2}', '{"true": false}')
+{"1": 2, "true": false}
+select json_merge('{"1": 2}', '{"true": false}', '{"3": 4}');
+json_merge('{"1": 2}', '{"true": false}', '{"3": 4}')
+{"1": 2, "true": false, "3": 4}
+select json_merge(NULL,json_object('foo', 1));
+json_merge(NULL,json_object('foo', 1))
+NULL
+select json_merge('a','b');
+json_merge('a','b')
+NULL
+Warnings:
+Warning 4038 Syntax error in JSON text in argument 1 to function 'json_merge' at position 1
+select json_merge('{"a":"b"}','{"c":"d"}');
+json_merge('{"a":"b"}','{"c":"d"}')
+{"a": "b", "c": "d"}
+SELECT JSON_MERGE('[1, 2]', '{"id": 47}');
+JSON_MERGE('[1, 2]', '{"id": 47}')
+[1, 2, {"id": 47}]
+select json_type('{"k1":123, "k2":345}');
+json_type('{"k1":123, "k2":345}')
+OBJECT
+select json_type('[123, "k2", 345]');
+json_type('[123, "k2", 345]')
+ARRAY
+select json_type("true");
+json_type("true")
+BOOLEAN
+select json_type('123');
+json_type('123')
+INTEGER
+select json_type('123.12');
+json_type('123.12')
+DOUBLE
+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
+select json_keys('foo');
+json_keys('foo')
+NULL
+Warnings:
+Warning 4038 Syntax error in JSON text in argument 1 to function 'json_keys' at position 1
+select json_keys('{"a":{"c":1, "d":2}, "b":2, "c":1, "a":3, "b":1, "c":2}');
+json_keys('{"a":{"c":1, "d":2}, "b":2, "c":1, "a":3, "b":1, "c":2}')
+["a", "b", "c"]
+select json_keys('{"c1": "value 1", "c1": "value 2"}');
+json_keys('{"c1": "value 1", "c1": "value 2"}')
+["c1"]
+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_search(@j, 'all', '10', NULL, '$**.k');
+json_search(@j, 'all', '10', NULL, '$**.k')
+"$[1][0].k"
+create table t1( json_col text );
+insert into t1 values
+('{ "a": "foobar" }'),
+('{ "a": "foobar", "b": "focus", "c": [ "arm", "foot", "shoulder" ] }');
+select json_search( json_col, 'all', 'foot' ) from t1;
+json_search( json_col, 'all', 'foot' )
+NULL
+"$.c[1]"
+drop table t1;
+select json_unquote('"abc"');
+json_unquote('"abc"')
+abc
+select json_unquote('abc');
+json_unquote('abc')
+abc
+create table t1 (c VARCHAR(8)) DEFAULT CHARSET=latin1;
+insert into t1 values ('abc'),('def');
+select json_object('foo', json_unquote(json_object('bar', c)),'qux', c) as fld from t1;
+fld
+{"foo": "{\"bar\": \"abc\"}", "qux": "abc"}
+{"foo": "{\"bar\": \"def\"}", "qux": "def"}
+drop table t1;
+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", json_compact('{"b": "abcd"}'));
+json_object("a", json_compact('{"b": "abcd"}'))
+{"a": {"b": "abcd"}}
+select json_compact(NULL);
+json_compact(NULL)
+NULL
+select json_depth(json_compact(NULL));
+json_depth(json_compact(NULL))
+NULL
+select json_depth('[[], {}]');
+json_depth('[[], {}]')
+2
+select json_depth('[[[1,2,3],"s"], {}, []]');
+json_depth('[[[1,2,3],"s"], {}, []]')
+4
+select json_depth('[10, {"a": 20}]');
+json_depth('[10, {"a": 20}]')
+3
+select json_length('');
+json_length('')
+NULL
+Warnings:
+Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_length'
+select json_length('{}');
+json_length('{}')
+0
+select json_length('[1, 2, {"a": 3}]');
+json_length('[1, 2, {"a": 3}]')
+3
+select json_length('{"a": 1, "b": {"c": 30}}', '$.b');
+json_length('{"a": 1, "b": {"c": 30}}', '$.b')
+1
+select json_length('{"a": 1, "b": {"c": 30}}');
+json_length('{"a": 1, "b": {"c": 30}}')
+2
+select json_length('{}{');
+json_length('{}{')
+NULL
+Warnings:
+Warning 4038 Syntax error in JSON text in argument 1 to function 'json_length' at position 3
+create table json (j INT);
+show create table json;
+Table Create Table
+json CREATE TABLE `json` (
+ `j` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table json;
+select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' );
+json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' )
+1
+select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' );
+json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' )
+1
+select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0]' );
+json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0]' )
+1
+select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0][0]' );
+json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0][0]' )
+1
+select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2]' );
+json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2]' )
+2
+select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0]' );
+json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0]' )
+2
+select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0]' );
+json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0]' )
+2
+select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0][0]' );
+json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0][0]' )
+2
+select json_length( '{"a":{"b":{"d":1}}, "a":{"c":{"d":1, "j":2}}}', '$.a[0][0][0].c' );
+json_length( '{"a":{"b":{"d":1}}, "a":{"c":{"d":1, "j":2}}}', '$.a[0][0][0].c' )
+2
+select json_set('1', '$[0]', 100);
+json_set('1', '$[0]', 100)
+100
+select json_set('1', '$[0][0]', 100);
+json_set('1', '$[0][0]', 100)
+100
+select json_set('1', '$[1]', 100);
+json_set('1', '$[1]', 100)
+[1, 100]
+select json_set('{"a":12}', '$[0]', 100);
+json_set('{"a":12}', '$[0]', 100)
+100
+select json_set('{"a":12}', '$[0].a', 100);
+json_set('{"a":12}', '$[0].a', 100)
+{"a": 100}
+select json_set('{"a":12}', '$[0][0].a', 100);
+json_set('{"a":12}', '$[0][0].a', 100)
+{"a": 100}
+select json_set('{"a":12}', '$[0][1].a', 100);
+json_set('{"a":12}', '$[0][1].a', 100)
+{"a": 12}
+select json_value('{"\\"key1":123}', '$."\\"key1"');
+json_value('{"\\"key1":123}', '$."\\"key1"')
+123
+select json_value('{"\\"key1\\"":123}', '$."\\"key1\\""');
+json_value('{"\\"key1\\"":123}', '$."\\"key1\\""')
+123
+select json_value('{"key 1":123}', '$."key 1"');
+json_value('{"key 1":123}', '$."key 1"')
+123
+select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]");
+json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]")
+1
+select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[3]");
+json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[3]")
+0
+select json_extract( '[1]', '$[0][0]' );
+json_extract( '[1]', '$[0][0]' )
+1
+select json_extract( '[1]', '$[1][0]' );
+json_extract( '[1]', '$[1][0]' )
+NULL
+select json_extract( '[1]', '$**[0]' );
+json_extract( '[1]', '$**[0]' )
+[1]
+select json_extract( '[1]', '$**[0][0]' );
+json_extract( '[1]', '$**[0][0]' )
+[1]
+select json_insert('1', '$[0]', 4);
+json_insert('1', '$[0]', 4)
+1
+select json_replace('1', '$[0]', 4);
+json_replace('1', '$[0]', 4)
+4
+select json_set('1', '$[0]', 4);
+json_set('1', '$[0]', 4)
+4
+select json_set('1', '$[1]', 4);
+json_set('1', '$[1]', 4)
+[1, 4]
+select json_replace('1', '$[1]', 4);
+json_replace('1', '$[1]', 4)
+1
+SELECT json_insert('[]', '$[0][0]', 100);
+json_insert('[]', '$[0][0]', 100)
+[]
+SELECT json_insert('1', '$[0][0]', 100);
+json_insert('1', '$[0][0]', 100)
+1
+SELECT json_replace('1', '$[0][0]', 100);
+json_replace('1', '$[0][0]', 100)
+100
+SELECT json_replace('[]', '$[0][0]', 100);
+json_replace('[]', '$[0][0]', 100)
+[]
+SELECT json_set('[]', '$[0][0]', 100);
+json_set('[]', '$[0][0]', 100)
+[]
+SELECT json_set('[]', '$[0][0][0]', 100);
+json_set('[]', '$[0][0][0]', 100)
+[]
+SELECT JSON_search( '{"": "a"}', "one", 'a');
+JSON_search( '{"": "a"}', "one", 'a')
+"$."
+select json_merge('{"a":"b"}', '{"a":"c"}') ;
+json_merge('{"a":"b"}', '{"a":"c"}')
+{"a": ["b", "c"]}
+select json_merge('{"a":{"x":"b"}}', '{"a":"c"}') ;
+json_merge('{"a":{"x":"b"}}', '{"a":"c"}')
+{"a": [{"x": "b"}, "c"]}
+select json_merge('{"a":{"u":12, "x":"b"}}', '{"a":{"x":"c"}}') ;
+json_merge('{"a":{"u":12, "x":"b"}}', '{"a":{"x":"c"}}')
+{"a": {"u": 12, "x": ["b", "c"]}}
+select json_merge('{"a":{"u":12, "x":"b", "r":1}}', '{"a":{"x":"c", "r":2}}') ;
+json_merge('{"a":{"u":12, "x":"b", "r":1}}', '{"a":{"x":"c", "r":2}}')
+{"a": {"u": 12, "x": ["b", "c"], "r": [1, 2]}}
+select json_compact('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}');
+json_compact('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}')
+{"a":1,"b":[1,2,3],"c":{"aa":"v1","bb":"v2"}}
+select json_loose('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}');
+json_loose('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}')
+{"a": 1, "b": [1, 2, 3], "c": {"aa": "v1", "bb": "v2"}}
+select json_detailed('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}');
+json_detailed('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}')
+{
+ "a": 1,
+ "b":
+ [
+ 1,
+ 2,
+ 3
+ ],
+ "c":
+ {
+ "aa": "v1",
+ "bb": "v2"
+ }
+}
+SELECT JSON_search( '{"x": "\\""}', "one", '"');
+JSON_search( '{"x": "\\""}', "one", '"')
+"$.x"
+SELECT JSON_search( '{"x": "\\""}', "one", '\\"');
+JSON_search( '{"x": "\\""}', "one", '\\"')
+"$.x"
+set @@global.net_buffer_length=1024;
+set @@global.max_allowed_packet=2048;
+connect newconn, localhost, root,,;
+show variables like 'net_buffer_length';
+Variable_name Value
+net_buffer_length 1024
+show variables like 'max_allowed_packet';
+Variable_name Value
+max_allowed_packet 2048
+select json_array(repeat('a',1024),repeat('a',1024));
+json_array(repeat('a',1024),repeat('a',1024))
+NULL
+Warnings:
+Warning 1301 Result of json_array() was larger than max_allowed_packet (2048) - truncated
+select json_object("a", repeat('a',1024),"b", repeat('a',1024));
+json_object("a", repeat('a',1024),"b", repeat('a',1024))
+NULL
+Warnings:
+Warning 1301 Result of json_object() was larger than max_allowed_packet (2048) - truncated
+connection default;
+set @@global.max_allowed_packet = default;
+set @@global.net_buffer_length = default;
+disconnect newconn;
+create table t1(j longtext, p longtext);
+insert into t1 values
+('{"a":1,"b":2,"c":3}','$.a'),
+('{"a":1,"b":2,"c":3}','$.b'),
+('{"a":1,"b":2,"c":3}','$.c');
+select j, p, json_remove(j, p) from t1;
+j p json_remove(j, p)
+{"a":1,"b":2,"c":3} $.a {"b": 2, "c": 3}
+{"a":1,"b":2,"c":3} $.b {"a": 1, "c": 3}
+{"a":1,"b":2,"c":3} $.c {"a": 1, "b": 2}
+drop table t1;
+SET @str = 'bar', @path = '$';
+SELECT JSON_SEARCH('{"foo":"bar"}', 'all' , @str, '%', @path);
+JSON_SEARCH('{"foo":"bar"}', 'all' , @str, '%', @path)
+"$.foo"
+SELECT JSON_VALUE('[{"foo": 1},"bar"]', '$[*][0]');
+JSON_VALUE('[{"foo": 1},"bar"]', '$[*][0]')
+bar
+CREATE TABLE t1 (f INT NOT NULL);
+INSERT INTO t1 VALUES (0);
+SELECT JSON_KEYS(f) FROM t1 ORDER BY 1;
+JSON_KEYS(f)
+NULL
+DROP TABLE t1;
+SELECT JSON_EXTRACT( '{"foo":"bar"}', '$[*].*' );
+JSON_EXTRACT( '{"foo":"bar"}', '$[*].*' )
+NULL
+SELECT JSON_EXTRACT( '{"foo":"bar"}', '$[*]' );
+JSON_EXTRACT( '{"foo":"bar"}', '$[*]' )
+NULL
+select JSON_EXTRACT('{"name":"value"}', '$.name') = 'value';
+JSON_EXTRACT('{"name":"value"}', '$.name') = 'value'
+1
+select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = true;
+JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = true
+1
+select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = false;
+JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = false
+0
+select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = 1;
+JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = 1
+1
+select JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"');
+JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"')
+"\u00f6"
+select JSON_EXTRACT('{"foo": "bar" foobar foo invalid ', '$.foo');
+JSON_EXTRACT('{"foo": "bar" foobar foo invalid ', '$.foo')
+NULL
+Warnings:
+Warning 4038 Syntax error in JSON text in argument 1 to function 'json_extract' at position 15
+SELECT JSON_OBJECT('foo', '`');
+JSON_OBJECT('foo', '`')
+{"foo": "`"}
+SELECT JSON_OBJECT("foo", "bar`bar");
+JSON_OBJECT("foo", "bar`bar")
+{"foo": "bar`bar"}
+SELECT JSON_SET('{}', '$.age', 87);
+JSON_SET('{}', '$.age', 87)
+{"age": 87}
+SELECT JSON_MERGE('[]', '{"c":"d"}');
+JSON_MERGE('[]', '{"c":"d"}')
+[{"c": "d"}]
+SET @str = "{\"\\u00e4\\u00f6\":\"yes\"}";
+SET @path = "$.\"\\u00e4\\u00f6\"";
+select @str, @path, JSON_EXTRACT(@str, @path);
+@str @path JSON_EXTRACT(@str, @path)
+{"\u00e4\u00f6":"yes"} $."\u00e4\u00f6" "yes"
+SET @str = "{\"\\u00e4\":\"yes\"}";
+SET @path = "$.\"\\u00e4\"";
+select @str, @path, JSON_EXTRACT(@str, @path);
+@str @path JSON_EXTRACT(@str, @path)
+{"\u00e4":"yes"} $."\u00e4" "yes"
+select json_array(5,json_query('[1,2]','$'));
+json_array(5,json_query('[1,2]','$'))
+[5, [1,2]]
+SELECT JSON_ARRAY('1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú') AS json_data;
+json_data
+["1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú"]
+SELECT JSON_OBJECT("user","Jožko Mrkvičká") as json_data;
+json_data
+{"user": "Jožko Mrkvičká"}
+select json_contains_path('{"foo":"bar"}', 'one', '$[]');
+json_contains_path('{"foo":"bar"}', 'one', '$[]')
+NULL
+Warnings:
+Warning 4042 Syntax error in JSON path in argument 3 to function 'json_contains_path' at position 3
+select JSON_VALID(0x36f0c8dccd83c5eac156da);
+JSON_VALID(0x36f0c8dccd83c5eac156da)
+0
+create table t1(a double not null);
+insert into t1 values (2),(1);
+select 1 from t1 where json_extract(a,'$','$[81]');
+1
+drop table t1;
+select json_extract('{"test":8.437e-5}','$.test');
+json_extract('{"test":8.437e-5}','$.test')
+8.437e-5
+#
+# End of 10.2 tests
+#
+#
+# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
+#
+SELECT
+JSON_VALID('{"id": 1, "name": "Monty"}') AS json_valid,
+JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") AS json_exists,
+JSON_CONTAINS('{"A": 0, "B": {"C": 1}, "D": 2}', '2', '$.A') AS ison_contains,
+JSON_CONTAINS_PATH('{"A": 1, "B": [2], "C": [3, 4]}', 'one', '$.A', '$.D') AS json_contains_path;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def json_valid 3 1 1 Y 32896 0 63
+def json_exists 3 1 1 Y 32896 0 63
+def ison_contains 3 1 1 Y 32896 0 63
+def json_contains_path 3 1 1 Y 32896 0 63
+json_valid json_exists ison_contains json_contains_path
+1 1 0 1
+SELECT
+JSON_LENGTH('{"a": 1, "b": {"c": 30}}') AS json_length,
+JSON_DEPTH('[10, {"a": 20}]') AS json_depnth;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def json_length 3 10 1 Y 32896 0 63
+def json_depnth 3 10 1 N 32897 0 63
+json_length json_depnth
+2 3
+#
+# End of 10.3 tests
+#
View Lorry Controller Status