select json_valid('[1, 2]'); select json_valid('"string"}'); select json_valid('{"key1":1, "key2":[2,3]}'); select json_valid('[false, true, null]'); select json_valid(repeat('[', 1000)); select json_valid(repeat('{"a":', 1000)); select json_value('{"key1":123}', '$.key2'); select json_value('{"key1":123}', '$.key1'); select json_value('{"key1":[1,2,3]}', '$.key1'); select json_value('{"key1": [1,2,3], "key1":123}', '$.key1'); select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z') as exp; select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2'); select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1'); select json_query('{"key1": 1}', '$.key1'); select json_query('{"key1":123, "key1": [1,2,3]}', '$.key1'); select json_query('{"key1":123, "key1": [1,2,3]}', concat('$', repeat('.k', 1000))) as exp; select json_array(); select json_array(1); #enable after fix MDEV-28649 --disable_view_protocol select json_array(1, "text", false, null); select json_array_append('["a", "b"]', '$', FALSE); --enable_view_protocol select json_array_append('{"k1":1, "k2":["a", "b"]}', '$.k2', 2); select json_array_append('["a", ["b", "c"], "d"]', '$[0]', 2); select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x'); select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2]', 'x'); select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[3]', 'x'); select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[4]', 'x'); select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x') as exp; select json_array_insert('true', '$', 1); select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2][1]', 'y') as exp; select json_contains('{"k1":123, "k2":345}', '123', '$.k1'); select json_contains('"you"', '"you"'); select json_contains('"youth"', '"you"'); --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT select json_contains('[1]', '[1]', '$', '$[0]'); select json_contains('', '', '$'); select json_contains('null', 'null', '$'); select json_contains('"10"', '"10"', '$'); select json_contains('"10"', '10', '$'); select json_contains('10.1', '10', '$'); select json_contains('10.0', '10', '$'); select json_contains('[1]', '1'); select json_contains('[2, 1]', '1'); select json_contains('[2, [2, 3], 1]', '1'); select json_contains('[4, [2, 3], 1]', '2'); select json_contains('[2, 1]', '[1, 2]'); select json_contains('[2, 1]', '[1, 0, 2]'); select json_contains('[2, 0, 3, 1]', '[1, 2]'); select json_contains('{"b":[1,2], "a":1}', '{"a":1, "b":2}'); select json_contains('{"a":1}', '{}'); select json_contains('[1, {"a":1}]', '{}'); select json_contains('[1, {"a":1}]', '{"a":1}'); select json_contains('[{"abc":"def", "def":"abc"}]', '["foo","bar"]'); select json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]') as exp; select json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}'); select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]") as exp; select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[10]") as exp; select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.ma") as exp; select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1") as exp; select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1", "$.ma") as exp; select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.ma") as exp; select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.key2") as exp; select json_contains_path('{ "a": true }', NULL, '$.a' ) as exp; select json_contains_path('{ "a": true }', 'all', NULL ) as exp; select json_contains_path('{"a":{"b":"c"}}', 'one', '$.a.*') as exp; select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1") as exp; select json_extract('{"key1":"asd", "key2":[2,3]}', "$.keyX", "$.keyY") as exp; select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2") as exp; select json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2") as exp; select json_extract('{"key0":true, "key1":"qwe"}', "$.key1") as exp; select json_extract(json_object('foo', 'foobar'),'$') as exp; select json_extract('[10, 20, [30, 40]]', '$[2][*]') as exp; select json_extract('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]') as exp; select json_extract('1', '$') as exp; select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]') as exp; select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]') as exp; select json_extract( '[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a') as exp; #enable after MDEV-32454 fix --disable_view_protocol select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word') as exp; select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3) as exp; select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2) as exp; select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word') as exp; select json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') as exp; --enable_view_protocol select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') as exp; select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]') as exp; set @j = '["a", ["b", "c"], "d"]'; select json_remove(@j, '$[0]'); select json_remove(@j, '$[1]'); select json_remove(@j, '$[2]'); set @j = '{"a": 1, "b": [2, 3]}'; select json_remove(@j, '$.b'); select json_remove(@j, '$.a'); select json_object(); select json_object("ki", 1, "mi", "ya"); create table t1 as select json_object('id', 87, 'name', 'carrot') as f; show create table t1; select * from t1; drop table t1; select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") as ex; select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]") as ex; select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]") as ex; select json_quote('"string"'); create table t1 as select json_quote('foo'); select * from t1; show create table t1; drop table t1; --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT select json_merge('string'); select json_merge('string', 123); select json_merge('"string"', 123); select json_merge('[1, 2]', '[true, false]'); select json_merge('{"1": 2}', '{"true": false}'); select json_merge('{"1": 2}', '{"true": false}', '{"3": 4}'); select json_merge(NULL,json_object('foo', 1)); select json_merge('a','b'); select json_merge('{"a":"b"}','{"c":"d"}'); SELECT JSON_MERGE('[1, 2]', '{"id": 47}'); #enable after MDEV-32454 fix --disable_view_protocol select json_type('{"k1":123, "k2":345}'); select json_type('[123, "k2", 345]'); select json_type("true"); select json_type('123'); select json_type('123.12'); --enable_view_protocol select json_keys('{"a":{"c":1, "d":2}, "b":2}'); select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.a"); select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.b"); select json_keys('foo'); # # mdev-12789 JSON_KEYS returns duplicate keys twice # select json_keys('{"a":{"c":1, "d":2}, "b":2, "c":1, "a":3, "b":1, "c":2}') as ex; select json_keys('{"c1": "value 1", "c1": "value 2"}') as ex; SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]'; select json_search(@j, 'one', 'abc') as ex; select json_search(@j, 'all', 'abc') as ex; select json_search(@j, 'all', 'abc', NULL, '$[2]') as ex; select json_search(@j, 'all', 'abc', NULL, '$') as ex; select json_search(@j, 'all', '10', NULL, '$') as ex; select json_search(@j, 'all', '10', NULL, '$[*]') as ex; select json_search(@j, 'all', '10', NULL, '$[*][0].k') as ex; select json_search(@j, 'all', '10', NULL, '$**.k') as ex; 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; drop table t1; #enable after MDEV-32454 fix --disable_view_protocol select json_unquote('"abc"'); select json_unquote('abc'); --enable_view_protocol # # MDEV-13703 Illegal mix of collations for operation 'json_object' on using JSON_UNQUOTE as an argument. # 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; drop table t1; #enable after MDEV-32454 fix --disable_view_protocol select json_object("a", json_object("b", "abcd")); select json_object("a", '{"b": "abcd"}'); select json_object("a", json_compact('{"b": "abcd"}')); --enable_view_protocol select json_compact(NULL); select json_depth(json_compact(NULL)); select json_depth('[[], {}]'); select json_depth('[[[1,2,3],"s"], {}, []]'); select json_depth('[10, {"a": 20}]'); select json_length(''); select json_length('{}'); select json_length('[1, 2, {"a": 3}]'); select json_length('{"a": 1, "b": {"c": 30}}', '$.b'); select json_length('{"a": 1, "b": {"c": 30}}'); select json_length('{}{'); create table json (j INT); show create table json; drop table json; select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' ) as ex; select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' ) as ex; select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0]' ) as ex; select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0][0]' ) as ex; select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2]' ) as ex; select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0]' ) as ex; select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0]' ) as ex; select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0][0]' ) as ex; select json_length( '{"a":{"b":{"d":1}}, "a":{"c":{"d":1, "j":2}}}', '$.a[0][0][0].c' ) as ex; select json_set('1', '$[0]', 100); select json_set('1', '$[0][0]', 100); select json_set('1', '$[1]', 100); select json_set('{"a":12}', '$[0]', 100); select json_set('{"a":12}', '$[0].a', 100); select json_set('{"a":12}', '$[0][0].a', 100); select json_set('{"a":12}', '$[0][1].a', 100); select json_value('{"\\"key1":123}', '$."\\"key1"') as ex; select json_value('{"\\"key1\\"":123}', '$."\\"key1\\""') as ex; select json_value('{"key 1":123}', '$."key 1"') as ex; select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]") as ex; select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[3]") as ex; select json_extract( '[1]', '$[0][0]' ); select json_extract( '[1]', '$[1][0]' ); select json_extract( '[1]', '$**[0]' ); select json_extract( '[1]', '$**[0][0]' ); select json_insert('1', '$[0]', 4); select json_replace('1', '$[0]', 4); select json_set('1', '$[0]', 4); select json_set('1', '$[1]', 4); select json_replace('1', '$[1]', 4); SELECT json_insert('[]', '$[0][0]', 100); SELECT json_insert('1', '$[0][0]', 100); SELECT json_replace('1', '$[0][0]', 100); SELECT json_replace('[]', '$[0][0]', 100); SELECT json_set('[]', '$[0][0]', 100); SELECT json_set('[]', '$[0][0][0]', 100); # # MDEV-11857 json_search() shows "Out of memory" with empty key. # SELECT JSON_search( '{"": "a"}', "one", 'a'); # # MDEV-11858 json_merge() concatenates instead of merging. # select json_merge('{"a":"b"}', '{"a":"c"}') as ex ; select json_merge('{"a":{"x":"b"}}', '{"a":"c"}') as ex ; select json_merge('{"a":{"u":12, "x":"b"}}', '{"a":{"x":"c"}}') as ex ; select json_merge('{"a":{"u":12, "x":"b", "r":1}}', '{"a":{"x":"c", "r":2}}') as ex ; select json_compact('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') as ex; #enable after MDEV-32454 fix --disable_view_protocol select json_loose('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') as ex; select json_detailed('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') as ex; --enable_view_protocol # # MDEV-11856 json_search doesn't search for values with double quotes character (") # SELECT JSON_search( '{"x": "\\""}', "one", '"') as ex; SELECT JSON_search( '{"x": "\\""}', "one", '\\"') as ex; # # MDEV-11833 JSON functions don't seem to respect max_allowed_packet. # 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'; show variables like 'max_allowed_packet'; select json_array(repeat('a',1024),repeat('a',1024)) as ex; select json_object("a", repeat('a',1024),"b", repeat('a',1024)) as ex; --connection default set @@global.max_allowed_packet = @save_max_allowed_packet; set @@global.net_buffer_length = @save_net_buffer_length; --disconnect newconn # # MDEV-12262 Assertion `!null_value' failed in virtual bool Item::send on JSON_REMOVE. # 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; drop table t1; # # MDEV-12364 Server crashes in __memcpy_sse2_unaligned / String::copy on JSON_SEARCH with variables. # SET @str = 'bar', @path = '$'; SELECT JSON_SEARCH('{"foo":"bar"}', 'all' , @str, '%', @path); # # MDEV-12351 Assertion `cur_step->type & JSON_PATH_KEY' failed in json_find_path. # SELECT JSON_VALUE('[{"foo": 1},"bar"]', '$[*][0]'); # # MDEV-12363 Assertion `0' failed in Type_handler_string_result::make_sort_key(uchar*, Item*, const SORT_FIELD_ATTR*, Sort_param*) # CREATE TABLE t1 (f INT NOT NULL); INSERT INTO t1 VALUES (0); SELECT JSON_KEYS(f) FROM t1 ORDER BY 1; DROP TABLE t1; # # MDEV-12324 Wrong result (phantom array value) on JSON_EXTRACT. # SELECT JSON_EXTRACT( '{"foo":"bar"}', '$[*].*' ); SELECT JSON_EXTRACT( '{"foo":"bar"}', '$[*]' ); # # MDEV-12604 Comparison of JSON_EXTRACT result differs with Mysql. # select JSON_EXTRACT('{"name":"value"}', '$.name') = 'value' as ex; select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = true as ex; select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = false as ex; select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = 1 as ex; select JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"') as ex; # # MDEV-129892 JSON_EXTRACT returns data for invalid JSON # select JSON_EXTRACT('{"foo": "bar" foobar foo invalid ', '$.foo') as ex; # # MDEV-13138 JSON_OBJECT returns null with strings containing backticks. # SELECT JSON_OBJECT('foo', '`') as ex; SELECT JSON_OBJECT("foo", "bar`bar") as ex; # # MDEV-13324 JSON_SET returns NULL instead of object. # #enable after MDEV-32454 fix --disable_view_protocol SELECT JSON_SET('{}', '$.age', 87); --enable_view_protocol # # MDEV-13104 Json functions. # SELECT JSON_MERGE('[]', '{"c":"d"}'); # # MDEV-12774 JSON_EXTRACT fails with some escaped unicode as key. # SET @str = "{\"\\u00e4\\u00f6\":\"yes\"}"; SET @path = "$.\"\\u00e4\\u00f6\""; select @str, @path, JSON_EXTRACT(@str, @path); SET @str = "{\"\\u00e4\":\"yes\"}"; SET @path = "$.\"\\u00e4\""; select @str, @path, JSON_EXTRACT(@str, @path); # # MDEV-12877 Wrong result from JSON native function. # select json_array(5,json_query('[1,2]','$')); # # MDEV-13633 JSON_ARRAY() - bad output with some UTF8 characters. # SELECT JSON_ARRAY('1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú') AS json_data; SELECT JSON_OBJECT("user","Jožko Mrkvičká") as json_data; # # MDEV-12312 JSON_CONTAINS_PATH does not detect invalid path and returns TRUE. # select json_contains_path('{"foo":"bar"}', 'one', '$[]'); # # MDEV-13971 crash in skip_num_constant. # select JSON_VALID(0x36f0c8dccd83c5eac156da); # # MDEV-13970 crash in Item_func_json_extract::read_json. # create table t1(a double not null); insert into t1 values (2),(1); select 1 from t1 where json_extract(a,'$','$[81]'); drop table t1; # # MDEV-15561 json_extract returns NULL with numbers in scientific notation. # select json_extract('{"test":8.437e-5}','$.test'); # # MDEV-15905 select json_value('{"b":true}','$.b')=1 --> false with # "Truncated incorrect DOUBLE value: 'true'" # select json_value('{"b":true}','$.b')=1; # # MDEV-16209 JSON_EXTRACT in query crashes server. # CREATE TABLE t1 (c VARCHAR(8)); INSERT INTO t1 VALUES ('foo'),('bar'); SELECT * FROM t1 WHERE c IN (JSON_EXTRACT('{"a":"b"}', '$.*')); DROP TABLE t1; --echo # --echo # MDEV-16814 CREATE TABLE SELECT JSON_QUOTE(multibyte_charset_expr) makes a field of a wrong length --echo # CREATE TABLE t1 AS SELECT JSON_QUOTE(_latin1'foo') AS c1, JSON_QUOTE(_utf8'foo') AS c2; SHOW CREATE TABLE t1; DROP TABLE t1; --echo # --echo # MDEV-16054 simple json functions flatline cpu on garbage input. --echo # select json_array(1,user(),compress(5.140264e+307)); --echo # --echo # MDEV-16869 String functions don't respect character set of JSON_VALUE. --echo # 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; select REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' as exp from t1; drop table t1; --echo # --echo # MDEV-16750 JSON_SET mishandles unicode every second pair of arguments. --echo # #enable after MDEV-32454 fix --disable_view_protocol SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6) as exp; SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6) as exp; SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6') as exp; --enable_view_protocol --echo # --echo # MDEV-17121 JSON_ARRAY_APPEND --echo # select json_array_append('[ ]', '$', 'aue'); --echo # --echo # MDEV-17018 JSON_SEARCH and User-Defined Variables. --echo # SET @`json` := '["A", [{"B": "1"}], {"C": "AB"}, {"D": "BC"}]', @`value` := 'AB'; SELECT JSON_SEARCH(@`json`, 'one', @`value`); SET @`json` := NULL, @`value` := NULL; --echo # --echo # MDEV-17001 JSON_MERGE returns nullwhen merging empty array. --echo # SELECT JSON_MERGE('[1]', '[]'); --echo # --echo # MDEV-16174 Assertion `0' failed in Type_handler_string_result:: --echo # make_sort_key(uchar*, Item*, const SORT_FIELD_ATTR*, Sort_param*) --echo # 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; DROP TABLE t1, t2; SET sql_mode=default; --echo # --echo # MDEV-17454 JSON_VALID( '{"a":1]' ) evaluates to 1 --echo # select JSON_VALID( '{"a":1]' ); --echo # --echo # MDEV-18886 JSON_ARRAY() does not recognise JSON argument. --echo # SELECT JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket')) as exp; SELECT CHARSET(JSON_ARRAY()) as exp; SELECT CHARSET(JSON_OBJECT()) as exp; --echo # --echo # MDEV-13992 Implement JSON_MERGE_PATCH --echo # 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; DROP TABLE merge_t; SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}') as exp; SELECT JSON_MERGE_PATCH(NULL, '[1,2,3]') as exp; SELECT JSON_MERGE_PATCH(NULL, 'a') as exp; SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}') as exp; --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT SELECT JSON_MERGE_PATCH() as exp; --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT SELECT JSON_MERGE_PATCH('{}') as exp; SELECT JSON_MERGE_PATCH('{', '[1,2,3]') as exp; SELECT JSON_MERGE_PATCH('{"a":"b"}', '[1,') as exp; --echo # --echo # MDEV-22976 CAST(JSON_EXTRACT() AS DECIMAL) does not handle boolean values --echo # SELECT CAST(JSON_EXTRACT('{"x":true}', '$.x') AS DOUBLE) AS cf, CAST(JSON_EXTRACT('{"x":true}', '$.x') AS DECIMAL) AS cd; SELECT CAST(JSON_EXTRACT('{"x":false}', '$.x') AS DOUBLE) AS cf, CAST(JSON_EXTRACT('{"x":false}', '$.x') AS DECIMAL) AS cd; --echo # --echo # MDEV-24585 Assertion `je->s.cs == nice_js->charset()' failed in json_nice. --echo # SELECT JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' ); --echo # --echo # MDEV-18284 JSON casting using JSON_COMPACT doesn't always work --echo # with values from subqueries --echo # CREATE TABLE json_test(a JSON, b JSON); INSERT INTO json_test VALUES ("[1,2,3]", '{"a":"foo"}'); SELECT * FROM json_test; SELECT json_object("a", json_compact(a), "b", b) FROM (SELECT * FROM json_test) AS json_test_values; SELECT json_object("a", json_compact(a), "b", json_compact(b)) FROM (SELECT * FROM json_test) AS json_test_values; DROP TABLE json_test; --echo # --echo # End of 10.2 tests --echo # --echo # --echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions --echo # --enable_metadata --disable_ps_protocol --disable_view_protocol 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; SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}') AS json_length, JSON_DEPTH('[10, {"a": 20}]') AS json_depnth; --enable_view_protocol --enable_ps_protocol --disable_metadata --echo # --echo # MDEV-19670 json escaped unicode parse error --echo # SELECT json_valid('{"value":"\\ud83d\\ude0a"}'); SELECT json_valid('{"test": "\\ud83d\\ude0b"}'); --echo # --echo # MDEV-19670 json escaped unicode parse error --echo # SELECT JSON_VALID('{"admin\\"": null}'), '{"admin\\"": null}' UNION SELECT JSON_VALID('{"\\"admin": null}'), '{"\\"admin": null}' UNION SELECT JSON_VALID('{"\\"": null}'), '{"\\"": null}'; --echo # --echo # MDEV-29188: Crash in JSON_EXTRACT --echo # 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; drop table t1; --echo # --echo # MDEV-27151: JSON_VALUE() does not parse NULL properties properly --echo # --echo # --echo # It is correct for JSON_EXTRACT() to give null instead of "NULL" because --echo # it returns the json literal that is put inside json. --echo # Hence it should return null as in 'null' string and not SQL NULL. --echo # JSON_VALUE() returns the "VALUE" so it is correct for it to return SQl NULL --echo # SELECT NULL; SELECT JSON_VALUE('{"nulltest": null}', '$.nulltest'); SELECT 1 + NULL; SELECT 1 + JSON_VALUE('{"nulltest": null}', '$.nulltest'); SELECT NULL; SELECT JSON_EXTRACT('{"a":null, "b":10, "c":"null"}', '$.a'); --echo # --echo # Start of 10.4 tests --echo # --echo # --echo # MDEV-16351 JSON_OBJECT() treats hybrid functions with boolean arguments as numbers --echo # --vertical_results #enable after fix MDEV-28649 --disable_view_protocol SELECT JSON_OBJECT("cond", true) AS j1, JSON_OBJECT("cond", COALESCE(true, false)) j2, JSON_OBJECT("cond", COALESCE(COALESCE(true, false))) j3; --enable_view_protocol --horizontal_results CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); SELECT JSON_OBJECT('x',(SELECT MAX(a)=4 FROM t1)); SELECT JSON_OBJECT('x',(SELECT MAX(a)=3 FROM t1)); SELECT JSON_OBJECT('x',(SELECT MAX(a)=2 FROM t1)); SELECT JSON_OBJECT('x',MAX(a=4)) FROM t1; SELECT JSON_OBJECT('x',MAX(a=3)) FROM t1; SELECT JSON_OBJECT('x',MAX(a=2)) FROM t1; SELECT JSON_OBJECT('x',(SELECT MAX(a=4) FROM t1)); SELECT JSON_OBJECT('x',(SELECT MAX(a=3) FROM t1)); SELECT JSON_OBJECT('x',(SELECT MAX(a=2) FROM t1)); SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=4 FROM t1))='{"x": true}' THEN a END; SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=4 FROM t1))='{"x": false}' THEN a END; SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=3 FROM t1))='{"x": true}' THEN a END; SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=3 FROM t1))='{"x": false}' THEN a END; SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=2 FROM t1))='{"x": true}' THEN a END; SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=2 FROM t1))='{"x": false}' THEN a END; SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=4) FROM t1))='{"x": true}' THEN a END; SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=4) FROM t1))='{"x": false}' THEN a END; SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=3) FROM t1))='{"x": true}' THEN a END; SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=3) FROM t1))='{"x": false}' THEN a END; SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{"x": true}' THEN a END; SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{"x": false}' THEN a END; DROP TABLE t1; -- echo # -- echo # MDEV-16620 JSON_ARRAYAGG -- echo # -- echo # -- echo # Integer aggregation -- echo # 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; SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1; SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b; DROP TABLE t1; -- echo # -- echo # Real aggregation -- echo # 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; SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b), JSON_ARRAYAGG(c) FROM t1; DROP TABLE t1; -- echo # -- echo # Boolean aggregation -- echo # 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; SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1; SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b; #enable after MDEV-32454 fix --disable_view_protocol SELECT JSON_ARRAYAGG(TRUE), JSON_ARRAYAGG(FALSE) FROM t1; --enable_view_protocol DROP TABLE t1; -- echo # -- echo # Aggregation of strings with quoted -- echo # 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; SELECT JSON_ARRAYAGG(a) FROM t1; DROP TABLE t1; -- echo # -- echo # Strings and NULLs -- echo # 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; SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1; SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY a; -- echo # -- echo # DISTINCT and LIMIT -- echo # SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1; SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1; SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1 GROUP BY b; SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1 GROUP BY a; SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1; SELECT JSON_ARRAYAGG(DISTINCT b) FROM t1; SELECT JSON_ARRAYAGG(DISTINCT a LIMIT 2) FROM t1; SELECT JSON_ARRAYAGG(DISTINCT b LIMIT 2) FROM t1; -- echo # -- echo # JSON aggregation -- echo # SELECT JSON_VALID(JSON_ARRAYAGG(JSON_ARRAY(a, b))) FROM t1; SELECT JSON_ARRAYAGG(JSON_ARRAY(a, b)) FROM t1; SELECT JSON_ARRAYAGG(JSON_ARRAY(a, b)) FROM t1 GROUP BY a; SELECT JSON_VALID(JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b))) FROM t1; SELECT JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) FROM t1; SELECT JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) FROM t1 GROUP BY a; -- echo # -- echo # Error checks -- echo # --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT SELECT JSON_ARRAYAGG(a, b) FROM t1; --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT SELECT JSON_ARRAYAGG(JSON_ARRAYAGG(a, b)) FROM t1; --error ER_INVALID_GROUP_FUNC_USE SELECT JSON_ARRAYAGG(JSON_ARRAYAGG(a)) FROM t1; -- echo # -- echo # MDEV-16620 JSON_OBJECTAGG -- echo # SELECT JSON_OBJECTAGG(a, b) FROM t1; --error ER_PARSE_ERROR SELECT JSON_OBJECTAGG(a) FROM t1; DROP TABLE t1; --echo # --echo # MDEV-19160 JSON_DETAILED output unnecessarily verbose --echo # 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; select JSON_PRETTY(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) as exp from t200; select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) as exp from t200; drop table t200; --echo # --echo # MDEV-24538: JSON_LENGTH does not return error upon wrong number of parameters --echo # --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT SELECT JSON_LENGTH('{"a":"b"}','$','$', 'foo'); --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT SELECT JSON_LENGTH(); --echo # 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; SET @@COLLATION_CONNECTION= @old_collation_connection; --echo # --echo # End of 10.4 tests --echo # -- echo # -- echo # MDEV-16620 JSON_ARRAYAGG -- echo # CREATE TABLE t1 (a INT); SELECT JSON_ARRAYAGG(a) FROM t1; DROP TABLE t1; -- echo # -- echo # MDEV-21915 Server crashes in copy_fields,Item_func_group_concat::add -- echo while using json_arrayagg() as a window function -- echo # --error ER_NOT_SUPPORTED_YET select json_arrayagg(a) over () from (select 1 a) t; --error ER_NOT_SUPPORTED_YET select json_objectagg(a, b) over () from (select 1 a, 2 b) t; SELECT JSON_ARRAYAGG(NULL) FROM (SELECT 1 AS t) AS A; SELECT JSON_ARRAYAGG("null") FROM (SELECT 1 AS t) AS A; create view v as (select json_arrayagg(json_object("type", "permPeriod", "id", "asd")) as JSON_DATA); select * from v; drop view v; select json_arrayagg(a order by a asc) from (select 1 a union select 2 a) t; select json_object('x', json_arrayagg(json_object('a', 1))); --echo # --echo # MDEV-22011: DISTINCT with JSON_ARRAYAGG gives wrong results --echo # 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; SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1; INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL); SELECT JSON_ARRAYAGG(a) FROM t1; SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1; 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; SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1; INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL); SELECT JSON_ARRAYAGG(a) FROM t1; SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1; DROP TABLE t1; --echo # --echo # MDEV-22840: JSON_ARRAYAGG gives wrong results with NULL values and ORDER by clause --echo # CREATE TABLE t1(a VARCHAR(255)); INSERT INTO t1 VALUES ('red'),('blue'); SELECT JSON_ARRAYAGG(a) FROM t1; SELECT JSON_ARRAYAGG(a ORDER BY a DESC) FROM t1; SELECT JSON_ARRAYAGG(a ORDER BY a ASC) FROM t1; INSERT INTO t1 VALUES (NULL); SELECT JSON_ARRAYAGG(a) FROM t1; SELECT JSON_ARRAYAGG(a ORDER BY a DESC) FROM t1; SELECT JSON_ARRAYAGG(a ORDER BY a ASC) FROM t1; 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))); #enable after MDEV-32454 fix --disable_view_protocol --disable_ps2_protocol select json_arrayagg(a) from t1; --enable_ps2_protocol --enable_view_protocol 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; drop table t1; --echo # --echo # MDEV-23029: JSON_OBJECTAGG returns NULL when used together with GROUP BY --echo # 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; SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e, B.a; CREATE VIEW v AS SELECT JSON_OBJECTAGG(a, e) FROM t1; SELECT * FROM v; DROP VIEW v; DROP TABLE t1; --echo # --echo # MDEV-23004 When using GROUP BY with JSON_ARRAYAGG with joint table, the square brackets are not included. --echo # 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; DROP TABLE t1; DROP TABLE t2; --echo # --echo # MDEV-27018 IF and COALESCE lose "json" property --echo # SELECT json_object('a', if(1, json_object('b', 'c'), json_object('e', 'f'))) as exp; SELECT json_object('a', coalesce(json_object('b', 'c'))) as exp; --echo # --echo # MDEV-26392: Crash with json_get_path_next and 10.5.12 --echo # 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; DROP TABLE arrNestTest; --echo # --echo # MDEV-30412 JSON_OBJECTAGG doesn't escape double quote in key --echo # SELECT JSON_OBJECTAGG('"', 1); SELECT JSON_OBJECTAGG('\"', 1); SELECT JSON_OBJECTAGG('\\', 1); --echo # --echo # End of 10.5 tests --echo # --echo # --echo # MDEV-26054 Server crashes in Item_func_json_arrayagg::get_str_from_field --echo # 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; DROP VIEW v; DROP TABLE t; --echo # --echo # MDEV-29264 JSON functions overflow error based ON LONGTEXT field --echo # 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; 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; 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; DROP TABLE t; --echo # --echo # End of 10.6 tests --echo # --echo # --echo # MDEV-31147 json_normalize does not work correctly with MSAN build --echo # 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; DROP TABLE t1; --echo # --echo # End of 10.8 tests --echo # --echo # --echo # MDEV-27677: Implement JSON_OVERLAPS() --echo # --echo # Testing scalar json datatypes --echo # Comparing scalar json datatypes with itself SELECT JSON_OVERLAPS('true', 'true'); SELECT JSON_OVERLAPS('false', 'false'); SELECT JSON_OVERLAPS('1', '1'); SELECT JSON_OVERLAPS('"string1"', '"string1"'); SELECT JSON_OVERLAPS('null', 'null'); --echo # Comparing scalar json datatypes with other scalar datatype SELECT JSON_OVERLAPS('true', 'false'); SELECT JSON_OVERLAPS('1', '"1"'); SELECT JSON_OVERLAPS('1', '0'); SELECT JSON_OVERLAPS('null', '0'); SELECT JSON_OVERLAPS('"string1"', '"string2"'); SELECT JSON_OVERLAPS('true','["abc", 1, 2, true, false]'); SELECT JSON_OVERLAPS('true','["abc", 1, 2, [true]]'); SELECT JSON_OVERLAPS('true','{"A":true}'); --echo # Testing non-scalar json data types --echo # Comparing object with object (non-nested) SELECT JSON_OVERLAPS('{"A":[1, 2, 3]}','{}'); SELECT JSON_OVERLAPS('{"A": 1}', '{"A": 1}'); SELECT JSON_OVERLAPS('{"A": 1}', '{"B": 1}'); SELECT JSON_OVERLAPS('{ "A": 1, "B": "string1" }', '{ "A": 2, "B": "string1" }') as exp; SELECT JSON_OVERLAPS('{ "A": 1, "B": "string1" }', '{ "A": 2, "B": "string2" }') as exp; --echo # Comparing nested object with other nested object SELECT JSON_OVERLAPS('{ "A": 1, "B": {"C":2} }', '{ "A": 2, "B": {"C":1} }') as exp; SELECT JSON_OVERLAPS('{ "A": 1, "B": {"C":2} }', '{ "A": 2, "B": {"C":2} }') as exp; SELECT JSON_OVERLAPS('{ "A": { "B": true } }', '{ "A": { "B": true, "C": false } }') as exp; SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}', '{"C":3, "B":{"E":5, "D":4}}') as exp; SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":5, "D":4}}') as exp; SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":[5, 6, 7], "D":4}}') as exp; SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":[7, 6 ,5], "D":4}}') as exp; SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "F":{"E":[5, 6, 7], "D":4}}') as exp; --echo # Comparing array with array (non-nested) SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, 1]') as exp; SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, 5]'); SELECT JSON_OVERLAPS('[1,2,3]','[]') as exp; --echo # Comparing nested arrays SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, [1]]') as exp; SELECT JSON_OVERLAPS('[1, 2, [true, false], null]', '[[1], [true, false]]') as exp; SELECT JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]') as exp; --echo # Comparing one non-scalar json datatypes with another non-scalar --echo # json datatype --echo # Comparing array with object SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '{"A": 1}') as exp; SELECT JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]', '{"A": 1}') as exp; SELECT JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]', '{"A": 1}') as exp; SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]', '{"A": 1, "B": 2}') as exp; SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]', '{"A": 1, "B": 3}') as exp; -- echo # Comparing nested array with object SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1, "B": 2}') as exp; SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1, "B": 3}') as exp; SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1}') as exp; --echo # Comparing array with nested object SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]', '{"A": 1, "B": {"C": 12}}') as exp; SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]', '{"A": 1, "B": {"C": 12}}') as exp; --echo # 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; SELECT JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]', '{"A": 1, "B": {"C": 12}}') as exp; --echo # Comparing object with array SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 2}]') as exp; SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 3}]') as exp; SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]') as exp; 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; 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; 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; --echo # 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; --echo # Checking errors and warnings --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT SELECT JSON_OVERLAPS('[1,2,{"A":B}]', '{"A":B}', '{"C":"string1"}'); --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT SELECT JSON_OVERLAPS('[1,2,{"A":B}]'); --echo # --echo # MDEV-27990: Incorrect behavior of JSON_OVERLAPS() on warning --echo # SELECT JSON_OVERLAPS('',''); SELECT JSON_OVERLAPS('true','tr'); --echo # --echo # MDEV-22224: Support JSON Path negative index --echo # 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); SELECT JSON_ARRAY_APPEND(@json, '$.A[last-1][last]', 5); 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); SELECT JSON_ARRAY_INSERT(@json, '$.A[last-1][last-1]', 5); 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]'); SELECT JSON_CONTAINS(@json, '15', '$.A[last-1][last]'); 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]'); SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A[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_EXISTS(@json, '$.A[-2][-1]'); SELECT JSON_EXISTS(@json, '$.A[last-1][last]'); 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]'); SELECT JSON_EXTRACT(@json, '$.A[last-7][1]'); SET @json= '[{"A": 1, "B": 2, "C": {"D": 3}},{"A": 1, "B": 2, "C": {"D": 3}}]'; SELECT JSON_KEYS(@json, '$[-1].C'); SELECT JSON_KEYS(@json, '$[last].C'); 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]'); SELECT JSON_LENGTH(@json, '$.A[last-1][last-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]'); SELECT JSON_QUERY(@json, '$.A[last-7]'); 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]'); SELECT JSON_REMOVE(@json, '$.A[last-9]'); 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); SELECT JSON_REPLACE(@json, '$.A[last]', 4); SET @json = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]'; SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[-2]'); SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[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_SET(@json, '$.A[-4]', 100); SELECT JSON_SET(@json, '$.A[last-3]', 100); 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'); SELECT JSON_VALUE(@json, '$.A[last-1][last-1].key1'); --echo # --echo # MDEV-27972: Unexpected behavior with negative zero (-0) in JSON Path --echo # SET @json='{ "x": [0,1]}'; SELECT JSON_VALUE(@json,'$.x[last]'); SELECT JSON_VALUE(@json,'$.x[last-0]'); SELECT JSON_VALUE(@json,'$.x[-0]'); SELECT JSON_VALUE(@json,'$.x[0]'); --echo # --echo # MDEV-27911: Implement range notation for json path --echo # 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]'); 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]'); 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'); 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]'); 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]'); SELECT JSON_EXTRACT(@json, '$[3][3][last-1 to last]'); SELECT JSON_EXTRACT(@json, '$[3][3][-2 to -1]'); --echo # 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]'); 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]'); 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); 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); 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); 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); 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]'); 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]'); --echo # --echo # MDEV-28075: JSON_VALUE returns first value from array not from range --echo # 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'); 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]'); SELECT JSON_VALUE(@json, '$[2 to 3][0]'); --echo # --echo # MDEV-28072: JSON_EXTRACT has inconsistent behavior with '0' value in --echo # json path (when range is used) --echo # SET @json= '[ 11, 22 , 33]'; SELECT JSON_EXTRACT(@json, '$[0 to 0]'); SELECT JSON_EXTRACT(@json, '$[0 to -0]'); SELECT JSON_EXTRACT(@json, '$[-0 to 0]'); SELECT JSON_EXTRACT(@json, '$[-0 to -0]'); --echo # --echo # MDEV-28071: JSON_EXISTS returns always 1 if it is used range notation --echo # for json path --echo # 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]'); SELECT JSON_EXISTS(@json, '$[2][2][4 to 6]'); SELECT JSON_EXISTS(@json, '$[2][2][1 to 4]'); --echo # --echo # MDEV-28326: Server crashes in json_path_parts_compare --echo # SELECT * FROM JSON_TABLE('{"foo":["bar","qux"]}','$**.*[0]' COLUMNS(col1 CHAR(8) PATH '$[0]')) AS jt; --echo # --echo # MDEV-29212: json_overlaps() does not check nested key-value pair correctly --echo # SET @json1 = '{"kk":{"k1":"v1","k2":"v2"}}'; SET @json2 = '{"kk":{"k1":"v1","k2":"v2","k3":"v3"}}'; SELECT JSON_OVERLAPS(@json2, @json1); SELECT JSON_OVERLAPS(@json1, @json2); --echo # --echo # MDEV-30304: Json Range only affects first row of the result set --echo # 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 ; SELECT JSON_EXTRACT(j, '$[*]') FROM t1 ; DROP TABLE t1; --echo # --echo # MDEV-29381: JSON paths containing dashes are reported as syntax errors in procedures --echo # SELECT JSON_EXTRACT('{ "my-key": 1 }', '$."my-key"'); SELECT JSON_EXTRACT('{ "my-key": 1 }', '$.my-key'); --echo # --echo # MDEV-23187: Assorted assertion failures in json_find_path with certain collations --echo # 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'); 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'); 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'); 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'); SET @@collation_connection= @save_collation_connection; --echo # --echo # End of 10.9 Test --echo #