From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/main/func_json.result | 2547 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 2547 insertions(+) create mode 100644 mysql-test/main/func_json.result (limited to 'mysql-test/main/func_json.result') diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result new file mode 100644 index 00000000..0336de08 --- /dev/null +++ b/mysql-test/main/func_json.result @@ -0,0 +1,2547 @@ +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') as exp; +exp +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))) as exp; +exp +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') as exp; +exp +["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') as exp; +exp +["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", {}]') as exp; +exp +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]") as exp; +exp +1 +select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[10]") as exp; +exp +0 +select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.ma") as exp; +exp +0 +select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1") as exp; +exp +1 +select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1", "$.ma") as exp; +exp +1 +select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.ma") as exp; +exp +0 +select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.key2") as exp; +exp +1 +select json_contains_path('{ "a": true }', NULL, '$.a' ) as exp; +exp +NULL +select json_contains_path('{ "a": true }', 'all', NULL ) as exp; +exp +NULL +select json_contains_path('{"a":{"b":"c"}}', 'one', '$.a.*') as exp; +exp +1 +select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1") as exp; +exp +"asd" +select json_extract('{"key1":"asd", "key2":[2,3]}', "$.keyX", "$.keyY") as exp; +exp +NULL +select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2") as exp; +exp +["asd", [2, 3]] +select json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2") as exp; +exp +[5, [2, 3]] +select json_extract('{"key0":true, "key1":"qwe"}', "$.key1") as exp; +exp +"qwe" +select json_extract(json_object('foo', 'foobar'),'$') as exp; +exp +{"foo": "foobar"} +select json_extract('[10, 20, [30, 40]]', '$[2][*]') as exp; +exp +[30, 40] +select json_extract('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]') as exp; +exp +[{"a": 3}, 30, 40] +select json_extract('1', '$') as exp; +exp +1 +select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]') as exp; +exp +20 +select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]') as exp; +exp +[20] +select json_extract( '[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a') as exp; +exp +[[3, 4]] +select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word') as exp; +exp +{"a": 1, "b": {"c": 1, "k1": "word"}, "d": [1, 2]} +select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3) as exp; +exp +{"a": 1, "b": {"c": 1}, "d": [1, 2, 3]} +select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2) as exp; +exp +{"a": [1, 2], "b": {"c": 1}, "d": [1, 2]} +select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word') as exp; +exp +{"a": 1, "b": {"c": 1}, "d": [1, 2]} +select json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') as exp; +exp +{"a": 10, "b": [2, 3], "c": "[true, false]"} +select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') as exp; +exp +{"a": 10, "b": [2, 3]} +select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]') as exp; +exp +{"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 COLLATE=latin1_swedish_ci +select * from t1; +f +{"id": 87, "name": "carrot"} +drop table t1; +select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") as ex; +ex +1 +select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]") as ex; +ex +1 +select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]") as ex; +ex +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(38) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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_preserve' 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_preserve' 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}') as ex; +ex +["a", "b", "c"] +select json_keys('{"c1": "value 1", "c1": "value 2"}') as ex; +ex +["c1"] +SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]'; +select json_search(@j, 'one', 'abc') as ex; +ex +"$[0]" +select json_search(@j, 'all', 'abc') as ex; +ex +["$[0]", "$[2].x"] +select json_search(@j, 'all', 'abc', NULL, '$[2]') as ex; +ex +"$[2].x" +select json_search(@j, 'all', 'abc', NULL, '$') as ex; +ex +["$[0]", "$[2].x"] +select json_search(@j, 'all', '10', NULL, '$') as ex; +ex +"$[1][0].k" +select json_search(@j, 'all', '10', NULL, '$[*]') as ex; +ex +"$[1][0].k" +select json_search(@j, 'all', '10', NULL, '$[*][0].k') as ex; +ex +"$[1][0].k" +select json_search(@j, 'all', '10', NULL, '$**.k') as ex; +ex +"$[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' ) as ex from t1; +ex +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 COLLATE=latin1_swedish_ci +drop table json; +select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' ) as ex; +ex +1 +select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' ) as ex; +ex +1 +select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0]' ) as ex; +ex +1 +select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0][0]' ) as ex; +ex +1 +select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2]' ) as ex; +ex +2 +select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0]' ) as ex; +ex +2 +select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0]' ) as ex; +ex +2 +select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0][0]' ) as ex; +ex +2 +select json_length( '{"a":{"b":{"d":1}}, "a":{"c":{"d":1, "j":2}}}', '$.a[0][0][0].c' ) as ex; +ex +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"') as ex; +ex +123 +select json_value('{"\\"key1\\"":123}', '$."\\"key1\\""') as ex; +ex +123 +select json_value('{"key 1":123}', '$."key 1"') as ex; +ex +123 +select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]") as ex; +ex +1 +select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[3]") as ex; +ex +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"}') as ex ; +ex +{"a": ["b", "c"]} +select json_merge('{"a":{"x":"b"}}', '{"a":"c"}') as ex ; +ex +{"a": [{"x": "b"}, "c"]} +select json_merge('{"a":{"u":12, "x":"b"}}', '{"a":{"x":"c"}}') as ex ; +ex +{"a": {"u": 12, "x": ["b", "c"]}} +select json_merge('{"a":{"u":12, "x":"b", "r":1}}', '{"a":{"x":"c", "r":2}}') as ex ; +ex +{"a": {"u": 12, "x": ["b", "c"], "r": [1, 2]}} +select json_compact('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') as ex; +ex +{"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"}}') as ex; +ex +{"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"}}') as ex; +ex +{ + "a": 1, + "b": + [ + 1, + 2, + 3 + ], + "c": + { + "aa": "v1", + "bb": "v2" + } +} +SELECT JSON_search( '{"x": "\\""}', "one", '"') as ex; +ex +"$.x" +SELECT JSON_search( '{"x": "\\""}', "one", '\\"') as ex; +ex +"$.x" +set @save_max_allowed_packet=@@max_allowed_packet; +set @save_net_buffer_length=@@net_buffer_length; +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)) as ex; +ex +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)) as ex; +ex +NULL +Warnings: +Warning 1301 Result of json_object() was larger than max_allowed_packet (2048) - truncated +connection default; +set @@global.max_allowed_packet = @save_max_allowed_packet; +set @@global.net_buffer_length = @save_net_buffer_length; +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' as ex; +ex +1 +select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = true as ex; +ex +1 +select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = false as ex; +ex +0 +select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = 1 as ex; +ex +1 +select JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"') as ex; +ex +"\u00f6" +select JSON_EXTRACT('{"foo": "bar" foobar foo invalid ', '$.foo') as ex; +ex +NULL +Warnings: +Warning 4038 Syntax error in JSON text in argument 1 to function 'json_extract' at position 15 +SELECT JSON_OBJECT('foo', '`') as ex; +ex +{"foo": "`"} +SELECT JSON_OBJECT("foo", "bar`bar") as ex; +ex +{"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 +select json_value('{"b":true}','$.b')=1; +json_value('{"b":true}','$.b')=1 +1 +CREATE TABLE t1 (c VARCHAR(8)); +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT * FROM t1 WHERE c IN (JSON_EXTRACT('{"a":"b"}', '$.*')); +c +DROP TABLE t1; +# +# MDEV-16814 CREATE TABLE SELECT JSON_QUOTE(multibyte_charset_expr) makes a field of a wrong length +# +CREATE TABLE t1 AS SELECT +JSON_QUOTE(_latin1'foo') AS c1, +JSON_QUOTE(_utf8'foo') AS c2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(38) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, + `c2` varchar(38) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# MDEV-16054 simple json functions flatline cpu on garbage input. +# +select json_array(1,user(),compress(5.140264e+307)); +json_array(1,user(),compress(5.140264e+307)) +NULL +# +# MDEV-16869 String functions don't respect character set of JSON_VALUE. +# +create table t1(json_col TEXT) DEFAULT CHARSET=latin1; +insert into t1 values (_latin1 X'7B226B657931223A2253EC227D'); +select JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' from t1; +JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' +1 +select REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' as exp from t1; +exp +1 +drop table t1; +# +# MDEV-16750 JSON_SET mishandles unicode every second pair of arguments. +# +SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6) as exp; +exp +{"a": ""} +SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6) as exp; +exp +{"a": "", "b": ""} +SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6') as exp; +exp +{"a": "", "x": 1, "b": ""} +# +# MDEV-17121 JSON_ARRAY_APPEND +# +select json_array_append('[ ]', '$', 'aue'); +json_array_append('[ ]', '$', 'aue') +["aue"] +# +# MDEV-17018 JSON_SEARCH and User-Defined Variables. +# +SET @`json` := '["A", [{"B": "1"}], {"C": "AB"}, {"D": "BC"}]', @`value` := 'AB'; +SELECT JSON_SEARCH(@`json`, 'one', @`value`); +JSON_SEARCH(@`json`, 'one', @`value`) +"$[2].C" +SET @`json` := NULL, @`value` := NULL; +# +# MDEV-17001 JSON_MERGE returns nullwhen merging empty array. +# +SELECT JSON_MERGE('[1]', '[]'); +JSON_MERGE('[1]', '[]') +[1] +# +# MDEV-16174 Assertion `0' failed in Type_handler_string_result:: +# make_sort_key(uchar*, Item*, const SORT_FIELD_ATTR*, Sort_param*) +# +SET sql_mode=''; +CREATE TABLE t1 (fld varchar(16) NOT NULL); +CREATE TABLE t2 SELECT JSON_ARRAY_INSERT(fld, '$.[0]', '0') FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `JSON_ARRAY_INSERT(fld, '$.[0]', '0')` varchar(21) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1, t2; +SET sql_mode=default; +# +# MDEV-17454 JSON_VALID( '{"a":1]' ) evaluates to 1 +# +select JSON_VALID( '{"a":1]' ); +JSON_VALID( '{"a":1]' ) +0 +# +# MDEV-18886 JSON_ARRAY() does not recognise JSON argument. +# +SELECT JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket')) as exp; +exp +["str", {"plugin": "unix_socket"}] +SELECT CHARSET(JSON_ARRAY()) as exp; +exp +latin1 +SELECT CHARSET(JSON_OBJECT()) as exp; +exp +latin1 +# +# MDEV-13992 Implement JSON_MERGE_PATCH +# +CREATE TABLE merge_t( +id INT PRIMARY KEY AUTO_INCREMENT, +target VARCHAR(100), patch VARCHAR(100) +); +INSERT INTO merge_t(target, patch) VALUES +('{"a":"b"}', '{"a":"c"}'), +('{"a":"b"}', '{"b":"c"}'), +('{"a":"b"}', '{"a":null}'), +('{"a":"b", "b":"c"}', '{"a":null}'), +('{"a":["b"]}', '{"a":"c"}'), +('{"a":"c"}', '{"a":["b"]}'), +('{"a": {"b":"c"}}', '{"a": {"b":"d", "c":null}}'), +('{"a":[{"b":"c"}]}', '{"a": [1]}'), +('["a","b"]', '["c","d"]'), +('{"a":"b"}', '["c"]'), +('{"a":"foo"}', 'null'), +('{"a":"foo"}', '"bar"'), +('{"e":null}', '{"a":1}'), +('[1,2]', '{"a":"b", "c":null}'), +('{}', '{"a":{"bb":{"ccc":null}}}'), +(NULL, '{}'), +('{}', NULL); +SELECT id, target, patch, +JSON_MERGE_PATCH(target, patch) AS merged, +JSON_EXTRACT(JSON_MERGE_PATCH(target, patch), '$.a') AS a +FROM merge_t ORDER BY id; +id target patch merged a +1 {"a":"b"} {"a":"c"} {"a": "c"} "c" +2 {"a":"b"} {"b":"c"} {"a": "b", "b": "c"} "b" +3 {"a":"b"} {"a":null} {} NULL +4 {"a":"b", "b":"c"} {"a":null} {"b": "c"} NULL +5 {"a":["b"]} {"a":"c"} {"a": "c"} "c" +6 {"a":"c"} {"a":["b"]} {"a": ["b"]} ["b"] +7 {"a": {"b":"c"}} {"a": {"b":"d", "c":null}} {"a": {"b": "d"}} {"b": "d"} +8 {"a":[{"b":"c"}]} {"a": [1]} {"a": [1]} [1] +9 ["a","b"] ["c","d"] ["c", "d"] NULL +10 {"a":"b"} ["c"] ["c"] NULL +11 {"a":"foo"} null null NULL +12 {"a":"foo"} "bar" "bar" NULL +13 {"e":null} {"a":1} {"e": null, "a": 1} 1 +14 [1,2] {"a":"b", "c":null} {"a": "b"} "b" +15 {} {"a":{"bb":{"ccc":null}}} {"a": {"bb": {}}} {"bb": {}} +16 NULL {} NULL NULL +17 {} NULL NULL NULL +DROP TABLE merge_t; +SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}') as exp; +exp +NULL +SELECT JSON_MERGE_PATCH(NULL, '[1,2,3]') as exp; +exp +[1, 2, 3] +SELECT JSON_MERGE_PATCH(NULL, 'a') as exp; +exp +NULL +Warnings: +Warning 4038 Syntax error in JSON text in argument 2 to function 'json_merge_patch' at position 1 +SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}') as exp; +exp +{"d": "e"} +SELECT JSON_MERGE_PATCH() as exp; +ERROR 42000: Incorrect parameter count in the call to native function 'JSON_MERGE_PATCH' +SELECT JSON_MERGE_PATCH('{}') as exp; +ERROR 42000: Incorrect parameter count in the call to native function 'JSON_MERGE_PATCH' +SELECT JSON_MERGE_PATCH('{', '[1,2,3]') as exp; +exp +NULL +Warnings: +Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_merge_patch' +SELECT JSON_MERGE_PATCH('{"a":"b"}', '[1,') as exp; +exp +NULL +Warnings: +Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_merge_patch' +# +# MDEV-22976 CAST(JSON_EXTRACT() AS DECIMAL) does not handle boolean values +# +SELECT +CAST(JSON_EXTRACT('{"x":true}', '$.x') AS DOUBLE) AS cf, +CAST(JSON_EXTRACT('{"x":true}', '$.x') AS DECIMAL) AS cd; +cf cd +1 1 +SELECT +CAST(JSON_EXTRACT('{"x":false}', '$.x') AS DOUBLE) AS cf, +CAST(JSON_EXTRACT('{"x":false}', '$.x') AS DECIMAL) AS cd; +cf cd +0 0 +# +# MDEV-24585 Assertion `je->s.cs == nice_js->charset()' failed in json_nice. +# +SELECT JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' ); +JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' ) +["x"] +# +# MDEV-18284 JSON casting using JSON_COMPACT doesn't always work +# with values from subqueries +# +CREATE TABLE json_test(a JSON, b JSON); +INSERT INTO json_test VALUES ("[1,2,3]", '{"a":"foo"}'); +SELECT * FROM json_test; +a b +[1,2,3] {"a":"foo"} +SELECT json_object("a", json_compact(a), "b", b) +FROM (SELECT * FROM json_test) AS json_test_values; +json_object("a", json_compact(a), "b", b) +{"a": [1,2,3], "b": {"a":"foo"}} +SELECT json_object("a", json_compact(a), "b", json_compact(b)) +FROM (SELECT * FROM json_test) AS json_test_values; +json_object("a", json_compact(a), "b", json_compact(b)) +{"a": [1,2,3], "b": {"a":"foo"}} +DROP TABLE json_test; +# +# 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 +# +# MDEV-19670 json escaped unicode parse error +# +SELECT json_valid('{"value":"\\ud83d\\ude0a"}'); +json_valid('{"value":"\\ud83d\\ude0a"}') +1 +SELECT json_valid('{"test": "\\ud83d\\ude0b"}'); +json_valid('{"test": "\\ud83d\\ude0b"}') +1 +# +# MDEV-19670 json escaped unicode parse error +# +SELECT JSON_VALID('{"admin\\"": null}'), '{"admin\\"": null}' + UNION +SELECT JSON_VALID('{"\\"admin": null}'), '{"\\"admin": null}' + UNION +SELECT JSON_VALID('{"\\"": null}'), '{"\\"": null}'; +JSON_VALID('{"admin\\"": null}') {"admin\"": null} +1 {"admin\"": null} +1 {"\"admin": null} +1 {"\"": null} +# +# MDEV-29188: Crash in JSON_EXTRACT +# +CREATE TABLE t1 (j JSON); +INSERT INTO t1 VALUES +('{"ID": "4", "Name": "Betty", "Age": 19}'), +('[10, 20, [30, 40]]'); +SELECT * FROM t1 WHERE JSON_EXTRACT(j, '$.Age')=19; +j +{"ID": "4", "Name": "Betty", "Age": 19} +drop table t1; +# +# MDEV-27151: JSON_VALUE() does not parse NULL properties properly +# +# +# It is correct for JSON_EXTRACT() to give null instead of "NULL" because +# it returns the json literal that is put inside json. +# Hence it should return null as in 'null' string and not SQL NULL. +# JSON_VALUE() returns the "VALUE" so it is correct for it to return SQl NULL +# +SELECT NULL; +NULL +NULL +SELECT JSON_VALUE('{"nulltest": null}', '$.nulltest'); +JSON_VALUE('{"nulltest": null}', '$.nulltest') +NULL +SELECT 1 + NULL; +1 + NULL +NULL +SELECT 1 + JSON_VALUE('{"nulltest": null}', '$.nulltest'); +1 + JSON_VALUE('{"nulltest": null}', '$.nulltest') +NULL +SELECT NULL; +NULL +NULL +SELECT JSON_EXTRACT('{"a":null, "b":10, "c":"null"}', '$.a'); +JSON_EXTRACT('{"a":null, "b":10, "c":"null"}', '$.a') +null +# +# Start of 10.4 tests +# +# +# MDEV-16351 JSON_OBJECT() treats hybrid functions with boolean arguments as numbers +# +SELECT +JSON_OBJECT("cond", true) AS j1, +JSON_OBJECT("cond", COALESCE(true, false)) j2, +JSON_OBJECT("cond", COALESCE(COALESCE(true, false))) j3; +j1 {"cond": true} +j2 {"cond": true} +j3 {"cond": true} +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT JSON_OBJECT('x',(SELECT MAX(a)=4 FROM t1)); +JSON_OBJECT('x',(SELECT MAX(a)=4 FROM t1)) +{"x": false} +SELECT JSON_OBJECT('x',(SELECT MAX(a)=3 FROM t1)); +JSON_OBJECT('x',(SELECT MAX(a)=3 FROM t1)) +{"x": true} +SELECT JSON_OBJECT('x',(SELECT MAX(a)=2 FROM t1)); +JSON_OBJECT('x',(SELECT MAX(a)=2 FROM t1)) +{"x": false} +SELECT JSON_OBJECT('x',MAX(a=4)) FROM t1; +JSON_OBJECT('x',MAX(a=4)) +{"x": false} +SELECT JSON_OBJECT('x',MAX(a=3)) FROM t1; +JSON_OBJECT('x',MAX(a=3)) +{"x": true} +SELECT JSON_OBJECT('x',MAX(a=2)) FROM t1; +JSON_OBJECT('x',MAX(a=2)) +{"x": true} +SELECT JSON_OBJECT('x',(SELECT MAX(a=4) FROM t1)); +JSON_OBJECT('x',(SELECT MAX(a=4) FROM t1)) +{"x": false} +SELECT JSON_OBJECT('x',(SELECT MAX(a=3) FROM t1)); +JSON_OBJECT('x',(SELECT MAX(a=3) FROM t1)) +{"x": true} +SELECT JSON_OBJECT('x',(SELECT MAX(a=2) FROM t1)); +JSON_OBJECT('x',(SELECT MAX(a=2) FROM t1)) +{"x": true} +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=4 FROM t1))='{"x": true}' THEN a END; +a +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=4 FROM t1))='{"x": false}' THEN a END; +a +1 +2 +3 +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=3 FROM t1))='{"x": true}' THEN a END; +a +1 +2 +3 +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=3 FROM t1))='{"x": false}' THEN a END; +a +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=2 FROM t1))='{"x": true}' THEN a END; +a +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=2 FROM t1))='{"x": false}' THEN a END; +a +1 +2 +3 +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=4) FROM t1))='{"x": true}' THEN a END; +a +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=4) FROM t1))='{"x": false}' THEN a END; +a +1 +2 +3 +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=3) FROM t1))='{"x": true}' THEN a END; +a +1 +2 +3 +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=3) FROM t1))='{"x": false}' THEN a END; +a +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{"x": true}' THEN a END; +a +1 +2 +3 +SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{"x": false}' THEN a END; +a +DROP TABLE t1; +# +# MDEV-16620 JSON_ARRAYAGG +# +# +# Integer aggregation +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1, 1),(2, 1), (1, 1),(2, 1), (3, 2),(2, 2),(2, 2),(2, 2); +SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1; +JSON_VALID(JSON_ARRAYAGG(a)) +1 +SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1; +JSON_ARRAYAGG(a) JSON_ARRAYAGG(b) +[1,2,1,2,3,2,2,2] [1,1,1,1,2,2,2,2] +SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b; +JSON_ARRAYAGG(a) JSON_ARRAYAGG(b) +[1,2,1,2] [1,1,1,1] +[3,2,2,2] [2,2,2,2] +DROP TABLE t1; +# +# Real aggregation +# +CREATE TABLE t1 (a FLOAT, b DOUBLE, c DECIMAL(10, 2)); +INSERT INTO t1 VALUES (1.0, 2.0, 3.0),(1.0, 3.0, 9.0),(1.0, 4.0, 16.0),(1.0, 5.0, 25.0); +SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1; +JSON_VALID(JSON_ARRAYAGG(a)) +1 +SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b), JSON_ARRAYAGG(c) FROM t1; +JSON_ARRAYAGG(a) JSON_ARRAYAGG(b) JSON_ARRAYAGG(c) +[1,1,1,1] [2,3,4,5] [3.00,9.00,16.00,25.00] +DROP TABLE t1; +# +# Boolean aggregation +# +CREATE TABLE t1 (a BOOLEAN, b BOOLEAN); +INSERT INTO t1 VALUES (TRUE, TRUE), (TRUE, FALSE), (FALSE, TRUE), (FALSE, FALSE); +SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1; +JSON_VALID(JSON_ARRAYAGG(a)) +1 +SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1; +JSON_ARRAYAGG(a) JSON_ARRAYAGG(b) +[1,1,0,0] [1,0,1,0] +SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b; +JSON_ARRAYAGG(a) JSON_ARRAYAGG(b) +[1,0] [0,0] +[1,0] [1,1] +SELECT JSON_ARRAYAGG(TRUE), JSON_ARRAYAGG(FALSE) FROM t1; +JSON_ARRAYAGG(TRUE) JSON_ARRAYAGG(FALSE) +[true,true,true,true] [false,false,false,false] +DROP TABLE t1; +# +# Aggregation of strings with quoted +# +CREATE TABLE t1 (a VARCHAR(80)); +INSERT INTO t1 VALUES +('"double_quoted_value"'), ("'single_quoted_value'"), +('"double_quoted_value"'), ("'single_quoted_value'"); +SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1; +JSON_VALID(JSON_ARRAYAGG(a)) +1 +SELECT JSON_ARRAYAGG(a) FROM t1; +JSON_ARRAYAGG(a) +["\"double_quoted_value\"","'single_quoted_value'","\"double_quoted_value\"","'single_quoted_value'"] +DROP TABLE t1; +# +# Strings and NULLs +# +CREATE TABLE t1 (a INT, b VARCHAR(80)); +INSERT INTO t1 VALUES +(1, "Hello"),(1, "World"), (2, "This"),(2, "Will"), (2, "Work"),(2, "!"), (3, NULL), +(1, "Hello"),(1, "World"), (2, "This"),(2, "Will"), (2, "Work"),(2, "!"), (3, NULL); +SELECT JSON_VALID(JSON_ARRAYAGG(b)) FROM t1; +JSON_VALID(JSON_ARRAYAGG(b)) +1 +SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1; +JSON_ARRAYAGG(a) JSON_ARRAYAGG(b) +[1,1,2,2,2,2,3,1,1,2,2,2,2,3] ["Hello","World","This","Will","Work","!",null,"Hello","World","This","Will","Work","!",null] +SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY a; +JSON_ARRAYAGG(a) JSON_ARRAYAGG(b) +[1,1,1,1] ["Hello","World","Hello","World"] +[2,2,2,2,2,2,2,2] ["!","Work","Will","This","Will","This","!","Work"] +[3,3] [null,null] +# +# DISTINCT and LIMIT +# +SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1; +JSON_ARRAYAGG(b LIMIT 1) +["Hello"] +SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1; +JSON_ARRAYAGG(b LIMIT 2) +["Hello","World"] +SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1 GROUP BY b; +JSON_ARRAYAGG(b LIMIT 1) +[null] +["!"] +["Hello"] +["This"] +["Will"] +["Work"] +["World"] +SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1 GROUP BY a; +JSON_ARRAYAGG(b LIMIT 2) +["Hello","World"] +["!","Work"] +[null,null] +SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1; +JSON_ARRAYAGG(DISTINCT a) +[1,2,3] +SELECT JSON_ARRAYAGG(DISTINCT b) FROM t1; +JSON_ARRAYAGG(DISTINCT b) +[null,"!","Hello","This","Will","Work","World"] +SELECT JSON_ARRAYAGG(DISTINCT a LIMIT 2) FROM t1; +JSON_ARRAYAGG(DISTINCT a LIMIT 2) +[1,2] +SELECT JSON_ARRAYAGG(DISTINCT b LIMIT 2) FROM t1; +JSON_ARRAYAGG(DISTINCT b LIMIT 2) +[null,"!"] +# +# JSON aggregation +# +SELECT JSON_VALID(JSON_ARRAYAGG(JSON_ARRAY(a, b))) FROM t1; +JSON_VALID(JSON_ARRAYAGG(JSON_ARRAY(a, b))) +1 +SELECT JSON_ARRAYAGG(JSON_ARRAY(a, b)) FROM t1; +JSON_ARRAYAGG(JSON_ARRAY(a, b)) +[[1, "Hello"],[1, "World"],[2, "This"],[2, "Will"],[2, "Work"],[2, "!"],[3, null],[1, "Hello"],[1, "World"],[2, "This"],[2, "Will"],[2, "Work"],[2, "!"],[3, null]] +SELECT JSON_ARRAYAGG(JSON_ARRAY(a, b)) FROM t1 GROUP BY a; +JSON_ARRAYAGG(JSON_ARRAY(a, b)) +[[1, "Hello"],[1, "World"],[1, "Hello"],[1, "World"]] +[[2, "!"],[2, "Work"],[2, "Will"],[2, "This"],[2, "Will"],[2, "This"],[2, "!"],[2, "Work"]] +[[3, null],[3, null]] +SELECT JSON_VALID(JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b))) FROM t1; +JSON_VALID(JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b))) +1 +SELECT JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) FROM t1; +JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) +[{"a": 1, "b": "Hello"},{"a": 1, "b": "World"},{"a": 2, "b": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "Work"},{"a": 2, "b": "!"},{"a": 3, "b": null},{"a": 1, "b": "Hello"},{"a": 1, "b": "World"},{"a": 2, "b": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "Work"},{"a": 2, "b": "!"},{"a": 3, "b": null}] +SELECT JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) FROM t1 GROUP BY a; +JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) +[{"a": 1, "b": "Hello"},{"a": 1, "b": "World"},{"a": 1, "b": "Hello"},{"a": 1, "b": "World"}] +[{"a": 2, "b": "!"},{"a": 2, "b": "Work"},{"a": 2, "b": "Will"},{"a": 2, "b": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "This"},{"a": 2, "b": "!"},{"a": 2, "b": "Work"}] +[{"a": 3, "b": null},{"a": 3, "b": null}] +# +# Error checks +# +SELECT JSON_ARRAYAGG(a, b) FROM t1; +ERROR 42000: Incorrect parameter count in the call to native function 'JSON_ARRAYAGG' +SELECT JSON_ARRAYAGG(JSON_ARRAYAGG(a, b)) FROM t1; +ERROR 42000: Incorrect parameter count in the call to native function 'JSON_ARRAYAGG' +SELECT JSON_ARRAYAGG(JSON_ARRAYAGG(a)) FROM t1; +ERROR HY000: Invalid use of group function +# +# MDEV-16620 JSON_OBJECTAGG +# +SELECT JSON_OBJECTAGG(a, b) FROM t1; +JSON_OBJECTAGG(a, b) +{"1":"Hello", "1":"World", "2":"This", "2":"Will", "2":"Work", "2":"!", "3":null, "1":"Hello", "1":"World", "2":"This", "2":"Will", "2":"Work", "2":"!", "3":null} +SELECT JSON_OBJECTAGG(a) FROM t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') FROM t1' at line 1 +DROP TABLE t1; +# +# MDEV-19160 JSON_DETAILED output unnecessarily verbose +# +create table t200 (a text); +insert into t200 values +('{ + "steps": [ + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 2e308 + }, + "potential_range_indexes": [ + { + "index": "a_b", + "usable": true, + "key_parts": ["a", "b"] + } + ], + "best_covering_index_scan": { + "index": "a_b", + "cost": 52.195, + "chosen": true + }, + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a_b", + "ranges": ["2 <= a <= 2 AND 4 <= b <= 4", "123"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": true, + "rows": 1, + "cost": 1.1752, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [], + "test_one_line_array":["123"] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a_b", + "rows": 1, + "ranges": ["2 <= a <= 2 AND 4 <= b <= 4"] + }, + "rows_for_plan": 1, + "cost_for_plan": 1.1752, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "a_b", + "selectivity_from_index": 0.001 + } + ], + "selectivity_for_columns": [], + "cond_selectivity": 0.001 + } + ] + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +}'); +select JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) as exp from t200; +exp +[ + { + "range_scan_alternatives": + [ + { + "index": "a_b", + "ranges": + [ + "2 <= a <= 2 AND 4 <= b <= 4", + "123" + ], + "rowid_ordered": true, + "using_mrr": false, + "index_only": true, + "rows": 1, + "cost": 1.1752, + "chosen": true + } + ], + "analyzing_roworder_intersect": + { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": + [], + "test_one_line_array": + ["123"] + } +] +select JSON_PRETTY(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) as exp from t200; +exp +[ + { + "range_scan_alternatives": + [ + { + "index": "a_b", + "ranges": + [ + "2 <= a <= 2 AND 4 <= b <= 4", + "123" + ], + "rowid_ordered": true, + "using_mrr": false, + "index_only": true, + "rows": 1, + "cost": 1.1752, + "chosen": true + } + ], + "analyzing_roworder_intersect": + { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": + [], + "test_one_line_array": + ["123"] + } +] +select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) as exp from t200; +exp +[{"range_scan_alternatives": [{"index": "a_b", "ranges": ["2 <= a <= 2 AND 4 <= b <= 4", "123"], "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 1.1752, "chosen": true}], "analyzing_roworder_intersect": {"cause": "too few roworder scans"}, "analyzing_index_merge_union": [], "test_one_line_array": ["123"]}] +drop table t200; +# +# MDEV-24538: JSON_LENGTH does not return error upon wrong number of parameters +# +SELECT JSON_LENGTH('{"a":"b"}','$','$', 'foo'); +ERROR 42000: Incorrect parameter count in the call to native function 'json_length' +SELECT JSON_LENGTH(); +ERROR 42000: Incorrect parameter count in the call to native function 'JSON_LENGTH' +# MDEV-23187: Assorted assertion failures in json_find_path with certain collations +SET @old_collation_connection= @@COLLATION_CONNECTION; +SET COLLATION_CONNECTION= ucs2_unicode_ci; +SELECT JSON_VALUE('["foo"]', '$**[0]') AS f; +f +foo +SET @@COLLATION_CONNECTION= @old_collation_connection; +# +# End of 10.4 tests +# +# +# MDEV-16620 JSON_ARRAYAGG +# +CREATE TABLE t1 (a INT); +SELECT JSON_ARRAYAGG(a) FROM t1; +JSON_ARRAYAGG(a) +NULL +DROP TABLE t1; +# +# MDEV-21915 Server crashes in copy_fields,Item_func_group_concat::add +while using json_arrayagg() as a window function +# +select json_arrayagg(a) over () from (select 1 a) t; +ERROR 42000: This version of MariaDB doesn't yet support 'JSON_ARRAYAGG() aggregate as window function' +select json_objectagg(a, b) over () from (select 1 a, 2 b) t; +ERROR 42000: This version of MariaDB doesn't yet support 'JSON_OBJECTAGG() aggregate as window function' +SELECT JSON_ARRAYAGG(NULL) FROM (SELECT 1 AS t) AS A; +JSON_ARRAYAGG(NULL) +[null] +SELECT JSON_ARRAYAGG("null") FROM (SELECT 1 AS t) AS A; +JSON_ARRAYAGG("null") +["null"] +create view v as (select json_arrayagg(json_object("type", "permPeriod", "id", "asd")) as JSON_DATA); +select * from v; +JSON_DATA +[{"type": "permPeriod", "id": "asd"}] +drop view v; +select json_arrayagg(a order by a asc) from (select 1 a union select 2 a) t; +json_arrayagg(a order by a asc) +[1,2] +select json_object('x', json_arrayagg(json_object('a', 1))); +json_object('x', json_arrayagg(json_object('a', 1))) +{"x": [{"a": 1}]} +# +# MDEV-22011: DISTINCT with JSON_ARRAYAGG gives wrong results +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3); +SELECT JSON_ARRAYAGG(a) FROM t1; +JSON_ARRAYAGG(a) +[1,2,3,1,2,3] +SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1; +JSON_ARRAYAGG(DISTINCT a) +[1,2,3] +INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL); +SELECT JSON_ARRAYAGG(a) FROM t1; +JSON_ARRAYAGG(a) +[1,2,3,1,2,3,null,null] +SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1; +JSON_ARRAYAGG(DISTINCT a) +[null,1,2,3] +DROP TABLE t1; +CREATE TABLE t1(a VARCHAR(10), b INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3); +SELECT JSON_ARRAYAGG(a) FROM t1; +JSON_ARRAYAGG(a) +["1","2","3","1","2","3"] +SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1; +JSON_ARRAYAGG(DISTINCT a) +["1","2","3"] +INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL); +SELECT JSON_ARRAYAGG(a) FROM t1; +JSON_ARRAYAGG(a) +["1","2","3","1","2","3",null,null] +SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1; +JSON_ARRAYAGG(DISTINCT a) +[null,"1","2","3"] +DROP TABLE t1; +# +# MDEV-22840: JSON_ARRAYAGG gives wrong results with NULL values and ORDER by clause +# +CREATE TABLE t1(a VARCHAR(255)); +INSERT INTO t1 VALUES ('red'),('blue'); +SELECT JSON_ARRAYAGG(a) FROM t1; +JSON_ARRAYAGG(a) +["red","blue"] +SELECT JSON_ARRAYAGG(a ORDER BY a DESC) FROM t1; +JSON_ARRAYAGG(a ORDER BY a DESC) +["red","blue"] +SELECT JSON_ARRAYAGG(a ORDER BY a ASC) FROM t1; +JSON_ARRAYAGG(a ORDER BY a ASC) +["blue","red"] +INSERT INTO t1 VALUES (NULL); +SELECT JSON_ARRAYAGG(a) FROM t1; +JSON_ARRAYAGG(a) +["red","blue",null] +SELECT JSON_ARRAYAGG(a ORDER BY a DESC) FROM t1; +JSON_ARRAYAGG(a ORDER BY a DESC) +["red","blue",null] +SELECT JSON_ARRAYAGG(a ORDER BY a ASC) FROM t1; +JSON_ARRAYAGG(a ORDER BY a ASC) +[null,"blue","red"] +DROP TABLE t1; +set group_concat_max_len=64; +create table t1 (a varchar(254)); +insert into t1 values (concat('x64-', repeat('a', 60))); +insert into t1 values (concat('x64-', repeat('b', 60))); +insert into t1 values (concat('x64-', repeat('c', 60))); +select json_arrayagg(a) from t1; +json_arrayagg(a) +["x64-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"] +Warnings: +Warning 1260 Row 1 was cut by JSON_ARRAYAGG() +drop table t1; +SET group_concat_max_len= default; +create table t1 (col1 json); +insert into t1 values('{"color":"red", "size":1}' ); +insert into t1 values('{"color":"blue", "size":2}' ); +select JSON_ARRAYAGG(col1) from t1; +JSON_ARRAYAGG(col1) +[{"color":"red", "size":1},{"color":"blue", "size":2}] +drop table t1; +# +# MDEV-23029: JSON_OBJECTAGG returns NULL when used together with GROUP BY +# +CREATE TABLE t1 (e INT, a VARCHAR(255), v VARCHAR(255)); +INSERT INTO t1 VALUES (0, 'a1', '1') , (0, 'a2', '2') , (1, 'b1', '3'); +EXPLAIN SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e, B.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +1 SIMPLE B ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e, B.a; +e JSON_OBJECTAGG(B.a, B.v) +0 {"a1":"1", "a1":"1", "a1":"1"} +0 {"a2":"2", "a2":"2", "a2":"2"} +1 {"b1":"3", "b1":"3", "b1":"3"} +CREATE VIEW v AS SELECT JSON_OBJECTAGG(a, e) FROM t1; +SELECT * FROM v; +JSON_OBJECTAGG(a, e) +{"a1":0, "a2":0, "b1":1} +DROP VIEW v; +DROP TABLE t1; +# +# MDEV-23004 When using GROUP BY with JSON_ARRAYAGG with joint table, the square brackets are not included. +# +CREATE TABLE t1(id int primary key, name varchar(50)); +CREATE TABLE t2(id int, owner_id int); +INSERT INTO t1 VALUES (1, "name1"), (2, "name2"), (3, "name3"); +INSERT INTO t2 VALUES (1, 1), (2, 1), (3, 2), (4, 3); +SELECT t1.id, JSON_ARRAYAGG(JSON_OBJECT('id',t2.id) ORDER BY t2.id) as materials +from t1 LEFT JOIN t2 on t1.id = t2.owner_id +GROUP BY t1.id ORDER BY id; +id materials +1 [{"id": 1},{"id": 2}] +2 [{"id": 3}] +3 [{"id": 4}] +DROP TABLE t1; +DROP TABLE t2; +# +# MDEV-27018 IF and COALESCE lose "json" property +# +SELECT json_object('a', if(1, json_object('b', 'c'), json_object('e', 'f'))) as exp; +exp +{"a": {"b": "c"}} +SELECT json_object('a', coalesce(json_object('b', 'c'))) as exp; +exp +{"a": {"b": "c"}} +# +# MDEV-26392: Crash with json_get_path_next and 10.5.12 +# +CREATE TABLE arrNestTest ( +id VARCHAR(80) AS (JSON_COMPACT(JSON_EXTRACT(doc, "$._id"))) UNIQUE KEY, +doc JSON, +CONSTRAINT id_not_null CHECK(id IS NOT NULL)); +INSERT INTO test.arrNestTest (doc) VALUES ('{ "_id" : { "$oid" : "611c0a463b150154132f6636" }, "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : 1.0 } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] }'); +SELECT * FROM arrNestTest; +id doc +{"$oid":"611c0a463b150154132f6636"} { "_id" : { "$oid" : "611c0a463b150154132f6636" }, "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : 1.0 } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } +DROP TABLE arrNestTest; +# +# MDEV-30412 JSON_OBJECTAGG doesn't escape double quote in key +# +SELECT JSON_OBJECTAGG('"', 1); +JSON_OBJECTAGG('"', 1) +{"\"":1} +SELECT JSON_OBJECTAGG('\"', 1); +JSON_OBJECTAGG('\"', 1) +{"\"":1} +SELECT JSON_OBJECTAGG('\\', 1); +JSON_OBJECTAGG('\\', 1) +{"\\":1} +# +# End of 10.5 tests +# +# +# MDEV-26054 Server crashes in Item_func_json_arrayagg::get_str_from_field +# +CREATE TABLE t (a VARCHAR(8)); +CREATE VIEW v AS SELECT * FROM t; +INSERT INTO t VALUES ('foo'),('bar'); +SELECT JSON_ARRAYAGG(a) AS f FROM v; +f +["foo","bar"] +DROP VIEW v; +DROP TABLE t; +# +# MDEV-29264 JSON functions overflow error based ON LONGTEXT field +# +CREATE TABLE t(l1 LONGTEXT, l2 LONGTEXT, l3 LONGTEXT, l4 LONGTEXT); +INSERT INTO t VALUES('k1', 'v1', 'k2', 'v2'); +SELECT JSON_ARRAY(l1, l2, l3, l4), JSON_OBJECT(l1, l2, l3, l4) from t; +JSON_ARRAY(l1, l2, l3, l4) JSON_OBJECT(l1, l2, l3, l4) +["k1", "v1", "k2", "v2"] {"k1": "v1", "k2": "v2"} +SELECT JSON_ARRAY_APPEND(JSON_ARRAY(l1, l2, l3, l4), '$[0]', 'k3'), JSON_ARRAY_INSERT(JSON_ARRAY(l1, l2, l3, l4), '$[0]', 'k3') from t; +JSON_ARRAY_APPEND(JSON_ARRAY(l1, l2, l3, l4), '$[0]', 'k3') JSON_ARRAY_INSERT(JSON_ARRAY(l1, l2, l3, l4), '$[0]', 'k3') +[["k1", "k3"], "v1", "k2", "v2"] ["k3", "k1", "v1", "k2", "v2"] +SELECT JSON_INSERT(JSON_OBJECT(l1, l2, l3, l4), '$.k3', 'v3'),JSON_SET(JSON_OBJECT(l1, l2, l3, l4), '$.k2', 'new v2'),JSON_REPLACE(JSON_OBJECT(l1, l2, l3, l4), '$.k2', 'new v2') from t; +JSON_INSERT(JSON_OBJECT(l1, l2, l3, l4), '$.k3', 'v3') JSON_SET(JSON_OBJECT(l1, l2, l3, l4), '$.k2', 'new v2') JSON_REPLACE(JSON_OBJECT(l1, l2, l3, l4), '$.k2', 'new v2') +{"k1": "v1", "k2": "v2", "k3": "v3"} {"k1": "v1", "k2": "new v2"} {"k1": "v1", "k2": "new v2"} +DROP TABLE t; +# +# End of 10.6 tests +# +# +# MDEV-31147 json_normalize does not work correctly with MSAN build +# +CREATE TABLE t1 (val JSON); +ALTER TABLE t1 ADD COLUMN normalized_json JSON AS (JSON_NORMALIZE(val)); +INSERT INTO t1 (val) VALUES ('15'); +SELECT * FROM t1; +val normalized_json +15 1.5E1 +DROP TABLE t1; +# +# End of 10.8 tests +# +# +# MDEV-27677: Implement JSON_OVERLAPS() +# +# Testing scalar json datatypes +# Comparing scalar json datatypes with itself +SELECT JSON_OVERLAPS('true', 'true'); +JSON_OVERLAPS('true', 'true') +1 +SELECT JSON_OVERLAPS('false', 'false'); +JSON_OVERLAPS('false', 'false') +1 +SELECT JSON_OVERLAPS('1', '1'); +JSON_OVERLAPS('1', '1') +1 +SELECT JSON_OVERLAPS('"string1"', '"string1"'); +JSON_OVERLAPS('"string1"', '"string1"') +1 +SELECT JSON_OVERLAPS('null', 'null'); +JSON_OVERLAPS('null', 'null') +1 +# Comparing scalar json datatypes with other scalar datatype +SELECT JSON_OVERLAPS('true', 'false'); +JSON_OVERLAPS('true', 'false') +0 +SELECT JSON_OVERLAPS('1', '"1"'); +JSON_OVERLAPS('1', '"1"') +0 +SELECT JSON_OVERLAPS('1', '0'); +JSON_OVERLAPS('1', '0') +0 +SELECT JSON_OVERLAPS('null', '0'); +JSON_OVERLAPS('null', '0') +0 +SELECT JSON_OVERLAPS('"string1"', '"string2"'); +JSON_OVERLAPS('"string1"', '"string2"') +0 +SELECT JSON_OVERLAPS('true','["abc", 1, 2, true, false]'); +JSON_OVERLAPS('true','["abc", 1, 2, true, false]') +1 +SELECT JSON_OVERLAPS('true','["abc", 1, 2, [true]]'); +JSON_OVERLAPS('true','["abc", 1, 2, [true]]') +0 +SELECT JSON_OVERLAPS('true','{"A":true}'); +JSON_OVERLAPS('true','{"A":true}') +0 +# Testing non-scalar json data types +# Comparing object with object (non-nested) +SELECT JSON_OVERLAPS('{"A":[1, 2, 3]}','{}'); +JSON_OVERLAPS('{"A":[1, 2, 3]}','{}') +0 +SELECT JSON_OVERLAPS('{"A": 1}', +'{"A": 1}'); +JSON_OVERLAPS('{"A": 1}', +'{"A": 1}') +1 +SELECT JSON_OVERLAPS('{"A": 1}', +'{"B": 1}'); +JSON_OVERLAPS('{"A": 1}', +'{"B": 1}') +0 +SELECT JSON_OVERLAPS('{ + "A": 1, + "B": "string1" + }', +'{ + "A": 2, + "B": "string1" + }') as exp; +exp +1 +SELECT JSON_OVERLAPS('{ + "A": 1, + "B": "string1" + }', +'{ + "A": 2, + "B": "string2" + }') as exp; +exp +0 +# Comparing nested object with other nested object +SELECT JSON_OVERLAPS('{ + "A": 1, + "B": {"C":2} + }', +'{ + "A": 2, + "B": {"C":1} + }') as exp; +exp +0 +SELECT JSON_OVERLAPS('{ + "A": 1, + "B": {"C":2} + }', +'{ + "A": 2, + "B": {"C":2} + }') as exp; +exp +1 +SELECT JSON_OVERLAPS('{ + "A": { + "B": true + } + }', +'{ + "A": { + "B": true, + "C": false + } + }') as exp; +exp +0 +SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}', +'{"C":3, "B":{"E":5, "D":4}}') as exp; +exp +1 +SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', +'{"C":3, "B":{"E":5, "D":4}}') as exp; +exp +0 +SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', +'{"C":3, "B":{"E":[5, 6, 7], "D":4}}') as exp; +exp +1 +SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', +'{"C":3, "B":{"E":[7, 6 ,5], "D":4}}') as exp; +exp +0 +SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', +'{"C":3, "F":{"E":[5, 6, 7], "D":4}}') as exp; +exp +0 +# Comparing array with array (non-nested) +SELECT JSON_OVERLAPS('[1, 2, true, false, null]', +'[3, 4, 1]') as exp; +exp +1 +SELECT JSON_OVERLAPS('[1, 2, true, false, null]', +'[3, 4, 5]'); +JSON_OVERLAPS('[1, 2, true, false, null]', +'[3, 4, 5]') +0 +SELECT JSON_OVERLAPS('[1,2,3]','[]') as exp; +exp +0 +# Comparing nested arrays +SELECT JSON_OVERLAPS('[1, 2, true, false, null]', +'[3, 4, [1]]') as exp; +exp +0 +SELECT JSON_OVERLAPS('[1, 2, [true, false], null]', +'[[1], [true, false]]') as exp; +exp +1 +SELECT JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]') as exp; +exp +0 +# Comparing one non-scalar json datatypes with another non-scalar +# json datatype +# Comparing array with object +SELECT JSON_OVERLAPS('[1, 2, true, false, null]', +'{"A": 1}') as exp; +exp +0 +SELECT JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]', +'{"A": 1}') as exp; +exp +0 +SELECT JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]', +'{"A": 1}') as exp; +exp +1 +SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]', +'{"A": 1, "B": 2}') as exp; +exp +1 +SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]', +'{"A": 1, "B": 3}') as exp; +exp +0 +# Comparing nested array with object +SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', +'{"A": 1, "B": 2}') as exp; +exp +0 +SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', +'{"A": 1, "B": 3}') as exp; +exp +0 +SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', +'{"A": 1}') as exp; +exp +0 +# Comparing array with nested object +SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]', +'{"A": 1, "B": {"C": 12}}') as exp; +exp +1 +SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]', +'{"A": 1, "B": {"C": 12}}') as exp; +exp +0 +# Comparing nested array with nested objects +SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]', +'{"A": 1, "B":{"C": 12}}') as exp; +exp +0 +SELECT JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]', +'{"A": 1, "B": {"C": 12}}') as exp; +exp +0 +# Comparing object with array +SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', +'[1, 2, true, false, {"A": 1, "B": 2}]') as exp; +exp +0 +SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', +'[1, 2, true, false, {"A": 1, "B": 3}]') as exp; +exp +1 +SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', +'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]') as exp; +exp +1 +SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, 3]}', +'[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]') as exp; +exp +1 +SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}', +'[1, 2, true, false, {"A": 1, "B": 2}, {"A":1, "B":[1, 2, {"C": 3, "D": 5}]}]') as exp; +exp +1 +SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}', +'[1, 2, true, false, {"A": 1, "B": 2},{"A": 1, "B": [1, 2, {"C": 3, "D": 4}]}]') as exp; +exp +0 +# Comparing object with nested array +SELECT JSON_OVERLAPS('{"A": 1, "B": 3}','[1, 2, true, false, [{"A": 1, "B": 2}, {"A": 1, "B": 3}]]') as exp; +exp +0 +# Checking errors and warnings +SELECT JSON_OVERLAPS('[1,2,{"A":B}]', '{"A":B}', '{"C":"string1"}'); +ERROR 42000: Incorrect parameter count in the call to native function 'JSON_OVERLAPS' +SELECT JSON_OVERLAPS('[1,2,{"A":B}]'); +ERROR 42000: Incorrect parameter count in the call to native function 'JSON_OVERLAPS' +# +# MDEV-27990: Incorrect behavior of JSON_OVERLAPS() on warning +# +SELECT JSON_OVERLAPS('',''); +JSON_OVERLAPS('','') +0 +Warnings: +Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_overlaps' +SELECT JSON_OVERLAPS('true','tr'); +JSON_OVERLAPS('true','tr') +0 +Warnings: +Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_overlaps' +# +# MDEV-22224: Support JSON Path negative index +# +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, 13, {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_ARRAY_APPEND(@json, '$.A[-2][-1]', 5); +JSON_ARRAY_APPEND(@json, '$.A[-2][-1]', 5) +{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1": "value1"}, [15, 5]], true], "B": {"C": 1}, "D": 2} +SELECT JSON_ARRAY_APPEND(@json, '$.A[last-1][last]', 5); +JSON_ARRAY_APPEND(@json, '$.A[last-1][last]', 5) +{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1": "value1"}, [15, 5]], true], "B": {"C": 1}, "D": 2} +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, 13, {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_ARRAY_INSERT(@json, '$.A[-2][-2]', 5); +JSON_ARRAY_INSERT(@json, '$.A[-2][-2]', 5) +{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1": "value1"}, 5, [15]], true], "B": {"C": 1}, "D": 2} +SELECT JSON_ARRAY_INSERT(@json, '$.A[last-1][last-1]', 5); +JSON_ARRAY_INSERT(@json, '$.A[last-1][last-1]', 5) +{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1": "value1"}, 5, [15]], true], "B": {"C": 1}, "D": 2} +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, 13, {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_CONTAINS(@json, '15', '$.A[-2][-1]'); +JSON_CONTAINS(@json, '15', '$.A[-2][-1]') +1 +SELECT JSON_CONTAINS(@json, '15', '$.A[last-1][last]'); +JSON_CONTAINS(@json, '15', '$.A[last-1][last]') +1 +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, 13, {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A[-2]'); +JSON_CONTAINS_PATH(@json, 'one', '$.A[-2]') +1 +SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A[last-1]'); +JSON_CONTAINS_PATH(@json, 'one', '$.A[last-1]') +1 +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, 13, {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_EXISTS(@json, '$.A[-2][-1]'); +JSON_EXISTS(@json, '$.A[-2][-1]') +1 +SELECT JSON_EXISTS(@json, '$.A[last-1][last]'); +JSON_EXISTS(@json, '$.A[last-1][last]') +1 +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, [13, 14], {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_EXTRACT(@json, '$.A[-8][1]'); +JSON_EXTRACT(@json, '$.A[-8][1]') +5 +SELECT JSON_EXTRACT(@json, '$.A[last-7][1]'); +JSON_EXTRACT(@json, '$.A[last-7][1]') +5 +SET @json= '[{"A": 1, "B": 2, "C": {"D": 3}},{"A": 1, "B": 2, "C": {"D": 3}}]'; +SELECT JSON_KEYS(@json, '$[-1].C'); +JSON_KEYS(@json, '$[-1].C') +["D"] +SELECT JSON_KEYS(@json, '$[last].C'); +JSON_KEYS(@json, '$[last].C') +["D"] +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, [13, 14], {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_LENGTH(@json, '$.A[-2][-3]'); +JSON_LENGTH(@json, '$.A[-2][-3]') +2 +SELECT JSON_LENGTH(@json, '$.A[last-1][last-2]'); +JSON_LENGTH(@json, '$.A[last-1][last-2]') +2 +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, [13, 14], {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_QUERY(@json, '$.A[-8]'); +JSON_QUERY(@json, '$.A[-8]') +[4, 5, 6] +SELECT JSON_QUERY(@json, '$.A[last-7]'); +JSON_QUERY(@json, '$.A[last-7]') +[4, 5, 6] +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, [13, 14], {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_REMOVE(@json, '$.A[-10]'); +JSON_REMOVE(@json, '$.A[-10]') +{"A": [[1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], true], "B": {"C": 1}, "D": 2} +SELECT JSON_REMOVE(@json, '$.A[last-9]'); +JSON_REMOVE(@json, '$.A[last-9]') +{"A": [[1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], true], "B": {"C": 1}, "D": 2} +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, [13, 14], {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_REPLACE(@json, '$.A[-1]', 4); +JSON_REPLACE(@json, '$.A[-1]', 4) +{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], 4], "B": {"C": 1}, "D": 2} +SELECT JSON_REPLACE(@json, '$.A[last]', 4); +JSON_REPLACE(@json, '$.A[last]', 4) +{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], 4], "B": {"C": 1}, "D": 2} +SET @json = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]'; +SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[-2]'); +JSON_SEARCH(@json, 'all', 'abc', NULL, '$[-2]') +"$[2].x" +SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[last-1]'); +JSON_SEARCH(@json, 'all', 'abc', NULL, '$[last-1]') +"$[2].x" +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, [13, 14], {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_SET(@json, '$.A[-4]', 100); +JSON_SET(@json, '$.A[-4]', 100) +{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, 100, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], true], "B": {"C": 1}, "D": 2} +SELECT JSON_SET(@json, '$.A[last-3]', 100); +JSON_SET(@json, '$.A[last-3]', 100) +{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, 100, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], true], "B": {"C": 1}, "D": 2} +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, [13, 14], {"key1":123},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_VALUE(@json, '$.A[-2][-2].key1'); +JSON_VALUE(@json, '$.A[-2][-2].key1') +123 +SELECT JSON_VALUE(@json, '$.A[last-1][last-1].key1'); +JSON_VALUE(@json, '$.A[last-1][last-1].key1') +123 +# +# MDEV-27972: Unexpected behavior with negative zero (-0) in JSON Path +# +SET @json='{ "x": [0,1]}'; +SELECT JSON_VALUE(@json,'$.x[last]'); +JSON_VALUE(@json,'$.x[last]') +1 +SELECT JSON_VALUE(@json,'$.x[last-0]'); +JSON_VALUE(@json,'$.x[last-0]') +1 +SELECT JSON_VALUE(@json,'$.x[-0]'); +JSON_VALUE(@json,'$.x[-0]') +0 +SELECT JSON_VALUE(@json,'$.x[0]'); +JSON_VALUE(@json,'$.x[0]') +0 +# +# MDEV-27911: Implement range notation for json path +# +SET @json= '[ + [1, {"key1": "value1"}, 3], + [false, 5, 6], + [7, 8, [9, {"key2": 2}, 11]], + [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]], + [19, 20], + 21, 22 + ]'; +SELECT JSON_EXISTS(@json, '$[3][3][-2 to last]'); +JSON_EXISTS(@json, '$[3][3][-2 to last]') +1 +SET @json= '[ + [1, {"key1": "value1"}, 3], + [false, 5, 6], + [7, 8, [9, {"key2": 2}, 11]], + [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]], + [19, 20], + 21, 22 + ]'; +SELECT JSON_SEARCH(@json, 'one', '12', NULL, '$[3][0 to 3]'); +JSON_SEARCH(@json, 'one', '12', NULL, '$[3][0 to 3]') +NULL +SET @json= '[ + [1, {"key1": "value1"}, 3], + [false, 5, 6], + [7, 8, [9, {"key2": 2}, 11]], + [12, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]], + [19, 20] + ]'; +SELECT JSON_VALUE(@json, '$[0][1 to 2].key1'); +JSON_VALUE(@json, '$[0][1 to 2].key1') +value1 +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, [13, 14], {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_QUERY(@json, '$.A[-2][-3 to -1]'); +JSON_QUERY(@json, '$.A[-2][-3 to -1]') +[13, 14] +SET @json= '[ + [1, {"key1": "value1"}, 3], + [false, 5, 6], + [7, 8, [9, {"key2": 2}, 11]], + [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]], + [19, 20], + 21, 22 + ]'; +SELECT JSON_EXTRACT(@json, '$[0 to 3][2]'); +JSON_EXTRACT(@json, '$[0 to 3][2]') +[3, 6, [9, {"key2": 2}, 11], [14]] +SELECT JSON_EXTRACT(@json, '$[3][3][last-1 to last]'); +JSON_EXTRACT(@json, '$[3][3][last-1 to last]') +["string1", [16, {"key1": [1, 2, 3, [4, 5, 6]]}, 18]] +SELECT JSON_EXTRACT(@json, '$[3][3][-2 to -1]'); +JSON_EXTRACT(@json, '$[3][3][-2 to -1]') +["string1", [16, {"key1": [1, 2, 3, [4, 5, 6]]}, 18]] +# Checking errors +SET @json= '[ + [1, {"key1": "value1"}, 3], + [false, 5, 6], + [7, 8, [9, {"key2": 2}, 11]], + [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]], + [19, 20], + 21, 22 + ]'; +SELECT JSON_CONTAINS_PATH(@json,'one', '$[3][0 to 3]'); +JSON_CONTAINS_PATH(@json,'one', '$[3][0 to 3]') +1 +SET @json= '[ + [1, {"key1": "value1"}, 3], + [false, 5, 6], + [7, 8, [9, {"key2": 2}, 11]], + [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]], + [19, 20], + 21, 22 + ]'; +SELECT JSON_CONTAINS(@json, '$[3][0 to 3]'); +JSON_CONTAINS(@json, '$[3][0 to 3]') +NULL +Warnings: +Warning 4038 Syntax error in JSON text in argument 2 to function 'json_contains' at position 1 +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, 13, {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_ARRAY_INSERT(@json, '$.A[0 to last-1]', 5); +JSON_ARRAY_INSERT(@json, '$.A[0 to last-1]', 5) +NULL +Warnings: +Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_array_insert' +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, 13, {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_ARRAY_APPEND(@json, '$.A[*]', 7); +JSON_ARRAY_APPEND(@json, '$.A[*]', 7) +NULL +Warnings: +Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_array_append' +SET @json= '[ + [1, {"key1": "value1"}, 3], + [false, 5, 6], + [7, 8, [9, {"key2": 2}, 11]], + [12, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]], + [19, 20] + ]'; +SELECT JSON_SET(@json, '$[0][1 to 2].key1', 1); +JSON_SET(@json, '$[0][1 to 2].key1', 1) +NULL +Warnings: +Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_set' +SET @json= '[ + [1, {"key1": "value1"}, 3], + [false, 5, 6], + [7, 8, [9, {"key2": 2}, 11]], + [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]], + [19, 20], + 21, 22 + ]'; +SELECT JSON_REPLACE(@json, '$[1][last-2 to last]', 4); +JSON_REPLACE(@json, '$[1][last-2 to last]', 4) +NULL +Warnings: +Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_replace' +SET @json= '[ + [1, {"key1": "value1"}, 3], + [false, 5, 6], + [7, 8, [9, {"key2": 2}, 11]], + [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]], + [19, 20], + 21, 22 + ]'; +SELECT JSON_REMOVE(@json, '$[1][-6 to last-2]'); +JSON_REMOVE(@json, '$[1][-6 to last-2]') +NULL +Warnings: +Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_remove' +SET @json='{ + "A": [0, + [1, 2, 3], + [4, 5, 6], + "seven", + 0.8, + true, + false, + "eleven", + [12, [13, 14], {"key1":"value1"},[15]], + true], + "B": {"C": 1}, + "D": 2 + }'; +SELECT JSON_KEYS(@json, '$.A[8][1 to 3]'); +JSON_KEYS(@json, '$.A[8][1 to 3]') +NULL +Warnings: +Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_keys' +# +# MDEV-28075: JSON_VALUE returns first value from array not from range +# +SET @json1= '[ + [{"key1": "value1"}, {"key2": "value2"}], + [{"key3": "value3"}, {"key1": "value4"}], + [{"key1": "value5"}, {"key4": "value6"}, {"key1": "value7"}] + ]'; +SELECT JSON_VALUE(@json1, '$[2][1 to 2].key1'); +JSON_VALUE(@json1, '$[2][1 to 2].key1') +value7 +SET @json= '[ + [1.1, {"key1": "value1"}, 3], + [false, 5, 6], + [7, 8, [9, {"key2": 2}, 11]], + [11, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]], + [19, 20] + ]'; +SELECT JSON_VALUE(@json, '$[*][0]'); +JSON_VALUE(@json, '$[*][0]') +1.1 +SELECT JSON_VALUE(@json, '$[2 to 3][0]'); +JSON_VALUE(@json, '$[2 to 3][0]') +7 +# +# MDEV-28072: JSON_EXTRACT has inconsistent behavior with '0' value in +# json path (when range is used) +# +SET @json= '[ 11, 22 , 33]'; +SELECT JSON_EXTRACT(@json, '$[0 to 0]'); +JSON_EXTRACT(@json, '$[0 to 0]') +[11] +SELECT JSON_EXTRACT(@json, '$[0 to -0]'); +JSON_EXTRACT(@json, '$[0 to -0]') +[11] +SELECT JSON_EXTRACT(@json, '$[-0 to 0]'); +JSON_EXTRACT(@json, '$[-0 to 0]') +[11] +SELECT JSON_EXTRACT(@json, '$[-0 to -0]'); +JSON_EXTRACT(@json, '$[-0 to -0]') +[11] +# +# MDEV-28071: JSON_EXISTS returns always 1 if it is used range notation +# for json path +# +SET @json= '[ + [1, {"key1": "value1"}, 3], + [false, 5, 6], + [7, 8, [9, {"key2": 2}, 11]], + [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]], + [19, 20], + 21, 22 + ]'; +SELECT JSON_EXISTS(@json, '$[2][2][1 to 2]'); +JSON_EXISTS(@json, '$[2][2][1 to 2]') +1 +SELECT JSON_EXISTS(@json, '$[2][2][4 to 6]'); +JSON_EXISTS(@json, '$[2][2][4 to 6]') +0 +SELECT JSON_EXISTS(@json, '$[2][2][1 to 4]'); +JSON_EXISTS(@json, '$[2][2][1 to 4]') +1 +# +# MDEV-28326: Server crashes in json_path_parts_compare +# +SELECT * FROM JSON_TABLE('{"foo":["bar","qux"]}','$**.*[0]' COLUMNS(col1 CHAR(8) PATH '$[0]')) AS jt; +col1 +bar +# +# MDEV-29212: json_overlaps() does not check nested key-value pair correctly +# +SET @json1 = '{"kk":{"k1":"v1","k2":"v2"}}'; +SET @json2 = '{"kk":{"k1":"v1","k2":"v2","k3":"v3"}}'; +SELECT JSON_OVERLAPS(@json2, @json1); +JSON_OVERLAPS(@json2, @json1) +0 +SELECT JSON_OVERLAPS(@json1, @json2); +JSON_OVERLAPS(@json1, @json2) +0 +# +# MDEV-30304: Json Range only affects first row of the result set +# +CREATE TABLE t1 ( j JSON ); +INSERT INTO t1 (j) VALUES ('[{"key1": 1, "key2": 1}, {"key3": 1, "key4": 1}]'); +INSERT INTO t1 (j) VALUES ('[{"key1": 2, "key2": 2}, {"key3": 2, "key4": 2}, {"key5": 2, "key6": 2}]'); +INSERT INTO t1 (j) VALUES ('[{"key1": 3, "key2": 3}, {"key3": 3, "key4": 3}, {"key5": 3}]'); +SELECT JSON_EXTRACT(j, '$[0 to 1]') FROM t1 ; +JSON_EXTRACT(j, '$[0 to 1]') +[{"key1": 1, "key2": 1}, {"key3": 1, "key4": 1}] +[{"key1": 2, "key2": 2}, {"key3": 2, "key4": 2}] +[{"key1": 3, "key2": 3}, {"key3": 3, "key4": 3}] +SELECT JSON_EXTRACT(j, '$[*]') FROM t1 ; +JSON_EXTRACT(j, '$[*]') +[{"key1": 1, "key2": 1}, {"key3": 1, "key4": 1}] +[{"key1": 2, "key2": 2}, {"key3": 2, "key4": 2}, {"key5": 2, "key6": 2}] +[{"key1": 3, "key2": 3}, {"key3": 3, "key4": 3}, {"key5": 3}] +DROP TABLE t1; +# +# MDEV-29381: JSON paths containing dashes are reported as syntax errors in procedures +# +SELECT JSON_EXTRACT('{ "my-key": 1 }', '$."my-key"'); +JSON_EXTRACT('{ "my-key": 1 }', '$."my-key"') +1 +SELECT JSON_EXTRACT('{ "my-key": 1 }', '$.my-key'); +JSON_EXTRACT('{ "my-key": 1 }', '$.my-key') +1 +# +# MDEV-23187: Assorted assertion failures in json_find_path with certain collations +# +SET @save_collation_connection= @@collation_connection; +SET @json='{ "A": [ [{"k":"v"},[1]],true],"B": {"C": 1} }'; +SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1'); +JSON_VALUE(@json,'$.A[last-1][last-1].key1') +NULL +SET @json='{ "A": [ [{"k":"v"},[1]],true],"B": {"C": 1} }'; +SET collation_connection='ucs2_bin'; +SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1'); +JSON_VALUE(@json,'$.A[last-1][last-1].key1') +NULL +SET @json='{ "A": [ [{"k":"v"},[15]],true],"B": {"C": 1} }'; +SET sql_mode=0,character_set_connection=utf32; +SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1'); +JSON_VALUE(@json,'$.A[last-1][last-1].key1') +NULL +SET @json='{ "A": [ [{"k":"v"},[15]],true],"B": {"C": 1} }'; +SET sql_mode=0,character_set_connection=utf32; +SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1'); +JSON_VALUE(@json,'$.A[last-1][last-1].key1') +NULL +SET @@collation_connection= @save_collation_connection; +# +# End of 10.9 Test +# -- cgit v1.2.3