select json_valid('[1, 2]'); json_valid('[1, 2]') 1 select json_valid('"string"}'); json_valid('"string"}') 0 select json_valid('{"key1":1, "key2":[2,3]}'); json_valid('{"key1":1, "key2":[2,3]}') 1 select json_valid('[false, true, null]'); json_valid('[false, true, null]') 1 select json_valid(repeat('[', 1000)); json_valid(repeat('[', 1000)) 0 select json_valid(repeat('{"a":', 1000)); json_valid(repeat('{"a":', 1000)) 0 select json_value('{"key1":123}', '$.key2'); json_value('{"key1":123}', '$.key2') NULL select json_value('{"key1":123}', '$.key1'); json_value('{"key1":123}', '$.key1') 123 select json_value('{"key1":[1,2,3]}', '$.key1'); json_value('{"key1":[1,2,3]}', '$.key1') NULL select json_value('{"key1": [1,2,3], "key1":123}', '$.key1'); json_value('{"key1": [1,2,3], "key1":123}', '$.key1') 123 select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z'); JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z') Mon"t"y select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2'); json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2') NULL select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1'); json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1') {"a":1, "b":[1,2]} select json_query('{"key1": 1}', '$.key1'); json_query('{"key1": 1}', '$.key1') NULL select json_query('{"key1":123, "key1": [1,2,3]}', '$.key1'); json_query('{"key1":123, "key1": [1,2,3]}', '$.key1') [1,2,3] select json_query('{"key1":123, "key1": [1,2,3]}', concat('$', repeat('.k', 1000))); json_query('{"key1":123, "key1": [1,2,3]}', concat('$', repeat('.k', 1000))) NULL select json_array(); json_array() [] select json_array(1); json_array(1) [1] select json_array(1, "text", false, null); json_array(1, "text", false, null) [1, "text", false, null] select json_array_append('["a", "b"]', '$', FALSE); json_array_append('["a", "b"]', '$', FALSE) ["a", "b", false] select json_array_append('{"k1":1, "k2":["a", "b"]}', '$.k2', 2); json_array_append('{"k1":1, "k2":["a", "b"]}', '$.k2', 2) {"k1": 1, "k2": ["a", "b", 2]} select json_array_append('["a", ["b", "c"], "d"]', '$[0]', 2); json_array_append('["a", ["b", "c"], "d"]', '$[0]', 2) [["a", 2], ["b", "c"], "d"] select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x'); json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x') ["a", "x", {"b": [1, 2]}, [3, 4]] select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2]', 'x'); json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2]', 'x') ["a", {"b": [1, 2]}, "x", [3, 4]] select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[3]', 'x'); json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[3]', 'x') ["a", {"b": [1, 2]}, [3, 4], "x"] select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[4]', 'x'); json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[4]', 'x') ["a", {"b": [1, 2]}, [3, 4], "x"] select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x'); json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x') ["a", {"b": ["x", 1, 2]}, [3, 4]] select json_array_insert('true', '$', 1); json_array_insert('true', '$', 1) NULL select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2][1]', 'y'); json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2][1]', 'y') ["a", {"b": [1, 2]}, [3, "y", 4]] select json_contains('{"k1":123, "k2":345}', '123', '$.k1'); json_contains('{"k1":123, "k2":345}', '123', '$.k1') 1 select json_contains('"you"', '"you"'); json_contains('"you"', '"you"') 1 select json_contains('"youth"', '"you"'); json_contains('"youth"', '"you"') 0 select json_contains('[1]', '[1]', '$', '$[0]'); ERROR 42000: Incorrect parameter count in the call to native function 'json_contains' select json_contains('', '', '$'); json_contains('', '', '$') NULL Warnings: Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_contains' select json_contains('null', 'null', '$'); json_contains('null', 'null', '$') 1 select json_contains('"10"', '"10"', '$'); json_contains('"10"', '"10"', '$') 1 select json_contains('"10"', '10', '$'); json_contains('"10"', '10', '$') 0 select json_contains('10.1', '10', '$'); json_contains('10.1', '10', '$') 0 select json_contains('10.0', '10', '$'); json_contains('10.0', '10', '$') 1 select json_contains('[1]', '1'); json_contains('[1]', '1') 1 select json_contains('[2, 1]', '1'); json_contains('[2, 1]', '1') 1 select json_contains('[2, [2, 3], 1]', '1'); json_contains('[2, [2, 3], 1]', '1') 1 select json_contains('[4, [2, 3], 1]', '2'); json_contains('[4, [2, 3], 1]', '2') 1 select json_contains('[2, 1]', '[1, 2]'); json_contains('[2, 1]', '[1, 2]') 1 select json_contains('[2, 1]', '[1, 0, 2]'); json_contains('[2, 1]', '[1, 0, 2]') 0 select json_contains('[2, 0, 3, 1]', '[1, 2]'); json_contains('[2, 0, 3, 1]', '[1, 2]') 1 select json_contains('{"b":[1,2], "a":1}', '{"a":1, "b":2}'); json_contains('{"b":[1,2], "a":1}', '{"a":1, "b":2}') 1 select json_contains('{"a":1}', '{}'); json_contains('{"a":1}', '{}') 1 select json_contains('[1, {"a":1}]', '{}'); json_contains('[1, {"a":1}]', '{}') 1 select json_contains('[1, {"a":1}]', '{"a":1}'); json_contains('[1, {"a":1}]', '{"a":1}') 1 select json_contains('[{"abc":"def", "def":"abc"}]', '["foo","bar"]'); json_contains('[{"abc":"def", "def":"abc"}]', '["foo","bar"]') 0 select json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]'); json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]') 1 select json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}'); json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}') 1 select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]"); json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]") 1 select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[10]"); json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[10]") 0 select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.ma"); json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.ma") 0 select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1"); json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1") 1 select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1", "$.ma"); json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1", "$.ma") 1 select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.ma"); json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.ma") 0 select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.key2"); json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.key2") 1 select json_contains_path('{ "a": true }', NULL, '$.a' ); json_contains_path('{ "a": true }', NULL, '$.a' ) NULL select json_contains_path('{ "a": true }', 'all', NULL ); json_contains_path('{ "a": true }', 'all', NULL ) NULL select json_contains_path('{"a":{"b":"c"}}', 'one', '$.a.*'); json_contains_path('{"a":{"b":"c"}}', 'one', '$.a.*') 1 select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1"); json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1") "asd" select json_extract('{"key1":"asd", "key2":[2,3]}', "$.keyX", "$.keyY"); json_extract('{"key1":"asd", "key2":[2,3]}', "$.keyX", "$.keyY") NULL select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2"); json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2") ["asd", [2, 3]] select json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2"); json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2") [5, [2, 3]] select json_extract('{"key0":true, "key1":"qwe"}', "$.key1"); json_extract('{"key0":true, "key1":"qwe"}', "$.key1") "qwe" select json_extract(json_object('foo', 'foobar'),'$'); json_extract(json_object('foo', 'foobar'),'$') {"foo": "foobar"} select json_extract('[10, 20, [30, 40]]', '$[2][*]'); json_extract('[10, 20, [30, 40]]', '$[2][*]') [30, 40] select json_extract('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]'); json_extract('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]') [{"a": 3}, 30, 40] select json_extract('1', '$'); json_extract('1', '$') 1 select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]'); json_extract('[10, 20, [30, 40], 1, 10]', '$[1]') 20 select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]'); json_extract('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]') [20] select json_extract( '[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a'); json_extract( '[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a') [[3, 4]] select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word'); json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word') {"a": 1, "b": {"c": 1, "k1": "word"}, "d": [1, 2]} select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3); json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3) {"a": 1, "b": {"c": 1}, "d": [1, 2, 3]} select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2); json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2) {"a": [1, 2], "b": {"c": 1}, "d": [1, 2]} select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word'); json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word') {"a": 1, "b": {"c": 1}, "d": [1, 2]} select json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]'); json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') {"a": 10, "b": [2, 3], "c": "[true, false]"} select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]'); json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') {"a": 10, "b": [2, 3]} select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]'); json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]') {"a": 10, "b": "[true, false]"} set @j = '["a", ["b", "c"], "d"]'; select json_remove(@j, '$[0]'); json_remove(@j, '$[0]') [["b", "c"], "d"] select json_remove(@j, '$[1]'); json_remove(@j, '$[1]') ["a", "d"] select json_remove(@j, '$[2]'); json_remove(@j, '$[2]') ["a", ["b", "c"]] set @j = '{"a": 1, "b": [2, 3]}'; select json_remove(@j, '$.b'); json_remove(@j, '$.b') {"a": 1} select json_remove(@j, '$.a'); json_remove(@j, '$.a') {"b": [2, 3]} select json_object(); json_object() {} select json_object("ki", 1, "mi", "ya"); json_object("ki", 1, "mi", "ya") {"ki": 1, "mi": "ya"} create table t1 as select json_object('id', 87, 'name', 'carrot') as f; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `f` varchar(32) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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"); json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") 1 select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]"); json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]") 1 select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]"); json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]") 0 select json_quote('"string"'); json_quote('"string"') "\"string\"" create table t1 as select json_quote('foo'); select * from t1; json_quote('foo') "foo" show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `json_quote('foo')` varchar(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}'); json_keys('{"a":{"c":1, "d":2}, "b":2, "c":1, "a":3, "b":1, "c":2}') ["a", "b", "c"] select json_keys('{"c1": "value 1", "c1": "value 2"}'); json_keys('{"c1": "value 1", "c1": "value 2"}') ["c1"] SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]'; select json_search(@j, 'one', 'abc'); json_search(@j, 'one', 'abc') "$[0]" select json_search(@j, 'all', 'abc'); json_search(@j, 'all', 'abc') ["$[0]", "$[2].x"] select json_search(@j, 'all', 'abc', NULL, '$[2]'); json_search(@j, 'all', 'abc', NULL, '$[2]') "$[2].x" select json_search(@j, 'all', 'abc', NULL, '$'); json_search(@j, 'all', 'abc', NULL, '$') ["$[0]", "$[2].x"] select json_search(@j, 'all', '10', NULL, '$'); json_search(@j, 'all', '10', NULL, '$') "$[1][0].k" select json_search(@j, 'all', '10', NULL, '$[*]'); json_search(@j, 'all', '10', NULL, '$[*]') "$[1][0].k" select json_search(@j, 'all', '10', NULL, '$[*][0].k'); json_search(@j, 'all', '10', NULL, '$[*][0].k') "$[1][0].k" select json_search(@j, 'all', '10', NULL, '$**.k'); json_search(@j, 'all', '10', NULL, '$**.k') "$[1][0].k" create table t1( json_col text ); insert into t1 values ('{ "a": "foobar" }'), ('{ "a": "foobar", "b": "focus", "c": [ "arm", "foot", "shoulder" ] }'); select json_search( json_col, 'all', 'foot' ) from t1; json_search( json_col, 'all', 'foot' ) NULL "$.c[1]" drop table t1; select json_unquote('"abc"'); json_unquote('"abc"') abc select json_unquote('abc'); json_unquote('abc') abc create table t1 (c VARCHAR(8)) DEFAULT CHARSET=latin1; insert into t1 values ('abc'),('def'); select json_object('foo', json_unquote(json_object('bar', c)),'qux', c) as fld from t1; fld {"foo": "{\"bar\": \"abc\"}", "qux": "abc"} {"foo": "{\"bar\": \"def\"}", "qux": "def"} drop table t1; select json_object("a", json_object("b", "abcd")); json_object("a", json_object("b", "abcd")) {"a": {"b": "abcd"}} select json_object("a", '{"b": "abcd"}'); json_object("a", '{"b": "abcd"}') {"a": "{\"b\": \"abcd\"}"} select json_object("a", json_compact('{"b": "abcd"}')); json_object("a", json_compact('{"b": "abcd"}')) {"a": {"b": "abcd"}} select json_compact(NULL); json_compact(NULL) NULL select json_depth(json_compact(NULL)); json_depth(json_compact(NULL)) NULL select json_depth('[[], {}]'); json_depth('[[], {}]') 2 select json_depth('[[[1,2,3],"s"], {}, []]'); json_depth('[[[1,2,3],"s"], {}, []]') 4 select json_depth('[10, {"a": 20}]'); json_depth('[10, {"a": 20}]') 3 select json_length(''); json_length('') NULL Warnings: Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_length' select json_length('{}'); json_length('{}') 0 select json_length('[1, 2, {"a": 3}]'); json_length('[1, 2, {"a": 3}]') 3 select json_length('{"a": 1, "b": {"c": 30}}', '$.b'); json_length('{"a": 1, "b": {"c": 30}}', '$.b') 1 select json_length('{"a": 1, "b": {"c": 30}}'); json_length('{"a": 1, "b": {"c": 30}}') 2 select json_length('{}{'); json_length('{}{') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_length' at position 3 create table json (j INT); show create table json; Table Create Table json CREATE TABLE `json` ( `j` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table json; select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' ); json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' ) 1 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' ); json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' ) 1 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0]' ); json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0]' ) 1 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0][0]' ); json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0][0]' ) 1 select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2]' ); json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2]' ) 2 select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0]' ); json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0]' ) 2 select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0]' ); json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0]' ) 2 select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0][0]' ); json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0][0]' ) 2 select json_length( '{"a":{"b":{"d":1}}, "a":{"c":{"d":1, "j":2}}}', '$.a[0][0][0].c' ); json_length( '{"a":{"b":{"d":1}}, "a":{"c":{"d":1, "j":2}}}', '$.a[0][0][0].c' ) 2 select json_set('1', '$[0]', 100); json_set('1', '$[0]', 100) 100 select json_set('1', '$[0][0]', 100); json_set('1', '$[0][0]', 100) 100 select json_set('1', '$[1]', 100); json_set('1', '$[1]', 100) [1, 100] select json_set('{"a":12}', '$[0]', 100); json_set('{"a":12}', '$[0]', 100) 100 select json_set('{"a":12}', '$[0].a', 100); json_set('{"a":12}', '$[0].a', 100) {"a": 100} select json_set('{"a":12}', '$[0][0].a', 100); json_set('{"a":12}', '$[0][0].a', 100) {"a": 100} select json_set('{"a":12}', '$[0][1].a', 100); json_set('{"a":12}', '$[0][1].a', 100) {"a": 12} select json_value('{"\\"key1":123}', '$."\\"key1"'); json_value('{"\\"key1":123}', '$."\\"key1"') 123 select json_value('{"\\"key1\\"":123}', '$."\\"key1\\""'); json_value('{"\\"key1\\"":123}', '$."\\"key1\\""') 123 select json_value('{"key 1":123}', '$."key 1"'); json_value('{"key 1":123}', '$."key 1"') 123 select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]"); json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]") 1 select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[3]"); json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[3]") 0 select json_extract( '[1]', '$[0][0]' ); json_extract( '[1]', '$[0][0]' ) 1 select json_extract( '[1]', '$[1][0]' ); json_extract( '[1]', '$[1][0]' ) NULL select json_extract( '[1]', '$**[0]' ); json_extract( '[1]', '$**[0]' ) [1] select json_extract( '[1]', '$**[0][0]' ); json_extract( '[1]', '$**[0][0]' ) [1] select json_insert('1', '$[0]', 4); json_insert('1', '$[0]', 4) 1 select json_replace('1', '$[0]', 4); json_replace('1', '$[0]', 4) 4 select json_set('1', '$[0]', 4); json_set('1', '$[0]', 4) 4 select json_set('1', '$[1]', 4); json_set('1', '$[1]', 4) [1, 4] select json_replace('1', '$[1]', 4); json_replace('1', '$[1]', 4) 1 SELECT json_insert('[]', '$[0][0]', 100); json_insert('[]', '$[0][0]', 100) [] SELECT json_insert('1', '$[0][0]', 100); json_insert('1', '$[0][0]', 100) 1 SELECT json_replace('1', '$[0][0]', 100); json_replace('1', '$[0][0]', 100) 100 SELECT json_replace('[]', '$[0][0]', 100); json_replace('[]', '$[0][0]', 100) [] SELECT json_set('[]', '$[0][0]', 100); json_set('[]', '$[0][0]', 100) [] SELECT json_set('[]', '$[0][0][0]', 100); json_set('[]', '$[0][0][0]', 100) [] SELECT JSON_search( '{"": "a"}', "one", 'a'); JSON_search( '{"": "a"}', "one", 'a') "$." select json_merge('{"a":"b"}', '{"a":"c"}') ; json_merge('{"a":"b"}', '{"a":"c"}') {"a": ["b", "c"]} select json_merge('{"a":{"x":"b"}}', '{"a":"c"}') ; json_merge('{"a":{"x":"b"}}', '{"a":"c"}') {"a": [{"x": "b"}, "c"]} select json_merge('{"a":{"u":12, "x":"b"}}', '{"a":{"x":"c"}}') ; json_merge('{"a":{"u":12, "x":"b"}}', '{"a":{"x":"c"}}') {"a": {"u": 12, "x": ["b", "c"]}} select json_merge('{"a":{"u":12, "x":"b", "r":1}}', '{"a":{"x":"c", "r":2}}') ; json_merge('{"a":{"u":12, "x":"b", "r":1}}', '{"a":{"x":"c", "r":2}}') {"a": {"u": 12, "x": ["b", "c"], "r": [1, 2]}} select json_compact('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}'); json_compact('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') {"a":1,"b":[1,2,3],"c":{"aa":"v1","bb":"v2"}} select json_loose('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}'); json_loose('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') {"a": 1, "b": [1, 2, 3], "c": {"aa": "v1", "bb": "v2"}} select json_detailed('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}'); json_detailed('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') { "a": 1, "b": [ 1, 2, 3 ], "c": { "aa": "v1", "bb": "v2" } } SELECT JSON_search( '{"x": "\\""}', "one", '"'); JSON_search( '{"x": "\\""}', "one", '"') "$.x" SELECT JSON_search( '{"x": "\\""}', "one", '\\"'); JSON_search( '{"x": "\\""}', "one", '\\"') "$.x" set @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)); json_array(repeat('a',1024),repeat('a',1024)) NULL Warnings: Warning 1301 Result of json_array() was larger than max_allowed_packet (2048) - truncated select json_object("a", repeat('a',1024),"b", repeat('a',1024)); json_object("a", repeat('a',1024),"b", repeat('a',1024)) NULL Warnings: Warning 1301 Result of json_object() was larger than max_allowed_packet (2048) - truncated connection default; set @@global.max_allowed_packet = @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'; JSON_EXTRACT('{"name":"value"}', '$.name') = 'value' 1 select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = true; JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = true 1 select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = false; JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = false 0 select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = 1; JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = 1 1 select JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"'); JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"') "\u00f6" select JSON_EXTRACT('{"foo": "bar" foobar foo invalid ', '$.foo'); JSON_EXTRACT('{"foo": "bar" foobar foo invalid ', '$.foo') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_extract' at position 15 SELECT JSON_OBJECT('foo', '`'); JSON_OBJECT('foo', '`') {"foo": "`"} SELECT JSON_OBJECT("foo", "bar`bar"); JSON_OBJECT("foo", "bar`bar") {"foo": "bar`bar"} SELECT JSON_SET('{}', '$.age', 87); JSON_SET('{}', '$.age', 87) {"age": 87} SELECT JSON_MERGE('[]', '{"c":"d"}'); JSON_MERGE('[]', '{"c":"d"}') [{"c": "d"}] SET @str = "{\"\\u00e4\\u00f6\":\"yes\"}"; SET @path = "$.\"\\u00e4\\u00f6\""; select @str, @path, JSON_EXTRACT(@str, @path); @str @path JSON_EXTRACT(@str, @path) {"\u00e4\u00f6":"yes"} $."\u00e4\u00f6" "yes" SET @str = "{\"\\u00e4\":\"yes\"}"; SET @path = "$.\"\\u00e4\""; select @str, @path, JSON_EXTRACT(@str, @path); @str @path JSON_EXTRACT(@str, @path) {"\u00e4":"yes"} $."\u00e4" "yes" select json_array(5,json_query('[1,2]','$')); json_array(5,json_query('[1,2]','$')) [5, [1,2]] SELECT JSON_ARRAY('1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú') AS json_data; json_data ["1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú"] SELECT JSON_OBJECT("user","Jožko Mrkvičká") as json_data; json_data {"user": "Jožko Mrkvičká"} select json_contains_path('{"foo":"bar"}', 'one', '$[]'); json_contains_path('{"foo":"bar"}', 'one', '$[]') NULL Warnings: Warning 4042 Syntax error in JSON path in argument 3 to function 'json_contains_path' at position 3 select JSON_VALID(0x36f0c8dccd83c5eac156da); JSON_VALID(0x36f0c8dccd83c5eac156da) 0 create table t1(a double not null); insert into t1 values (2),(1); select 1 from t1 where json_extract(a,'$','$[81]'); 1 drop table t1; select json_extract('{"test":8.437e-5}','$.test'); json_extract('{"test":8.437e-5}','$.test') 8.437e-5 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' from t1; REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' 1 drop table t1; # # MDEV-16750 JSON_SET mishandles unicode every second pair of arguments. # SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6); JSON_SET('{}', '$.a', _utf8 0xC3B6) {"a": ""} SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6); JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6) {"a": "", "b": ""} SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6'); JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6') {"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')); JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket')) ["str", {"plugin": "unix_socket"}] SELECT CHARSET(JSON_ARRAY()); CHARSET(JSON_ARRAY()) latin1 SELECT CHARSET(JSON_OBJECT()); CHARSET(JSON_OBJECT()) 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"}'); JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}') NULL SELECT JSON_MERGE_PATCH(NULL, '[1,2,3]'); JSON_MERGE_PATCH(NULL, '[1,2,3]') [1, 2, 3] SELECT JSON_MERGE_PATCH(NULL, 'a'); JSON_MERGE_PATCH(NULL, 'a') 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"}'); JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}') {"d": "e"} SELECT JSON_MERGE_PATCH(); ERROR 42000: Incorrect parameter count in the call to native function 'JSON_MERGE_PATCH' SELECT JSON_MERGE_PATCH('{}'); ERROR 42000: Incorrect parameter count in the call to native function 'JSON_MERGE_PATCH' SELECT JSON_MERGE_PATCH('{', '[1,2,3]'); JSON_MERGE_PATCH('{', '[1,2,3]') NULL Warnings: Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_merge_patch' SELECT JSON_MERGE_PATCH('{"a":"b"}', '[1,'); JSON_MERGE_PATCH('{"a":"b"}', '[1,') 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 # # End of 10.3 tests # # # 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')) from t200; JSON_DETAILED(JSON_EXTRACT(a, '$**.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"] } ] select JSON_PRETTY(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; JSON_PRETTY(JSON_EXTRACT(a, '$**.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"] } ] select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; JSON_LOOSE(JSON_EXTRACT(a, '$**.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"]}] drop table t200; # # 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'))); json_object('a', if(1, json_object('b', 'c'), json_object('e', 'f'))) {"a": {"b": "c"}} SELECT json_object('a', coalesce(json_object('b', 'c'))); json_object('a', coalesce(json_object('b', 'c'))) {"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 # # # Beginning of 10.9 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" }'); JSON_OVERLAPS('{ "A": 1, "B": "string1" }', '{ "A": 2, "B": "string1" }') 1 SELECT JSON_OVERLAPS('{ "A": 1, "B": "string1" }', '{ "A": 2, "B": "string2" }'); JSON_OVERLAPS('{ "A": 1, "B": "string1" }', '{ "A": 2, "B": "string2" }') 0 # Comparing nested object with other nested object SELECT JSON_OVERLAPS('{ "A": 1, "B": {"C":2} }', '{ "A": 2, "B": {"C":1} }'); JSON_OVERLAPS('{ "A": 1, "B": {"C":2} }', '{ "A": 2, "B": {"C":1} }') 0 SELECT JSON_OVERLAPS('{ "A": 1, "B": {"C":2} }', '{ "A": 2, "B": {"C":2} }'); JSON_OVERLAPS('{ "A": 1, "B": {"C":2} }', '{ "A": 2, "B": {"C":2} }') 1 SELECT JSON_OVERLAPS('{ "A": { "B": true } }', '{ "A": { "B": true, "C": false } }'); JSON_OVERLAPS('{ "A": { "B": true } }', '{ "A": { "B": true, "C": false 0 SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}', '{"C":3, "B":{"E":5, "D":4}}'); JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}', '{"C":3, "B":{"E":5, "D":4}}') 1 SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":5, "D":4}}'); JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":5, "D":4}}') 0 SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":[5, 6, 7], "D":4}}'); JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":[5, 6, 7], "D":4}}') 1 SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":[7, 6 ,5], "D":4}}'); JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":[7, 6 ,5], "D":4}}') 0 SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "F":{"E":[5, 6, 7], "D":4}}'); JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "F":{"E":[5, 6, 7], "D":4}}') 0 # Comparing array with array (non-nested) SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, 1]'); JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, 1]') 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]','[]'); JSON_OVERLAPS('[1,2,3]','[]') 0 # Comparing nested arrays SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, [1]]'); JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, [1]]') 0 SELECT JSON_OVERLAPS('[1, 2, [true, false], null]', '[[1], [true, false]]'); JSON_OVERLAPS('[1, 2, [true, false], null]', '[[1], [true, false]]') 1 SELECT JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]'); JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]') 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}'); JSON_OVERLAPS('[1, 2, true, false, null]', '{"A": 1}') 0 SELECT JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]', '{"A": 1}'); JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]', '{"A": 1}') 0 SELECT JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]', '{"A": 1}'); JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]', '{"A": 1}') 1 SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]', '{"A": 1, "B": 2}'); JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]', '{"A": 1, "B": 2}') 1 SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]', '{"A": 1, "B": 3}'); JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]', '{"A": 1, "B": 3}') 0 # Comparing nested array with object SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1, "B": 2}'); JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1, "B": 2}') 0 SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1, "B": 3}'); JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1, "B": 3}') 0 SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1}'); JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1}') 0 # Comparing array with nested object SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]', '{"A": 1, "B": {"C": 12}}'); JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]', '{"A": 1, "B": {"C": 12}}') 1 SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]', '{"A": 1, "B": {"C": 12}}'); JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]', '{"A": 1, "B": {"C": 12}}') 0 # Comparing nested array with nested objects SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]', '{"A": 1, "B":{"C": 12}}'); JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]', '{"A": 1, "B":{"C": 12}}') 0 SELECT JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]', '{"A": 1, "B": {"C": 12}}'); JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]', '{"A": 1, "B": {"C": 12}}') 0 # Comparing object with array SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 2}]'); JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 2}]') 0 SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 3}]'); JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 3}]') 1 SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]'); JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]') 1 SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, 3]}', '[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]'); JSON_OVERLAPS('{"A": 1, "B": [1, 2, 3]}', '[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]') 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}]}]'); 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}]}]') 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}]}]'); 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}]}]') 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}]]'); JSON_OVERLAPS('{"A": 1, "B": 3}','[1, 2, true, false, [{"A": 1, "B": 2}, {"A": 1, "B": 3}]]') 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 # # End of 10.9 Test # # Beginning of 11.1 test # # MDEV-27128: Implement JSON Schema Validation FUNCTION # # Checking annotations SET @schema_number= '{ "title" : "This is title 1", "description":"this is description 1", "$comment":"This is comment 1", "type":"number", "deprecated":true, "readOnly":true, "writeOnly":false, "example":[2], "default":4, "$schema": "https://json-schema.org/draft/2019-09/json-schema-validation.html#rfc.section.9.5" }'; SELECT JSON_SCHEMA_VALID(@schema_number, '"string1"'); JSON_SCHEMA_VALID(@schema_number, '"string1"') 0 SELECT JSON_SCHEMA_VALID(@schema_number, '2'); JSON_SCHEMA_VALID(@schema_number, '2') 1 # Checking empty schema with empty json document SET @schema= '{}'; SELECT JSON_SCHEMA_VALID(@schema, ''); JSON_SCHEMA_VALID(@schema, '') 1 SELECT JSON_SCHEMA_VALID(@schema, '{}'); JSON_SCHEMA_VALID(@schema, '{}') 1 SELECT JSON_SCHEMA_VALID(@schema, '[]'); JSON_SCHEMA_VALID(@schema, '[]') 1 SELECT JSON_SCHEMA_VALID(@schema, 'null'); JSON_SCHEMA_VALID(@schema, 'null') 1 SELECT JSON_SCHEMA_VALID(@schema, 'true'); JSON_SCHEMA_VALID(@schema, 'true') 1 SELECT JSON_SCHEMA_VALID(@schema, 'false'); JSON_SCHEMA_VALID(@schema, 'false') 1 # Checking scalar against json schema # Checking boolean and null SET @schema_true= '{ "type": "boolean"}'; SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); JSON_SCHEMA_VALID(@schema_true, 'true') 1 SELECT JSON_SCHEMA_VALID(@schema_true, 'false'); JSON_SCHEMA_VALID(@schema_true, 'false') 1 SELECT JSON_SCHEMA_VALID(@schema_true, 'null'); JSON_SCHEMA_VALID(@schema_true, 'null') 0 SET @schema_true= '{ "type": "boolean", "const":"false"}'; SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); JSON_SCHEMA_VALID(@schema_true, 'true') 0 SET @schema_true= '{ "type": "boolean", "enum":[true, null, false]}'; SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); JSON_SCHEMA_VALID(@schema_true, 'true') 1 SET @schema_true= '{ "type": "boolean", "enum": [null, false]}'; SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); JSON_SCHEMA_VALID(@schema_true, 'true') 0 SET @schema_true= '{ "type": "boolean", "enum": [null, true]}'; SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); JSON_SCHEMA_VALID(@schema_true, 'true') 1 SET @schema_true= '{ "type": "boolean", "const":"false"}'; SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); JSON_SCHEMA_VALID(@schema_true, 'true') 0 # Type can be more than one SET @schema= ' { "type":["string", "number","array"] }'; SELECT JSON_SCHEMA_VALID(@schema, '{"key1":"val1"}'); JSON_SCHEMA_VALID(@schema, '{"key1":"val1"}') 0 SELECT JSON_SCHEMA_VALID(@schema, '"abc"'); JSON_SCHEMA_VALID(@schema, '"abc"') 1 SELECT JSON_SCHEMA_VALID(@schema, '3.14'); JSON_SCHEMA_VALID(@schema, '3.14') 1 # Checking number SET @schema_number= '{ "maximum":7, "minimum": 3, "multipleOf":3 }'; SELECT JSON_SCHEMA_VALID(@schema_number, '2'); JSON_SCHEMA_VALID(@schema_number, '2') 0 SET @schema_number= '{ "type": "number", "maximum":13, "minimum": 4, "multipleOf":3, "exclusiveMaximum": 9, "exclusiveMinimum":4 }'; SELECT JSON_SCHEMA_VALID(@schema_number, '2'); JSON_SCHEMA_VALID(@schema_number, '2') 0 SELECT JSON_SCHEMA_VALID(@schema_number, '6'); JSON_SCHEMA_VALID(@schema_number, '6') 1 SELECT JSON_SCHEMA_VALID(@schema_number, '9'); JSON_SCHEMA_VALID(@schema_number, '9') 0 SELECT JSON_SCHEMA_VALID(@schema_number, '5'); JSON_SCHEMA_VALID(@schema_number, '5') 0 SET @schema_number= '{ "type": "number", "maximum":100, "minimum": 0, "enum": [1, 2, "3", [4, 5, 6], {"key1":"val1"}] }'; SELECT JSON_SCHEMA_VALID(@schema_number, 1); JSON_SCHEMA_VALID(@schema_number, 1) 1 SELECT JSON_SCHEMA_VALID(@schema_number, 3); JSON_SCHEMA_VALID(@schema_number, 3) 0 SET @schema_number= '{ "type":"number", "maximum":10, "const":2 }'; SELECT JSON_SCHEMA_VALID(@schema_number, '3'); JSON_SCHEMA_VALID(@schema_number, '3') 0 SELECT JSON_SCHEMA_VALID(@schema_number, '2'); JSON_SCHEMA_VALID(@schema_number, '2') 1 # Checking string # checking format keyword. (not validating for now) SET @schema_string= '{ "type": "string", "format":"date-time" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_date-time"'); JSON_SCHEMA_VALID(@schema_string, '"not_date-time"') 1 SET @schema_string= '{ "type": "string", "format":"date" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_date"'); JSON_SCHEMA_VALID(@schema_string, '"not_date"') 1 SET @schema_string= '{ "type": "string", "format":"time" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_time"'); JSON_SCHEMA_VALID(@schema_string, '"not_time"') 1 SET @schema_string= '{ "type": "string", "format":"duration" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_duration"'); JSON_SCHEMA_VALID(@schema_string, '"not_duration"') 1 SET @schema_string= '{ "type": "string", "format":"email" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_email"'); JSON_SCHEMA_VALID(@schema_string, '"not_email"') 1 SET @schema_string= '{ "type": "string", "format":"idn-email" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_idn-email"'); JSON_SCHEMA_VALID(@schema_string, '"not_idn-email"') 1 SET @schema_string= '{ "type": "string", "format":"hostname" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_hostname"'); JSON_SCHEMA_VALID(@schema_string, '"not_hostname"') 1 SET @schema_string= '{ "type": "string", "format":"idn-hostname" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_idn-hostname"'); JSON_SCHEMA_VALID(@schema_string, '"not_idn-hostname"') 1 SET @schema_string= '{ "type": "string", "format":"ipv4" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_ipv4"'); JSON_SCHEMA_VALID(@schema_string, '"not_ipv4"') 1 SET @schema_string= '{ "type": "string", "format":"ipv6" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_ipv6"'); JSON_SCHEMA_VALID(@schema_string, '"not_ipv6"') 1 SET @schema_string= '{ "type": "string", "format":"uri" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uri"'); JSON_SCHEMA_VALID(@schema_string, '"not_uri"') 1 SET @schema_string= '{ "type": "string", "format":"uri-reference" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uri-reference"'); JSON_SCHEMA_VALID(@schema_string, '"not_uri-reference"') 1 SET @schema_string= '{ "type": "string", "format":"iri" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_iri"'); JSON_SCHEMA_VALID(@schema_string, '"not_iri"') 1 SET @schema_string= '{ "type": "string", "format":"iri-reference" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_iri-reference"'); JSON_SCHEMA_VALID(@schema_string, '"not_iri-reference"') 1 SET @schema_string= '{ "type": "string", "format":"uuid" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uuid"'); JSON_SCHEMA_VALID(@schema_string, '"not_uuid"') 1 SET @schema_string= '{ "type": "string", "format":"json-pointer" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_json-pointer"'); JSON_SCHEMA_VALID(@schema_string, '"not_json-pointer"') 1 SET @schema_string= '{ "type": "string", "format":"relative-json-pointer" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_relative-json-pointer"'); JSON_SCHEMA_VALID(@schema_string, '"not_relative-json-pointer"') 1 SET @schema_string= '{ "type": "string", "format":"regex" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"not_regex"'); JSON_SCHEMA_VALID(@schema_string, '"not_regex"') 1 # Validating other string keywords SET @schema_string= '{ "type": "string", "maxLength":7, "minLength": 4 }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar"'); JSON_SCHEMA_VALID(@schema_string, '"foobar"') 1 SET @schema_string= '{ "type": "string", "maxLength": 10, "minLength": 8 }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar"'); JSON_SCHEMA_VALID(@schema_string, '"foobar"') 0 SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar123"'); JSON_SCHEMA_VALID(@schema_string, '"foobar123"') 1 SET @schema_string= '{ "type": "string", "maxLength": 10, "minLength": 3, "const": "foobar" }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar123"'); JSON_SCHEMA_VALID(@schema_string, '"foobar123"') 0 SET @schema_string= '{ "type": "string", "enum": ["red", "green", "blue"] }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"green"'); JSON_SCHEMA_VALID(@schema_string, '"green"') 1 SELECT JSON_SCHEMA_VALID(@schema_string, '"orange"'); JSON_SCHEMA_VALID(@schema_string, '"orange"') 0 SET @string_schema= '{ "type":"string", "pattern":"ab+c" }'; SELECT JSON_SCHEMA_VALID(@string_schema, '"abc"'); JSON_SCHEMA_VALID(@string_schema, '"abc"') 1 # Validating non-scalar # Validating array SET @schema_array= '{"type":"array"}'; SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]') 1 SET @schema_array= '{"type":"array", "maxItems": 4, "minItems": 2}'; SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]') 1 SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '[1]'); JSON_SCHEMA_VALID(@schema_array, '[1]') 0 SET @schema_array= '{"maxItems": 4, "minItems": 2}'; SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]') 1 SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2]') 1 SET @schema_array= '{ "type":"array", "items": {"type":"number"}, "maxItems": 4, "minItems": 2}'; SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3]') 1 SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, "foobar"]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, "foobar"]') 0 SET @schema_array= '{"type":"array", "maxItems": 4, "minItems": 2, "const": [1, 2, 3, 4]}'; SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, "foobar"]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, "foobar"]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4]') 1 SET @schema_array= '{"type":"array", "enum":[[1,2,3], [4,5,6], [7,8,9]]}'; SELECT JSON_SCHEMA_VALID(@schema_array, '[4,5,6]'); JSON_SCHEMA_VALID(@schema_array, '[4,5,6]') 1 SELECT JSON_SCHEMA_VALID(@schema_array, '[1,5,7]'); JSON_SCHEMA_VALID(@schema_array, '[1,5,7]') 0 SET @schema_array= '{ "type": "array", "uniqueItems":true }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 2.0]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 2.0]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4.0]'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4.0]') 1 SET @schema_array= '{ "type": "array", "contains": { "type": "number" }, "minContains": 2, "maxContains": 3 }'; SELECT JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3, 4]'); JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3, 4]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3]'); JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3]') 1 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ] }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]') 1 SELECT JSON_SCHEMA_VALID(@schema_array, '[2, "string1",2]'); JSON_SCHEMA_VALID(@schema_array, '[2, "string1",2]') 0 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "items":true }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]') 1 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "items":false }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]') 0 # Checking objects SET @schema_object= '{"type":"object", "properties": { "number1":{ "type":"number", "maximum":12, "minimum":1 }, "string1": { "type":"string", "maxLength":10, "minLength": 4 }, "array1": {"type":"array", "maxItems": 4, "minItems": 2} } }'; SELECT JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3]}'); JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3]}') 1 SELECT JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3,4,5]}'); JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3,4,5]}') 0 SET @schema_obj= '{ "type": "object", "properties": { "number1":{"type":"number"}, "string1":{"type":"string"}, "array1":{"type":"array"} }, "dependentRequired": { "number1":["string1"] } }'; SELECT JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2, "string1":"abc"}'); JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2, "string1":"abc"}') 1 SELECT JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2}'); JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2}') 0 SET @schema_obj= '{"type":"object", "properties": { "number1":{ "type":"number", "maximum":12, "minimum":1 }, "key1" : { "type":"object", "properties": { "key2" :{ "type":"string" } } } }, "enum": [{"number1":3, "key1":{"key2":"string1"}}, {"number1":5, "key1":{"key2":"string3"}}, {"number1":7, "key1":{"key2":"string5"}}] }'; SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":1}}'); JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":1}}') 0 SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string1"}}'); JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string1"}}') 0 SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string7"}}'); JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string7"}}') 0 SET @schema_obj= '{"type":"object", "properties": { "number1":{ "type":"number", "maximum":12, "minimum":1 }, "obj1" : { "type":"object", "properties": { "obj1_1":{ "type":"string" }, "obj1_2": { "type":"array" } } }, "obj2" : { "type":"object", "properties" : { "obj2_1":{ "type":"number" } } } }, "required":["number1", "obj2"] }'; SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}'); JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}') 0 SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}'); JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}') 1 SET @schema_obj= '{"type":"object", "properties": { "number1":{ "type":"number", "maximum":12, "minimum":1 }, "obj1" : { "type":"object", "properties": { "obj1_1":{ "type":"string" }, "obj1_2": { "type":"array" } } }, "obj2" : { "type":"object", "properties" : { "obj2_1":{ "type":"number" } } } }, "required":["number1", "obj2"], "const": {"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}} }'; SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}'); JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}') 1 SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}'); JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}') 0 SET @schema_obj= '{"type":"object", "properties": { "number1":{ "type":"number", "maximum":12, "minimum":1 }, "obj1" : { "type":"object", "properties": { "obj1_1":{ "type":"string" }, "obj1_2": { "type":"array" } } }, "obj2" : { "type":"object", "properties" : { "obj2_1":{ "type":"number" } } } }, "maxProperties": 5, "minProperties":2 }'; SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}'); JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}') 1 SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":1, "number2":2, "number3":3, "number4":4, "number5":5, "number6":6}'); JSON_SCHEMA_VALID(@schema_obj,'{"number1":1, "number2":2, "number3":3, "number4":4, "number5":5, "number6":6}') 0 SET @schema_obj= '{"type":"object", "properties": { "number1":{ "type":"number", "maximum":12, "minimum":1 }, "obj1" : { "type":"object", "properties": { "obj1_1":{ "type":"string" }, "obj1_2": { "type":"array" } } } }, "maxProperties": 3, "minProperties":1, "additionalProperties":false }'; SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":"string2"}'); JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":"string2"}') 0 # Demonstrating that irrelavent keywords for a type and irrelavent type # are ignored, and none of the keywords are mandatory, including "type". SET @schema_properties= '{ "properties" : { "number1":{ "maximum":10 }, "string1" : { "maxLength": 3} } }'; SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":25, "string1":"ab" }'); JSON_SCHEMA_VALID(@schema_properties, '{ "number1":25, "string1":"ab" }') 0 SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":10, "string1":"ab" }'); JSON_SCHEMA_VALID(@schema_properties, '{ "number1":10, "string1":"ab" }') 1 SET @schema_properties= '{ "properties" : { "number1":{ "maximum":10 }, "string1" : { "maxLength": 3}, "obj1" : { "properties":{ "number2": {"minimum":8}, "array2": {"uniqueItems":true} } } } }'; SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":2, "array2":[1,2,3]} }'); JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":2, "array2":[1,2,3]} }') 0 SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3]} }'); JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3]} }') 1 SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"array2":[1,2,3]} }'); JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"array2":[1,2,3]} }') 1 SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3,2]} }'); JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3,2]} }') 0 SET @schema_num= '{ "maximum":10, "minimum":2 }'; SELECT JSON_SCHEMA_VALID(@schema_num, '5'); JSON_SCHEMA_VALID(@schema_num, '5') 1 SELECT JSON_SCHEMA_VALID(@schema_num, '"abc"'); JSON_SCHEMA_VALID(@schema_num, '"abc"') 1 SET @schema_str= '{ "maxLength":5, "minLength":2, "pattern":"a+bc" }'; SELECT JSON_SCHEMA_VALID(@schema_str, '"abc"'); JSON_SCHEMA_VALID(@schema_str, '"abc"') 1 SELECT JSON_SCHEMA_VALID(@schema_str, '"abcadef"'); JSON_SCHEMA_VALID(@schema_str, '"abcadef"') 0 SELECT JSON_SCHEMA_VALID(@schema_str, '"bc"'); JSON_SCHEMA_VALID(@schema_str, '"bc"') 0 SET @schema_arr= '{ "uniqueItems":true, "items":{"type":"string"}, "maximum":10 }'; SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "cde"]'); JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "cde"]') 1 SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "abc"]'); JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "abc"]') 0 SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", 1]'); JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", 1]') 0 SET @schema_const1= '{"const":2}'; SELECT JSON_SCHEMA_VALID(@schema_const1, '2'); JSON_SCHEMA_VALID(@schema_const1, '2') 1 SELECT JSON_SCHEMA_VALID(@schema_const1, '"abc"'); JSON_SCHEMA_VALID(@schema_const1, '"abc"') 0 SET @schema_const2= '{"const":true}'; SELECT JSON_SCHEMA_VALID(@schema_const2,'true'); JSON_SCHEMA_VALID(@schema_const2,'true') 1 SELECT JSON_SCHEMA_VALID(@schema_const2,'false'); JSON_SCHEMA_VALID(@schema_const2,'false') 0 SET @schema_enum='{"enum":[1,2,3,"abc", [4,5,6]]}'; SELECT JSON_SCHEMA_VALID(@schema_enum,'[4,5,6]'); JSON_SCHEMA_VALID(@schema_enum,'[4,5,6]') 1 SELECT JSON_SCHEMA_VALID(@schema_enum,'4'); JSON_SCHEMA_VALID(@schema_enum,'4') 0 SELECT JSON_SCHEMA_VALID(@schema_enum,'"abc"'); JSON_SCHEMA_VALID(@schema_enum,'"abc"') 1 SET @schema_required='{"required":["num1","str1", "arr1"]}'; SELECT JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "str1":"abc", "arr1":[1,2,3]}'); JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "str1":"abc", "arr1":[1,2,3]}') 1 SELECT JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "arr1":[1,2,3]}'); JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "arr1":[1,2,3]}') 0 SELECT JSON_SCHEMA_VALID(@schema_required,'"abcd"'); JSON_SCHEMA_VALID(@schema_required,'"abcd"') 1 SET @schema_dep_required='{ "dependentRequired": { "num1":["num2","num3"], "str1":["str2","str3"] } }'; SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3]}'); JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3]}') 1 SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "str1":"abcd"}'); JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "str1":"abcd"}') 0 SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "arr1":[1,2,3]}'); JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "arr1":[1,2,3]}') 1 # Checking syntax error SET @invalid_schema= '{"type":"object" "properties":{ "number1": {"type":"number"}, "obj2": {"type":"object", "properties": { "key1": {"type":"number"} } } } }'; SELECT JSON_SCHEMA_VALID(@invalid_schema, '{"number1":3, "obj2":{"key1":3}}'); JSON_SCHEMA_VALID(@invalid_schema, '{"number1":3, "obj2":{"key1":3}}') 1 Warnings: Warning 4038 Syntax error in JSON text in argument 2 to function 'json_schema_valid' at position 45 SET @invalid_json= '{"type":"array", "maxItems": 4, "minItems": 2, "const": [1, 2, 3, 4]}'; SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4'); JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4') 0 SET @schema_string= '{ "type": "string", "maxLength":-2 }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"abcxyz"'); ERROR HY000: Invalid value for keyword maxLength SET @schema_number= '{ "type": "number", "multipleOf":-3 }'; SELECT JSON_SCHEMA_VALID(@schema_number, '3'); ERROR HY000: Invalid value for keyword multipleOf SET @schema_object= '{ "type": "object", "properties":{"num1":{"type":"number"}}, "required":{} }'; SELECT JSON_SCHEMA_VALID(@schema_object, '{"num1":2}'); ERROR HY000: Invalid value for keyword required SET @schema_string= '{ "type": "string", "maxLength":-10 }'; SELECT JSON_SCHEMA_VALID(@schema_string, '"str1"'); ERROR HY000: Invalid value for keyword maxLength SET @schema_number= '{"type":"numberz"}'; SELECT JSON_SCHEMA_VALID(@schema_number, '"string1"'); ERROR HY000: Invalid value for keyword type # Using JSON_SCHEMA_VALID() as a constraint validation to insert into table CREATE TABLE str_table (val_str JSON CHECK(JSON_SCHEMA_VALID('{ "type":"string", "maxLength":5, "minLength":2, "enum":["ab", "cd", "abc", "def", "abcdef"] }', val_str))); CREATE TABLE num_table(val_num JSON CHECK(JSON_SCHEMA_VALID('{ "type":"number", "minimum":10, "maximum":30, "exclusiveMinimum":11, "exclusiveMaximum":29, "multipleOf":5, "const":15 }', val_num))); CREATE TABLE true_table(val_true JSON CHECK(JSON_SCHEMA_VALID('{ "type":"boolean", "enum":[true, false, null] }', val_true))); CREATE TABLE false_table (val_false JSON CHECK(JSON_SCHEMA_VALID('{ "type":"boolean" }', val_false))); CREATE TABLE null_table (val_null JSON CHECK(JSON_SCHEMA_VALID('{ "type":"null" }', val_null))); CREATE TABLE arr_table (val_arr JSON CHECK(JSON_SCHEMA_VALID('{ "type":"array", "uniqueItems":true, "maxItems":5, "minItems":1, "items":true, "prefixItems":[{"type":"number"}] }', val_arr))); CREATE TABLE obj_table(val_obj JSON CHECK(JSON_SCHEMA_VALID('{ "type":"object", "properties": { "number1":{ "type":"number", "maximum":5, "const":4 }, "string1":{ "type":"string", "maxLength":5, "minLength":3 }, "object1":{ "type":"object", "properties":{ "key1": {"type":"string"}, "key2":{"type":"array"}, "key3":{"type":"number", "minimum":3} }, "dependentRequired": { "key1":["key3"] } } }, "required":["number1","object1"] }', val_obj))); INSERT INTO str_table VALUES ('"ab"'), ('"cd"'), ('"abc"'), ('"def"'); INSERT INTO str_table VALUES ('"feb"'); ERROR 23000: CONSTRAINT `str_table.val_str` failed for `test`.`str_table` INSERT INTO str_table VALUES('"abcdef"'); ERROR 23000: CONSTRAINT `str_table.val_str` failed for `test`.`str_table` INSERT INTO str_table VALUES('"fedcba"'); ERROR 23000: CONSTRAINT `str_table.val_str` failed for `test`.`str_table` SELECT * FROM str_table; val_str "ab" "cd" "abc" "def" INSERT INTO num_table values('15'); INSERT INTO num_table values('25'); ERROR 23000: CONSTRAINT `num_table.val_num` failed for `test`.`num_table` SELECT * FROM num_table; val_num 15 INSERT INTO true_table VALUES ('true'); SELECT * FROM true_table; val_true true INSERT INTO false_table VALUES('false'); SELECT * FROM false_table; val_false false INSERT INTO arr_table VALUES ('[10, 2, "abc"]'); INSERT INTO arr_table VALUES('[100]'); INSERT INTO arr_table VALUES ('["str1", 2, "abc", 2.0]'); ERROR 23000: CONSTRAINT `arr_table.val_arr` failed for `test`.`arr_table` SELECT * FROM arr_table; val_arr [10, 2, "abc"] [100] INSERT INTO obj_table VALUES('{"number1":4, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}}'); INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}}'); ERROR 23000: CONSTRAINT `obj_table.val_obj` failed for `test`.`obj_table` INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd"}'); ERROR 23000: CONSTRAINT `obj_table.val_obj` failed for `test`.`obj_table` INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"]}'); ERROR 23000: CONSTRAINT `obj_table.val_obj` failed for `test`.`obj_table` SELECT * FROM obj_table; val_obj {"number1":4, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}} DROP TABLE str_table, num_table, true_table, false_table, null_table, arr_table, obj_table; # array validation SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "additionalItems" : {"type":"number"}, "unevaluatedItems": {"type":"string"}, "items":{"type":"array"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "2", "string"]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "2", "string"]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], [1,2]]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], [1,2]]') 1 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "additionalItems" : {"type":"number"}, "unevaluatedItems": {"type":"string"}, "items": true }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]') 1 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "additionalItems" : {"type":"number"}, "unevaluatedItems": {"type":"string"}, "items": false }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]') 0 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "additionalItems" : {"type":"number"}, "unevaluatedItems": {"type":"string"}, "items": [{"type":"string"}] }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); ERROR HY000: Invalid value for keyword items # Removing items SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "additionalItems" : {"type":"number"}, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') 1 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "additionalItems" : true, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') 1 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "additionalItems" : false, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') 0 # Using items in place of additionalItems SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "items" : false, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') 0 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "items" : true, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') 1 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "items" : {"type":"number"}, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') 1 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "items" : {"type":"number"}, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]') 0 # Removing items and additionalItems both SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "unevaluatedItems": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2]') 1 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "unevaluatedItems": true }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]') 1 SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ], "unevaluatedItems": false }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]') 0 # Removing items, additionalItems, unevaluatedItems SET @schema_array= '{ "type": "array", "prefixItems": [ { "type": "number", "maximum": 10, "minimum":3}, { "type": "string" } ] }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]') 1 # Removing prefixItems SET @schema_array= '{ "type": "array", "items": { "type": "number", "maximum": 10, "minimum":3}, "additionalItems" : {"type":"number"}, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]') 0 SET @schema_array= '{ "type": "array", "items": { "type": "number", "maximum": 10, "minimum":3}, "additionalItems" : {"type":"string"}, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]') 0 SET @schema_array= '{ "type": "array", "items": true, "additionalItems" : {"type":"string"}, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]') 1 SET @schema_array= '{ "type": "array", "items": false, "additionalItems" : {"type":"string"}, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '[]'); JSON_SCHEMA_VALID(@schema_array, '[]') 1 # Removing prefixItems and additionalItems SET @schema_array= '{ "type": "array", "items": {"type":"string"}, "unevaluatedItems": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]'); JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]'); JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]') 1 SET @schema_array= '{ "type": "array", "items": {"type":"string"}, "unevaluatedItems": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]'); JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]') 0 # removing prefixItems, additionalItems and unevaluatedItems SET @schema_array= '{ "type": "array", "items": {"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]'); JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]'); JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]') 1 # Checking that additionalItems alone has no effect on schema without items/prefixItems # regardless existence of unevaluatedItems SET @schema_array= '{ "type": "array", "additionalItems": {"type":"string"}, "unevaluatedItems": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]'); JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]') 1 SET @schema_array= '{ "type": "array", "additionalItems": true, "unevaluatedItems": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]'); JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]') 1 SET @schema_array= '{ "type": "array", "additionalItems": false, "unevaluatedItems": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]'); JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]') 1 # checking that unevaluatedItems alone can have effect on schema validation SET @schema_array= '{ "type": "array", "unevaluatedItems": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[1]'); JSON_SCHEMA_VALID(@schema_array, '[1]') 1 SET @schema_array= '{ "type": "array", "unevaluatedItems": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]'); JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]') 0 SET @schema_array= '{ "type": "array", "unevaluatedItems": false }'; SELECT JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]'); JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]') 0 SELECT JSON_SCHEMA_VALID(@schema_array, '[]'); JSON_SCHEMA_VALID(@schema_array, '[]') 1 SELECT JSON_SCHEMA_VALID(@schema_array, '[1,2,3]'); JSON_SCHEMA_VALID(@schema_array, '[1,2,3]') 0 # Object validation SET @property_names= '{ "PropertyNames":{ "pattern": "^I_" } }'; SELECT JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "O_ob1":{"key1":"val1"}}'); JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "O_ob1":{"key1":"val1"}}') 0 SELECT JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "I_ob1":{"key1":"val1"}}'); JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "I_ob1":{"key1":"val1"}}') 1 # checking that when a match is found in properties or patternProperties, it must validate and # validation result affects the schema. If key is not found in properties or patternProperties, and # additionalProperties exists, it must validate regardless of existence or value for unevaluatedProperties # and the result of validation with additionalProperties affects result of whole schema SET @object_schema= '{ "type":"object", "properties": { "key1":{"type":"string"}, "key2":{"type":"number", "maximum":50} }, "patternProperties": { "^I_": {"type":"number", "maximum":100}, "^S_" : {"type":"string", "maxLength":4} }, "additionalProperties":{ "type":"array", "maxItems":5 }, "unevaluatedProperties":{"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 1 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": [1,2,3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": [1,2,3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":"abc", "some_prop1":[1,2,3]}') 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"string1"}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"string1"}') 0 # removing patternProperties to check that validation falls back on additionalProperties and # existence of unevaluatedProperties still does not change anything because of existence of additional # properties SET @object_schema= '{ "type":"object", "properties": { "key1":{"type":"string"}, "key2":{"type":"number", "maximum":50} }, "additionalProperties":{ "type":"array", "maxItems":5 }, "unevaluatedProperties":{"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}') 1 # Remvoing additionalProperties to check that validation falls back on unevaluatedProperties SET @object_schema= '{ "type":"object", "properties": { "key1":{"type":"string"}, "key2":{"type":"number", "maximum":50} }, "patternProperties": { "^I_": {"type":"number", "maximum":100}, "^S_" : {"type":"string", "maxLength":4} }, "unevaluatedProperties":{"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":[1,2,3]}') 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":"str"}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":"str"}') 1 # Removing unevaluatedProperties has no effect on result when additionalProperties is present SET @object_schema= '{ "type":"object", "properties": { "key1":{"type":"string"}, "key2":{"type":"number", "maximum":50} }, "patternProperties": { "^I_": {"type":"number", "maximum":100}, "^S_" : {"type":"string", "maxLength":4} }, "additionalProperties":{ "type":"array", "maxItems":5 } }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 1 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"str1"}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"str1"}') 0 SET @object_schema= '{ "type":"object", "properties": { "key1":{"type":"string"}, "key2":{"type":"number", "maximum":50} }, "patternProperties": { "^I_": {"type":"number", "maximum":100}, "^S_" : {"type":"string", "maxLength":4} }, "additionalProperties": false }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 0 SET @object_schema= '{ "type":"object", "properties": { "key1":{"type":"string"}, "key2":{"type":"number", "maximum":50} }, "patternProperties": { "^I_": {"type":"number", "maximum":100}, "^S_" : {"type":"string", "maxLength":4} }, "additionalProperties": true }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 1 # Checking that in absence of additionalProperties, validation falls back on evaluatedProperties SET @object_schema= '{ "type":"object", "properties": { "key1":{"type":"string"}, "key2":{"type":"number", "maximum":50} }, "patternProperties": { "^I_": {"type":"number", "maximum":100}, "^S_" : {"type":"string", "maxLength":4} }, "unevaluatedProperties":{"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":"str1"}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":"str1"}') 1 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 0 SET @object_schema= '{ "type":"object", "properties": { "key1":{"type":"string"}, "key2":{"type":"number", "maximum":50} }, "unevaluatedProperties":{"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":"str2", "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":"str2", "S_":"abc", "some_prop1":[1,2,3]}') 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 0 # Properties to check if patternProperties get evaluated but keys not found in patternProperties get evaluated against # additionalProperties regardless of existence of unevaluatedProperperties SET @object_schema= '{ "type":"object", "patternProperties": { "^I_": {"type":"number", "maximum":100}, "^S_" : {"type":"string", "maxLength":4} }, "additionalProperties":{ "type":"array", "maxItems":5 }, "unevaluatedProperties":{"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 1 SET @object_schema= '{ "type":"object", "patternProperties": { "^I_": {"type":"number", "maximum":100}, "^S_" : {"type":"string", "maxLength":4} }, "additionalProperties":{ "type":"array", "maxItems":5 } }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 1 # Checking that in absence of additionalProperties and properties, the keys not found in patternProperties are # evaluated against unevaluatedProperties SET @object_schema= '{ "type":"object", "patternProperties": { "^I_": {"type":"number", "maximum":100}, "^S_" : {"type":"string", "maxLength":4} }, "unevaluatedProperties":{"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str2", "I_int":20, "S_":"abc", "some_prop1":"str1"}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str2", "I_int":20, "S_":"abc", "some_prop1":"str1"}') 1 # checking that in absence of properties, additionalProperties and unevaluatedPropoerties, the keys that are # not found are considered validated. SET @object_schema= '{ "type":"object", "patternProperties": { "^I_": {"type":"number", "maximum":100}, "^S_" : {"type":"string", "maxLength":4} } }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 1 # checking that additionalProperties are evaluated in absence of patternProperties and properties, regardless # of presence of unevaluatedProperties SET @object_schema= '{ "type":"object", "additionalProperties":{ "type":"array", "maxItems":5 }, "unevaluatedProperties":{"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}') 1 SET @object_schema= '{ "type":"object", "additionalProperties":{ "type":"array", "maxItems":5 } }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}') 1 # Checking that in absence of properties, patternProperties and additionalProperties, validation falls back on unevaluatedProperties SET @object_schema= '{ "type":"object", "unevaluatedProperties":{"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}') 1 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}') 0 SET @object_schema= '{ "type":"object", "unevaluatedProperties": false }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}') 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{}'); JSON_SCHEMA_VALID(@object_schema, '{}') 1 SET @object_schema= '{ "type":"object", "unevaluatedProperties": true }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}'); JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}') 1 SELECT JSON_SCHEMA_VALID(@object_schema, '{}'); JSON_SCHEMA_VALID(@object_schema, '{}') 1 SET @json_schema_dependent_schemas='{ "type": "object", "properties": { "str1": { "type": "string" }, "num1": { "type": "number" } }, "required": ["str1"], "dependentSchemas": { "num1": { "properties": { "str2": { "type": "string" } }, "required": ["str2"] } } }'; SELECT JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str", "num1":4}'); JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str", "num1":4}') 0 SELECT JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str"}'); JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str"}') 1 # Validating logic SET @not_schema= '{ "not":{ "maximum": 4 } }'; SELECT JSON_SCHEMA_VALID(@not_schema, '{"I_int1":3, "O_ob1":{"key1":"val1"}}'); JSON_SCHEMA_VALID(@not_schema, '{"I_int1":3, "O_ob1":{"key1":"val1"}}') 0 SELECT JSON_SCHEMA_VALID(@not_schema, '3'); JSON_SCHEMA_VALID(@not_schema, '3') 0 SELECT JSON_SCHEMA_VALID(@not_schema, '10'); JSON_SCHEMA_VALID(@not_schema, '10') 1 SET @not_schema= '{ "not":{ "properties": { "num1" : {"type":"number", "maximum":5}, "string1" : { "maxLength": 3} } } }'; SELECT JSON_SCHEMA_VALID(@not_schema, '{"num1":10, "string2":"abcd"}'); JSON_SCHEMA_VALID(@not_schema, '{"num1":10, "string2":"abcd"}') 1 SELECT JSON_SCHEMA_VALID(@not_schema, '{"num1":2, "string2":"abcd"}'); JSON_SCHEMA_VALID(@not_schema, '{"num1":2, "string2":"abcd"}') 0 SET @any_of_schema= '{ "anyOf":[{ "properties": { "num1" : {"type":"number", "maximum":5}, "string1" : { "maxLength": 3} } }, { "properties":{ "num1" : {"type":"number", "maximum": 1}, "string1" : { "maxLength":5} } } ] }'; SELECT JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abcd"}'); JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abcd"}') 0 SELECT JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abc"}'); JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abc"}') 1 SET @any_of_schema= '{ "anyOf": [ {"type":"number", "maximum":5}, {"type":"string"} ] }'; SELECT JSON_SCHEMA_VALID(@any_of_schema, '2'); JSON_SCHEMA_VALID(@any_of_schema, '2') 1 SELECT JSON_SCHEMA_VALID(@any_of_schema, '6'); JSON_SCHEMA_VALID(@any_of_schema, '6') 0 SELECT JSON_SCHEMA_VALID(@any_of_schema, '[1, 2, 3]'); JSON_SCHEMA_VALID(@any_of_schema, '[1, 2, 3]') 0 SET @one_of_schema= '{ "oneOf":[{ "properties": { "num1" : {"type":"number", "maximum":5}, "string1" : { "maxLength": 3} } }, { "properties":{ "num1" : {"type":"number", "maximum": 1}, "string1" : { "maxLength":5} } } ] }'; SELECT JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abcd"}'); JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abcd"}') 0 SELECT JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abc"}'); JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abc"}') 1 SET @one_of_schema= '{ "oneOf": [ {"type":"number", "maximum":5}, {"type":"number", "maximum":3} ] }'; SELECT JSON_SCHEMA_VALID(@one_of_schema, '2'); JSON_SCHEMA_VALID(@one_of_schema, '2') 0 SELECT JSON_SCHEMA_VALID(@one_of_schema, '4'); JSON_SCHEMA_VALID(@one_of_schema, '4') 1 SET @all_of_schema= '{ "allOf":[{ "properties": { "num1" : {"type":"number", "maximum":5}, "string1" : { "maxLength": 3} } }, { "properties":{ "num1" : {"type":"number", "maximum": 1}, "string1" : { "maxLength":5} } } ] }'; SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":2, "string1":"abcd"}'); JSON_SCHEMA_VALID(@all_of_schema, '{"num1":2, "string1":"abcd"}') 0 SET @all_of_schema= '{ "allOf":[ { "properties":{ "num1": {"type":"number"}, "string1": {"type":"string"} } }, { "properties":{ "num1": {"maximum":10}, "string1": {"maxLength":5} } } ] }'; SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"abc"}'); JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"abc"}') 1 SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"foobar"}'); JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"foobar"}') 0 SET @condition_schema= '{ "if" : {"maximum":30, "multipleOf":3}, "then":{"minimum":5}, "else":{"minimum":10} }'; SELECT JSON_SCHEMA_VALID(@condition_schema, '6'); JSON_SCHEMA_VALID(@condition_schema, '6') 1 SELECT JSON_SCHEMA_VALID(@condition_schema, '4'); JSON_SCHEMA_VALID(@condition_schema, '4') 0 SELECT JSON_SCHEMA_VALID(@condition_schema, '13'); JSON_SCHEMA_VALID(@condition_schema, '13') 1 SET @condition_schema= '{ "if" : {"maximum":30, "multipleOf":3} }'; SELECT JSON_SCHEMA_VALID(@condition_schema, '6'); JSON_SCHEMA_VALID(@condition_schema, '6') 1 SELECT JSON_SCHEMA_VALID(@condition_schema, '7'); JSON_SCHEMA_VALID(@condition_schema, '7') 1 SET @condition_schema= '{ "then":{"minimum":5}, "else":{"minimum":10} }'; SELECT JSON_SCHEMA_VALID(@condition_schema, '4'); JSON_SCHEMA_VALID(@condition_schema, '4') 1 SELECT JSON_SCHEMA_VALID(@condition_schema, '11'); JSON_SCHEMA_VALID(@condition_schema, '11') 1 # Checking unevaluatedProperperties with logical properties SET @all_of_unevaluated='{ "allOf": [ { "type": "object", "properties": { "street_address": { "type": "string" }, "city": { "type": "string" }, "state": { "type": "string" } }, "required": ["street_address", "city", "state"] } ], "properties": { "type": { "enum": ["residential", "business"] } }, "required": ["type"], "unevaluatedProperties": false }'; SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '{ "street_address": "1600 Pennsylvania Avenue NW", "city": "Washington", "state": "DC", "type": "business" }'); JSON_SCHEMA_VALID(@all_of_unevaluated, '{ "street_address": "1600 Pennsylvania Avenue NW", "city": "Washington", "state": "DC", "type": "business" }') 1 SET @all_of_unevaluated='{ "allOf": [ { "type": "object", "properties": { "street_address": { "type": "string" }, "city": { "type": "string" }, "state": { "type": "string" } }, "required": ["street_address", "city", "state"] } ], "unevaluatedProperties": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '{ "street_address": "1600 Pennsylvania Avenue NW", "city": "Washington", "state": "DC", "type": "business" }'); JSON_SCHEMA_VALID(@all_of_unevaluated, '{ "street_address": "1600 Pennsylvania Avenue NW", "city": "Washington", "state": "DC", "type": "business" }') 0 SET @any_of_unevaluated='{ "anyOf": [ { "type": "object", "properties": { "street_address": { "type": "string" }, "city": { "type": "string" }, "state": { "type": "string" } }, "required": ["street_address", "city", "state"] } ], "unevaluatedProperties": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@any_of_unevaluated, '{ "street_address": "1600 Pennsylvania Avenue NW", "city": "Washington", "state": "DC", "type": "business" }'); JSON_SCHEMA_VALID(@any_of_unevaluated, '{ "street_address": "1600 Pennsylvania Avenue NW", "city": "Washington", "state": "DC", "type": "business" }') 0 SET @all_of_unevaluated='{ "allOf": [ { "type": "array" }, { "maxItems":10 }, { "prefixItems": [ {"type":"number"}, {"type":"string"}], "additionalItems":{"type":"array"} } ], "unevaluatedItems": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]'); JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]') 0 SET @all_of_unevaluated='{ "anyOf": [ { "type": "array" }, { "maxItems":10 }, { "prefixItems": [ {"type":"number"}, {"type":"string"}], "additionalItems":{"type":"array"} } ], "unevaluatedItems": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]'); JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]') 1 SET @all_of_unevaluated='{ "oneOf": [ { "type": "array" }, { "maxItems":10 }, { "prefixItems": [ {"type":"number"}, {"type":"string"}], "additionalItems":{"type":"array"} } ], "unevaluatedItems": {"type":"number"} }'; SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]'); JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]') 0 # Media string SET @schema_media_string= '{ "type": "string", "contentMediaType": "text/html" }'; SELECT JSON_SCHEMA_VALID(@schema_media_string, '"str1"'); JSON_SCHEMA_VALID(@schema_media_string, '"str1"') 1 SET @schema_reference= '{"$ref": "http://example.com/custom-email-validator.json#"}'; SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); ERROR HY000: $ref keyword is not supported SET @schema_reference= '{"$id": "http://example.com/custom-email-validator.json#"}'; SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); ERROR HY000: $id keyword is not supported SET @schema_reference= '{"$anchor": "http://example.com/custom-email-validator.json#"}'; SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); ERROR HY000: $anchor keyword is not supported SET @schema_reference= '{"$defs": "http://example.com/custom-email-validator.json#"}'; SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); ERROR HY000: $defs keyword is not supported # # MDEV-30795: JSON_SCHEMA_VALID bugs mentioned in comment # SET @schema= '{ "type":"array", "uniqueItems":true }'; SELECT JSON_SCHEMA_VALID(@schema, '[null, null]'); JSON_SCHEMA_VALID(@schema, '[null, null]') 0 SET @schema_max_items= '{"maxItems":-1}'; SELECT JSON_SCHEMA_VALID(@schema_max_items, '[]'); ERROR HY000: Invalid value for keyword maxItems SET @schema_min_items= '{"minItems":-1}'; SELECT JSON_SCHEMA_VALID(@schema_min_items, '[]'); ERROR HY000: Invalid value for keyword maxLength SET @schema_max_properties= '{"maxProperties":-1}'; SELECT JSON_SCHEMA_VALID(@schema_max_properties, '{}'); ERROR HY000: Invalid value for keyword maxProperties SET @schema_min_properties= '{"minProperties":-1}'; SELECT JSON_SCHEMA_VALID(@schema_min_properties, '{}'); ERROR HY000: Invalid value for keyword minProperties SET @schema_multiple_of= '{"multipleOf":-1}'; SELECT JSON_SCHEMA_VALID(@schema_multiple_of, '2'); ERROR HY000: Invalid value for keyword multipleOf SET @schema_max_contains= '{"maxContains":-1}'; SELECT JSON_SCHEMA_VALID(@schema_max_contains, '[]'); ERROR HY000: Invalid value for keyword maxContains SET @schema_min_contains= '{"minContains":-1}'; SELECT JSON_SCHEMA_VALID(@schema_min_contains, '[]'); ERROR HY000: Invalid value for keyword minContains SET @schema_required='{"type":"object","required":[1,"str1", "str1"]}'; SELECT JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "str1":"abc", "arr1":[1,2,3]}'); ERROR HY000: Invalid value for keyword required # # MDEV-30995: JSON_SCHEMA_VALID is not validating case sensitive when using regex # SET @schema_pattern='{ "type": "string", "pattern": "[A-Z]" }'; SELECT JSON_SCHEMA_VALID(@schema_pattern, '"a"'); JSON_SCHEMA_VALID(@schema_pattern, '"a"') 0 SET @schema_property_names='{ "PropertyNames":{ "pattern": "^I_" } }'; SELECT JSON_SCHEMA_VALID(@schema_property_names, '{"I_num":4}'); JSON_SCHEMA_VALID(@schema_property_names, '{"I_num":4}') 1 SELECT JSON_SCHEMA_VALID(@schema_property_names, '{"i_num":4}'); JSON_SCHEMA_VALID(@schema_property_names, '{"i_num":4}') 0 SET @schema_pattern_properties= '{ "patternProperties": { "^I_": {"type":"number", "maximum":100}, "^S_" : {"type":"string", "maxLength":4} } }'; SELECT JSON_SCHEMA_VALID(@schema_pattern_properties, '{"I_": 50}'); JSON_SCHEMA_VALID(@schema_pattern_properties, '{"I_": 50}') 1 SELECT JSON_SCHEMA_VALID(@schema_pattern_properties, '{"I_": 150}'); JSON_SCHEMA_VALID(@schema_pattern_properties, '{"I_": 150}') 0 SELECT JSON_SCHEMA_VALID(@schema_pattern_properties, '{"i_": 50}'); JSON_SCHEMA_VALID(@schema_pattern_properties, '{"i_": 50}') 1 SELECT JSON_SCHEMA_VALID(@schema_pattern_properties, '{"i_": 150}'); JSON_SCHEMA_VALID(@schema_pattern_properties, '{"i_": 150}') 1 # End of 11.1 test