| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/MariaDB/mysql-test/main/ (MariaDB Server Version 8.1-8.4©) Datei vom 1.9.2026 mit Größe 227 kB |
|
Quellcode-Bibliothek func_json.result Sprache: Lisp |
|||||||||||||||
|
select json_valid('[1, 2]'); json_valid('[1, 2]') 1 select json_valid('"string"}'); json_valid('"string"}') 0 Warnings: Note 4038 Syntax error in JSON text in argument 1 to function 'json_valid' at position 9 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 Warnings: Note 4037 Unexpected end of JSON text in argument 1 to function 'json_valid' select json_valid(repeat('{"a":', 1000)); json_valid(repeat('{"a":', 1000)) 0 Warnings: Note 4037 Unexpected end of JSON text in argument 1 to function 'json_valid' select json_value('{"key1":123}', '$.key2'); json_value('{"key1":123}', '$.key2') NULL select json_value('{"key1":123}', '$.key1'); json_value('{"key1":123}', '$.key1') 123 select json_value('{"key1":[1,2,3]}', '$.key1'); json_value('{"key1":[1,2,3]}', '$.key1') NULL select json_value('{"key1": [1,2,3], "key1":123}', '$.key1'); json_value('{"key1": [1,2,3], "key1":123}', '$.key1') 123 select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z') as exp; exp Mon"t"y select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2'); json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2') NULL select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1'); json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1') {"a":1, "b":[1,2]} select json_query('{"key1": 1}', '$.key1'); json_query('{"key1": 1}', '$.key1') NULL select json_query('{"key1":123, "key1": [1,2,3]}', '$.key1'); json_query('{"key1":123, "key1": [1,2,3]}', '$.key1') [1,2,3] select json_query('{"key1":123, "key1": [1,2,3]}', concat('$', repeat('.k', 1000))) as e exp NULL select json_array(); json_array() [] select json_array(1); json_array(1) [1] select json_array(1, "text", false, null); json_array(1, "text", false, null) [1, "text", false, null] select json_array_append('["a", "b"]', '$', FALSE); json_array_append('["a", "b"]', '$', FALSE) ["a", "b", false] select json_array_append('{"k1":1, "k2":["a", "b"]}', '$.k2', 2); json_array_append('{"k1":1, "k2":["a", "b"]}', '$.k2', 2) {"k1": 1, "k2": ["a", "b", 2]} select json_array_append('["a", ["b", "c"], "d"]', '$[0]', 2); json_array_append('["a", ["b", "c"], "d"]', '$[0]', 2) [["a", 2], ["b", "c"], "d"] select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x'); json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x') ["a", "x", {"b": [1, 2]}, [3, 4]] select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2]', 'x'); json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2]', 'x') ["a", {"b": [1, 2]}, "x", [3, 4]] select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[3]', 'x'); json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[3]', 'x') ["a", {"b": [1, 2]}, [3, 4], "x"] select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[4]', 'x'); json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[4]', 'x') ["a", {"b": [1, 2]}, [3, 4], "x"] select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x') as exp; exp ["a", {"b": ["x", 1, 2]}, [3, 4]] select json_array_insert('true', '$', 1); json_array_insert('true', '$', 1) NULL select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2][1]', 'y') as exp; exp ["a", {"b": [1, 2]}, [3, "y", 4]] select json_contains('{"k1":123, "k2":345}', '123', '$.k1'); json_contains('{"k1":123, "k2":345}', '123', '$.k1') 1 select json_contains('"you"', '"you"'); json_contains('"you"', '"you"') 1 select json_contains('"youth"', '"you"'); json_contains('"youth"', '"you"') 0 select json_contains('[1]', '[1]', '$', '$[0]'); ERROR 42000: Incorrect parameter count in the call to native function 'json_contains' select json_contains('', '', '$'); json_contains('', '', '$') NULL Warnings: Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_contains' select json_contains('null', 'null', '$'); json_contains('null', 'null', '$') 1 select json_contains('"10"', '"10"', '$'); json_contains('"10"', '"10"', '$') 1 select json_contains('"10"', '10', '$'); json_contains('"10"', '10', '$') 0 select json_contains('10.1', '10', '$'); json_contains('10.1', '10', '$') 0 select json_contains('10.0', '10', '$'); json_contains('10.0', '10', '$') 1 select json_contains('[1]', '1'); json_contains('[1]', '1') 1 select json_contains('[2, 1]', '1'); json_contains('[2, 1]', '1') 1 select json_contains('[2, [2, 3], 1]', '1'); json_contains('[2, [2, 3], 1]', '1') 1 select json_contains('[4, [2, 3], 1]', '2'); json_contains('[4, [2, 3], 1]', '2') 1 select json_contains('[2, 1]', '[1, 2]'); json_contains('[2, 1]', '[1, 2]') 1 select json_contains('[2, 1]', '[1, 0, 2]'); json_contains('[2, 1]', '[1, 0, 2]') 0 select json_contains('[2, 0, 3, 1]', '[1, 2]'); json_contains('[2, 0, 3, 1]', '[1, 2]') 1 select json_contains('{"b":[1,2], "a":1}', '{"a":1, "b":2}'); json_contains('{"b":[1,2], "a":1}', '{"a":1, "b":2}') 1 select json_contains('{"a":1}', '{}'); json_contains('{"a":1}', '{}') 1 select json_contains('[1, {"a":1}]', '{}'); json_contains('[1, {"a":1}]', '{}') 1 select json_contains('[1, {"a":1}]', '{"a":1}'); json_contains('[1, {"a":1}]', '{"a":1}') 1 select json_contains('[{"abc":"def", "def":"abc"}]', '["foo","bar"]'); json_contains('[{"abc":"def", "def":"abc"}]', '["foo","bar"]') 0 select json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]') as exp; exp 1 select json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}'); json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}') 1 select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]") as exp; exp 1 select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[10]") as exp; exp 0 select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.ma") as exp; exp 0 select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1") as exp; exp 1 select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1", "$.ma") as exp; exp 1 select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.ma") as exp; exp 0 select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.key2") as exp exp 1 select json_contains_path('{ "a": true }', NULL, '$.a' ) as exp; exp NULL select json_contains_path('{ "a": true }', 'all', NULL ) as exp; exp NULL select json_contains_path('{"a":{"b":"c"}}', 'one', '$.a.*') as exp; exp 1 select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1") as exp; exp "asd" select json_extract('{"key1":"asd", "key2":[2,3]}', "$.keyX", "$.keyY") as exp; exp NULL select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2") as exp; exp ["asd", [2, 3]] select json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2") as exp; exp [5, [2, 3]] select json_extract('{"key0":true, "key1":"qwe"}', "$.key1") as exp; exp "qwe" select json_extract(json_object('foo', 'foobar'),'$') as exp; exp {"foo": "foobar"} select json_extract('[10, 20, [30, 40]]', '$[2][*]') as exp; exp [30, 40] select json_extract('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]') as exp; exp [{"a": 3}, 30, 40] select json_extract('1', '$') as exp; exp 1 select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]') as exp; exp 20 select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]') as exp; exp [20] select json_extract( '[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a') as exp; exp [[3, 4]] select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word') as exp; exp {"a": 1, "b": {"c": 1, "k1": "word"}, "d": [1, 2]} select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3) as exp; exp {"a": 1, "b": {"c": 1}, "d": [1, 2, 3]} select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2) as exp; exp {"a": [1, 2], "b": {"c": 1}, "d": [1, 2]} select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word') as exp; exp {"a": 1, "b": {"c": 1}, "d": [1, 2]} select json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') as exp; exp {"a": 10, "b": [2, 3], "c": "[true, false]"} select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') as exp; exp {"a": 10, "b": [2, 3]} select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]') as exp; exp {"a": 10, "b": "[true, false]"} set @j = '["a", ["b", "c"], "d"]'; select json_remove(@j, '$[0]'); json_remove(@j, '$[0]') [["b", "c"], "d"] select json_remove(@j, '$[1]'); json_remove(@j, '$[1]') ["a", "d"] select json_remove(@j, '$[2]'); json_remove(@j, '$[2]') ["a", ["b", "c"]] set @j = '{"a": 1, "b": [2, 3]}'; select json_remove(@j, '$.b'); json_remove(@j, '$.b') {"a": 1} select json_remove(@j, '$.a'); json_remove(@j, '$.a') {"b": [2, 3]} select json_object(); json_object() {} select json_object("ki", 1, "mi", "ya"); json_object("ki", 1, "mi", "ya") {"ki": 1, "mi": "ya"} create table t1 as select json_object('id', 87, 'name', 'carrot') as f; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `f` varchar(46) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci select * from t1; f {"id": 87, "name": "carrot"} drop table t1; select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") as ex; ex 1 select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]") as ex; ex 1 select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]") as ex; ex 0 select json_quote('"string"'); json_quote('"string"') "\"string\"" create table t1 as select json_quote('foo'); select * from t1; json_quote('foo') "foo" show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `json_quote('foo')` varchar(38) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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 posi 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 posi select json_merge('{"a":"b"}','{"c":"d"}'); json_merge('{"a":"b"}','{"c":"d"}') {"a": "b", "c": "d"} SELECT JSON_MERGE('[1, 2]', '{"id": 47}'); JSON_MERGE('[1, 2]', '{"id": 47}') [1, 2, {"id": 47}] select json_type('{"k1":123, "k2":345}'); json_type('{"k1":123, "k2":345}') OBJECT select json_type('[123, "k2", 345]'); json_type('[123, "k2", 345]') ARRAY select json_type("true"); json_type("true") BOOLEAN select json_type('123'); json_type('123') INTEGER select json_type('123.12'); json_type('123.12') DOUBLE select json_keys('{"a":{"c":1, "d":2}, "b":2}'); json_keys('{"a":{"c":1, "d":2}, "b":2}') ["a", "b"] select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.a"); json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.a") ["c", "d"] select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.b"); json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.b") NULL select json_keys('foo'); json_keys('foo') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_keys' at position 1 select json_keys('{"a":{"c":1, "d":2}, "b":2, "c":1, "a":3, "b":1, "c":2}') as ex; ex ["a", "b", "c"] select json_keys('{"c1": "value 1", "c1": "value 2"}') as ex; ex ["c1"] SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]'; select json_search(@j, 'one', 'abc') as ex; ex "$[0]" select json_search(@j, 'all', 'abc') as ex; ex ["$[0]", "$[2].x"] select json_search(@j, 'all', 'abc', NULL, '$[2]') as ex; ex "$[2].x" select json_search(@j, 'all', 'abc', NULL, '$') as ex; ex ["$[0]", "$[2].x"] select json_search(@j, 'all', '10', NULL, '$') as ex; ex "$[1][0].k" select json_search(@j, 'all', '10', NULL, '$[*]') as ex; ex "$[1][0].k" select json_search(@j, 'all', '10', NULL, '$[*][0].k') as ex; ex "$[1][0].k" select json_search(@j, 'all', '10', NULL, '$**.k') as ex; ex "$[1][0].k" create table t1( json_col text ); insert into t1 values ('{ "a": "foobar" }'), ('{ "a": "foobar", "b": "focus", "c": [ "arm", "foot", "shoulder" ] }'); select json_search( json_col, 'all', 'foot' ) as ex from t1; ex NULL "$.c[1]" drop table t1; select json_unquote('"abc"'); json_unquote('"abc"') abc select json_unquote('abc'); json_unquote('abc') abc create table t1 (c VARCHAR(8)) DEFAULT CHARSET=latin1; insert into t1 values ('abc'),('def'); select json_object('foo', json_unquote(json_object('bar', c)),'qux', c) as fld from t1; fld {"foo": "{\"bar\": \"abc\"}", "qux": "abc"} {"foo": "{\"bar\": \"def\"}", "qux": "def"} select json_unquote(json_object('bar', c)) as c1 from t1; c1 {"bar": "abc"} {"bar": "def"} select collation(json_unquote(json_object('bar', c))) as coll_json_unquote, coercibility(json_unquote(json_object('bar', c))) as coer_json_unquote, coercibility('bar') as coer_literal from t1 limit 1; coll_json_unquote coer_json_unquote coer_literal utf8mb4_bin 4 6 create table t2 as select json_object('foo', json_unquote(json_object('bar', c)),'qux', show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `fld` varchar(110) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci drop table t2; 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci drop table json; select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' ) as ex; ex 1 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' ) as ex; ex 1 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0]' ) as ex; ex 1 select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0][0]' ) as ex; ex 1 select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2]' ) as ex; ex 2 select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0]' ) as ex; ex 2 select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0]' ) as ex; ex 2 select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0][0]' ) as ex; ex 2 select json_length( '{"a":{"b":{"d":1}}, "a":{"c":{"d":1, "j":2}}}', '$.a[0][0][0].c' ex 2 select json_set('1', '$[0]', 100); json_set('1', '$[0]', 100) 100 select json_set('1', '$[0][0]', 100); json_set('1', '$[0][0]', 100) 100 select json_set('1', '$[1]', 100); json_set('1', '$[1]', 100) [1, 100] select json_set('{"a":12}', '$[0]', 100); json_set('{"a":12}', '$[0]', 100) 100 select json_set('{"a":12}', '$[0].a', 100); json_set('{"a":12}', '$[0].a', 100) {"a": 100} select json_set('{"a":12}', '$[0][0].a', 100); json_set('{"a":12}', '$[0][0].a', 100) {"a": 100} select json_set('{"a":12}', '$[0][1].a', 100); json_set('{"a":12}', '$[0][1].a', 100) {"a": 12} select json_value('{"\\"key1":123}', '$."\\"key1"') as ex; ex 123 select json_value('{"\\"key1\\"":123}', '$."\\"key1\\""') as ex; ex 123 select json_value('{"key 1":123}', '$."key 1"') as ex; ex 123 select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', " ex 1 select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', " ex 0 select json_extract( '[1]', '$[0][0]' ); json_extract( '[1]', '$[0][0]' ) 1 select json_extract( '[1]', '$[1][0]' ); json_extract( '[1]', '$[1][0]' ) NULL select json_extract( '[1]', '$**[0]' ); json_extract( '[1]', '$**[0]' ) [1] select json_extract( '[1]', '$**[0][0]' ); json_extract( '[1]', '$**[0][0]' ) [1] select json_insert('1', '$[0]', 4); json_insert('1', '$[0]', 4) 1 select json_replace('1', '$[0]', 4); json_replace('1', '$[0]', 4) 4 select json_set('1', '$[0]', 4); json_set('1', '$[0]', 4) 4 select json_set('1', '$[1]', 4); json_set('1', '$[1]', 4) [1, 4] select json_replace('1', '$[1]', 4); json_replace('1', '$[1]', 4) 1 SELECT json_insert('[]', '$[0][0]', 100); json_insert('[]', '$[0][0]', 100) [] SELECT json_insert('1', '$[0][0]', 100); json_insert('1', '$[0][0]', 100) 1 SELECT json_replace('1', '$[0][0]', 100); json_replace('1', '$[0][0]', 100) 100 SELECT json_replace('[]', '$[0][0]', 100); json_replace('[]', '$[0][0]', 100) [] SELECT json_set('[]', '$[0][0]', 100); json_set('[]', '$[0][0]', 100) [] SELECT json_set('[]', '$[0][0][0]', 100); json_set('[]', '$[0][0][0]', 100) [] SELECT JSON_search( '{"": "a"}', "one", 'a'); JSON_search( '{"": "a"}', "one", 'a') "$." select json_merge('{"a":"b"}', '{"a":"c"}') as ex ; ex {"a": ["b", "c"]} select json_merge('{"a":{"x":"b"}}', '{"a":"c"}') as ex ; ex {"a": [{"x": "b"}, "c"]} select json_merge('{"a":{"u":12, "x":"b"}}', '{"a":{"x":"c"}}') as ex ; ex {"a": {"u": 12, "x": ["b", "c"]}} select json_merge('{"a":{"u":12, "x":"b", "r":1}}', '{"a":{"x":"c", "r":2}}') as ex ; ex {"a": {"u": 12, "x": ["b", "c"], "r": [1, 2]}} select json_compact('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') as ex; ex {"a":1,"b":[1,2,3],"c":{"aa":"v1","bb":"v2"}} select json_loose('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') as ex; ex {"a": 1, "b": [1, 2, 3], "c": {"aa": "v1", "bb": "v2"}} select json_detailed('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}') as ex; ex { "a": 1, "b": [ 1, 2, 3 ], "c": { "aa": "v1", "bb": "v2" } } SELECT JSON_search( '{"x": "\\""}', "one", '"') as ex; ex "$.x" SELECT JSON_search( '{"x": "\\""}', "one", '\\"') as ex; ex "$.x" set @save_max_allowed_packet=@@max_allowed_packet; set @save_net_buffer_length=@@net_buffer_length; set @@global.net_buffer_length=1024; set @@global.max_allowed_packet=4096; 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 4096 select json_array(repeat('a',1024),repeat('a',1024)) as ex; ex ["aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa select json_object("a", repeat('a',1024),"b", repeat('a',1024)) as ex; ex {"a": "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 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]') NULL Warnings: Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_value' CREATE TABLE t1 (f INT NOT NULL); INSERT INTO t1 VALUES (0); SELECT JSON_KEYS(f) FROM t1 ORDER BY 1; JSON_KEYS(f) NULL DROP TABLE t1; SELECT JSON_EXTRACT( '{"foo":"bar"}', '$[*].*' ); JSON_EXTRACT( '{"foo":"bar"}', '$[*].*' ) NULL SELECT JSON_EXTRACT( '{"foo":"bar"}', '$[*]'); JSON_EXTRACT( '{"foo":"bar"}', '$[*]') NULL select JSON_EXTRACT('{"name":"value"}', '$.name') = 'value' as ex; ex 1 select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = true as ex; ex 1 select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = false as ex; ex 0 select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = 1 as ex; ex 1 select JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"') as ex; ex "\u00f6" select JSON_EXTRACT('{"foo": "bar" foobar foo invalid ', '$.foo') as ex; ex NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_extract' at position 15 SELECT JSON_OBJECT('foo', '`') as ex; ex {"foo": "`"} SELECT JSON_OBJECT("foo", "bar`bar") as ex; ex {"foo": "bar`bar"} SELECT JSON_SET('{}', '$.age', 87); JSON_SET('{}', '$.age', 87) {"age": 87} SELECT JSON_MERGE('[]', '{"c":"d"}'); JSON_MERGE('[]', '{"c":"d"}') [{"c": "d"}] SET @str = "{\"\\u00e4\\u00f6\":\"yes\"}"; SET @path = "$.\"\\u00e4\\u00f6\""; select @str, @path, JSON_EXTRACT(@str, @path); @str @path JSON_EXTRACT(@str, @path) {"\u00e4\u00f6":"yes"} $."\u00e4\u00f6" "yes" SET @str = "{\"\\u00e4\":\"yes\"}"; SET @path = "$.\"\\u00e4\""; select @str, @path, JSON_EXTRACT(@str, @path); @str @path JSON_EXTRACT(@str, @path) {"\u00e4":"yes"} $."\u00e4" "yes" select json_array(5,json_query('[1,2]','$')); json_array(5,json_query('[1,2]','$')) [5, [1,2]] SELECT JSON_ARRAY('1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú') AS json_data; json_data ["1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú"] SELECT JSON_OBJECT("user","Jožko Mrkvičká") as json_data; json_data {"user": "Jožko Mrkvičká"} select json_contains_path('{"foo":"bar"}', 'one', '$[]'); json_contains_path('{"foo":"bar"}', 'one', '$[]') NULL Warnings: Warning 4042 Syntax error in JSON path in argument 3 to function 'json_contains_path' at posit select JSON_VALID(0x36f0c8dccd83c5eac156da); JSON_VALID(0x36f0c8dccd83c5eac156da) 0 Warnings: Note 4035 Broken JSON string in argument 1 to function 'json_valid' at position 1 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 wron # 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DROP TABLE t1; # # MDEV-16054 simple json functions flatline cpu on garbage input. # select json_array(1,user(),compress(5.140264e+307)); json_array(1,user(),compress(5.140264e+307)) NULL # # MDEV-16869 String functions don't respect character set of JSON_VALUE. # create table t1(json_col TEXT) DEFAULT CHARSET=latin1; insert into t1 values (_latin1 X'7B226B657931223A2253EC227D'); select JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' from t1; JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' 1 select REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' as exp from t1 exp 1 drop table t1; # # MDEV-16750 JSON_SET mishandles unicode every second pair of arguments. # SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6) as exp; exp {"a": "�"} SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6) as exp; exp {"a": "�", "b": "�"} SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6') as exp; exp {"a": "�", "x": 1, "b": "�"} # # MDEV-17121 JSON_ARRAY_APPEND # select json_array_append('[ ]', '$', 'aue'); json_array_append('[ ]', '$', 'aue') ["aue"] # # MDEV-17018 JSON_SEARCH and User-Defined Variables. # SET @`json` := '["A", [{"B": "1"}], {"C": "AB"}, {"D": "BC"}]', @`value` := 'AB'; SELECT JSON_SEARCH(@`json`, 'one', @`value`); JSON_SEARCH(@`json`, 'one', @`value`) "$[2].C" SET @`json` := NULL, @`value` := NULL; # # MDEV-17001 JSON_MERGE returns nullwhen merging empty array. # SELECT JSON_MERGE('[1]', '[]'); JSON_MERGE('[1]', '[]') [1] # # MDEV-16174 Assertion `0' failed in Type_handler_string_result:: # make_sort_key(uchar*, Item*, const SORT_FIELD_ATTR*, Sort_param*) # SET sql_mode=''; CREATE TABLE t1 (fld varchar(16) NOT NULL); CREATE TABLE t2 SELECT JSON_ARRAY_INSERT(fld, '$.[0]', '0') FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `JSON_ARRAY_INSERT(fld, '$.[0]', '0')` varchar(21) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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 Warnings: Note 4038 Syntax error in JSON text in argument 1 to function 'json_valid' at position 7 # # MDEV-18886 JSON_ARRAY() does not recognise JSON argument. # SELECT JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket')) as exp ["str", {"plugin": "unix_socket"}] SELECT CHARSET(JSON_ARRAY()) as exp; exp latin1 SELECT CHARSET(JSON_OBJECT()) as exp; exp latin1 # # MDEV-13992 Implement JSON_MERGE_PATCH # CREATE TABLE merge_t( id INT PRIMARY KEY AUTO_INCREMENT, target VARCHAR(100), patch VARCHAR(100) ); INSERT INTO merge_t(target, patch) VALUES ('{"a":"b"}', '{"a":"c"}'), ('{"a":"b"}', '{"b":"c"}'), ('{"a":"b"}', '{"a":null}'), ('{"a":"b", "b":"c"}', '{"a":null}'), ('{"a":["b"]}', '{"a":"c"}'), ('{"a":"c"}', '{"a":["b"]}'), ('{"a": {"b":"c"}}', '{"a": {"b":"d", "c":null}}'), ('{"a":[{"b":"c"}]}', '{"a": [1]}'), ('["a","b"]', '["c","d"]'), ('{"a":"b"}', '["c"]'), ('{"a":"foo"}', 'null'), ('{"a":"foo"}', '"bar"'), ('{"e":null}', '{"a":1}'), ('[1,2]', '{"a":"b", "c":null}'), ('{}', '{"a":{"bb":{"ccc":null}}}'), (NULL, '{}'), ('{}', NULL); SELECT id, target, patch, JSON_MERGE_PATCH(target, patch) AS merged, JSON_EXTRACT(JSON_MERGE_PATCH(target, patch), '$.a') AS a FROM merge_t ORDER BY id; id target patch merged a 1 {"a":"b"} {"a":"c"} {"a": "c"} "c" 2 {"a":"b"} {"b":"c"} {"a": "b", "b": "c"} "b" 3 {"a":"b"} {"a":null} {} NULL 4 {"a":"b", "b":"c"} {"a":null} {"b": "c"} NULL 5 {"a":["b"]} {"a":"c"} {"a": "c"} "c" 6 {"a":"c"} {"a":["b"]} {"a": ["b"]} ["b"] 7 {"a": {"b":"c"}} {"a": {"b":"d", "c":null}} {"a": {"b": "d"}} {"b": "d"} 8 {"a":[{"b":"c"}]} {"a": [1]} {"a": [1]} [1] 9 ["a","b"] ["c","d"] ["c", "d"] NULL 10 {"a":"b"} ["c"] ["c"] NULL 11 {"a":"foo"} null null NULL 12 {"a":"foo"} "bar" "bar" NULL 13 {"e":null} {"a":1} {"e": null, "a": 1} 1 14 [1,2] {"a":"b", "c":null} {"a": "b"} "b" 15 {} {"a":{"bb":{"ccc":null}}} {"a": {"bb": {}}} {"bb": {}} 16 NULL {} NULL NULL 17 {} NULL NULL NULL DROP TABLE merge_t; SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}') as exp; exp NULL SELECT JSON_MERGE_PATCH(NULL, '[1,2,3]') as exp; exp [1, 2, 3] SELECT JSON_MERGE_PATCH(NULL, 'a') as exp; exp NULL Warnings: Warning 4038 Syntax error in JSON text in argument 2 to function 'json_merge_patch' at positio SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}') as exp; exp {"d": "e"} SELECT JSON_MERGE_PATCH() as exp; ERROR 42000: Incorrect parameter count in the call to native function 'JSON_MERGE_PATCH' SELECT JSON_MERGE_PATCH('{}') as exp; ERROR 42000: Incorrect parameter count in the call to native function 'JSON_MERGE_PATCH' SELECT JSON_MERGE_PATCH('{', '[1,2,3]') as exp; exp NULL Warnings: Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_merge_patch' SELECT JSON_MERGE_PATCH('{"a":"b"}', '[1,') as exp; exp NULL Warnings: Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_merge_patch' # # MDEV-22976 CAST(JSON_EXTRACT() AS DECIMAL) does not handle boolean values # SELECT CAST(JSON_EXTRACT('{"x":true}', '$.x') AS DOUBLE) AS cf, CAST(JSON_EXTRACT('{"x":true}', '$.x') AS DECIMAL) AS cd; cf cd 1 1 SELECT CAST(JSON_EXTRACT('{"x":false}', '$.x') AS DOUBLE) AS cf, CAST(JSON_EXTRACT('{"x":false}', '$.x') AS DECIMAL) AS cd; cf cd 0 0 # # MDEV-24585 Assertion `je->s.cs == nice_js->charset()' failed in json_nice. # SELECT JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' ); JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' ) ["x"] # # MDEV-18284 JSON casting using JSON_COMPACT doesn't always work # with values from subqueries # CREATE TABLE json_test(a JSON, b JSON); INSERT INTO json_test VALUES ("[1,2,3]", '{"a":"foo"}'); SELECT * FROM json_test; a b [1,2,3] {"a":"foo"} SELECT json_object("a", json_compact(a), "b", b) FROM (SELECT * FROM json_test) AS json_test_values; json_object("a", json_compact(a), "b", b) {"a": [1,2,3], "b": {"a":"foo"}} SELECT json_object("a", json_compact(a), "b", json_compact(b)) FROM (SELECT * FROM json_test) AS json_test_values; json_object("a", json_compact(a), "b", json_compact(b)) {"a": [1,2,3], "b": {"a":"foo"}} DROP TABLE json_test; # End of 10.2 tests # # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT fu # 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 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags 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 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 # # 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}' TH a SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=4 FROM t1))='{"x": false}' T a 1 2 3 SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=3 FROM t1))='{"x": true}' TH a 1 2 3 SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=3 FROM t1))='{"x": false}' T a SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=2 FROM t1))='{"x": true}' TH a SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=2 FROM t1))='{"x": false}' T a 1 2 3 SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=4) FROM t1))='{"x": true}' TH a SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=4) FROM t1))='{"x": false}' T a 1 2 3 SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=3) FROM t1))='{"x": true}' TH a 1 2 3 SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=3) FROM t1))='{"x": false}' T a SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{"x": true}' TH a 1 2 3 SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{"x": false}' T 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\"","'s 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,"Hel 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, "H 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"},{ 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 [{"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":" SELECT JSON_OBJECTAGG(a) FROM t1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Mari DROP TABLE t1; # # MDEV-19160 JSON_DETAILED output unnecessarily verbose # create table t200 (a text); insert into t200 values ('{ "steps": [ { "join_optimization": { "select_id": 1, "steps": [ { "rows_estimation": [ { "table": "t1", "range_analysis": { "table_scan": { "rows": 1000, "cost": 2e308 }, "potential_range_indexes": [ { "index": "a_b", "usable": true, "key_parts": ["a", "b"] } ], "best_covering_index_scan": { "index": "a_b", "cost": 52.195, "chosen": true }, "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "no group by or distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "a_b", "ranges": ["2 <= a <= 2 AND 4 <= b <= 4", "123"], "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 1.1752, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [], "test_one_line_array":["123"] }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "a_b", "rows": 1, "ranges": ["2 <= a <= 2 AND 4 <= b <= 4"] }, "rows_for_plan": 1, "cost_for_plan": 1.1752, "chosen": true } } }, { "selectivity_for_indexes": [ { "index_name": "a_b", "selectivity_from_index": 0.001 } ], "selectivity_for_columns": [], "cond_selectivity": 0.001 } ] } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] }'); select JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) as exp from exp [ { "range_scan_alternatives": [ { "index": "a_b", "ranges": [ "2 <= a <= 2 AND 4 <= b <= 4", "123" ], "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 1.1752, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [], "test_one_line_array": ["123"] } ] select JSON_PRETTY(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) as exp from t2 exp [ { "range_scan_alternatives": [ { "index": "a_b", "ranges": [ "2 <= a <= 2 AND 4 <= b <= 4", "123" ], "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 1.1752, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [], "test_one_line_array": ["123"] } ] select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) as exp from t20 exp [{"range_scan_alternatives": [{"index": "a_b", "ranges": ["2 <= a <= 2 AND 4 <= b <= 4", "123"], "ro drop table t200; # # MDEV-24538: JSON_LENGTH does not return error upon wrong number of parameters # SELECT JSON_LENGTH('{"a":"b"}','$','$', 'foo'); ERROR 42000: Incorrect parameter count in the call to native function 'json_length' SELECT JSON_LENGTH(); ERROR 42000: Incorrect parameter count in the call to native function 'JSON_LENGTH' # MDEV-23187: Assorted assertion failures in json_find_path with certain collations SET @old_collation_connection= @@COLLATION_CONNECTION; SET COLLATION_CONNECTION= ucs2_unicode_ci; SELECT JSON_VALUE('["foo"]', '$**[0]') AS f; f NULL Warnings: Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_value' SET @@COLLATION_CONNECTION= @old_collation_connection; # # MDEV-32587 JSON_VALID fail to validate integer zero in scientific notation # select JSON_VALID(' {"number": 1E-4}'); JSON_VALID(' {"number": 1E-4}') 1 select JSON_VALID(' {"number": 0E-4}'); JSON_VALID(' {"number": 0E-4}') 1 select JSON_VALID(' {"number": 0.0}'); JSON_VALID(' {"number": 0.0}') 1 select JSON_VALID(' {"number": 0.1E-4}'); JSON_VALID(' {"number": 0.1E-4}') 1 select JSON_VALID(' {"number": 0e-4}'); JSON_VALID(' {"number": 0e-4}') 1 select JSON_VALID(' {"number": -0E-4}'); JSON_VALID(' {"number": -0E-4}') 1 select JSON_VALUE(' {"number": 0E-4}', '$.number'); JSON_VALUE(' {"number": 0E-4}', '$.number') 0E-4 select JSON_VALID(' {"number": 00E-4}'); JSON_VALID(' {"number": 00E-4}') 0 Warnings: Note 4038 Syntax error in JSON text in argument 1 to function 'json_valid' at position 14 select JSON_VALID(' {"number": 01E-4}'); JSON_VALID(' {"number": 01E-4}') 0 Warnings: Note 4038 Syntax error in JSON text in argument 1 to function 'json_valid' at position 14 select JSON_VALID(' {"number": 0E-4.0}'); JSON_VALID(' {"number": 0E-4.0}') 0 Warnings: Note 4038 Syntax error in JSON text in argument 1 to function 'json_valid' at position 17 # 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 windo 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 wind 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 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 n # CREATE TABLE t1(id int primary key, name varchar(50)); CREATE TABLE t2(id int, owner_id int); INSERT INTO t1 VALUES (1, "name1"), (2, "name2"), (3, "name3"); INSERT INTO t2 VALUES (1, 1), (2, 1), (3, 2), (4, 3); SELECT t1.id, JSON_ARRAYAGG(JSON_OBJECT('id',t2.id) ORDER BY t2.id) as materials from t1 LEFT JOIN t2 on t1.id = t2.owner_id GROUP BY t1.id ORDER BY id; id materials 1 [{"id": 1},{"id": 2}] 2 [{"id": 3}] 3 [{"id": 4}] DROP TABLE t1; DROP TABLE t2; # # MDEV-27018 IF and COALESCE lose "json" property # SELECT json_object('a', if(1, json_object('b', 'c'), json_object('e', 'f'))) as exp; exp {"a": {"b": "c"}} SELECT json_object('a', coalesce(json_object('b', 'c'))) as exp; exp {"a": {"b": "c"}} # # MDEV-26392: Crash with json_get_path_next and 10.5.12 # CREATE TABLE arrNestTest ( id VARCHAR(80) AS (JSON_COMPACT(JSON_EXTRACT(doc, "$._id"))) UNIQUE KEY, doc JSON, CONSTRAINT id_not_null CHECK(id IS NOT NULL)); INSERT INTO test.arrNestTest (doc) VALUES ('{ "_id" : { "$oid" : "611c0a463b150154132f6636" SELECT * FROM arrNestTest; id doc {"$oid":"611c0a463b150154132f6636"} { "_id" : { "$oid" : "611c0a463b150154132f6636" }, "a 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} # # MDEV-24784 JSON_ARRAYAGG charset issue # set names utf8; select json_arrayagg('ä'), json_objectagg(1, 'ä'); json_arrayagg('ä') json_objectagg(1, 'ä') ["ä"] {"1":"ä"} set names latin1; select json_arrayagg('ä'), json_objectagg(1, 'ä'); json_arrayagg('ä') json_objectagg(1, 'ä') ["ä"] {"1":"ä"} # # MDEV-32287: JSON_EXTRACT not returning multiple values for same path # select JSON_EXTRACT("[1, 2, [30, 40]]", '$[2][1]', '$[2][1]'); JSON_EXTRACT("[1, 2, [30, 40]]", '$[2][1]', '$[2][1]') [40, 40] # # MDEV-31402: SIGSEGV in json_get_path_next | Item_func_json_extract::read_json # CREATE TABLE t (id CHAR AS (JSON_COMPACT (JSON_EXTRACT(doc,"$._id"))) UNIQUE KEY,doc JSON, INSERT INTO t (doc) VALUES ('{ "_id" : { "$oid" : "0ca0b0f0" },"a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ ERROR 22001: Data too long for column 'id' at row 1 DROP TABLE t; # # MDEV-19487: JSON_TYPE doesnt detect the type of String Values (returns NULL) and for Date/Da # SELECT JSON_TYPE(json_value(JSON_OBJECT("id", 1, "name", 'Monty', "date", Cast('2019-0 x NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_type' at position 5 # # MDEV-22141: JSON_REMOVE returns NULL on valid arguments # SELECT JSON_REMOVE('{"A": { "B": 1 }}', '$.A.B.C.D'); JSON_REMOVE('{"A": { "B": 1 }}', '$.A.B.C.D') {"A": {"B": 1}} # # MDEV-34143: Server crashes when executing JSON_EXTRACT after setting non-default collati # SET @save_collation_connection= @@collation_connection; SET collation_connection='utf16_bin'; SELECT JSON_EXTRACT('{"a": 1,"b": 2}','$.a'); JSON_EXTRACT('{"a": 1,"b": 2}','$.a') NULL Warnings: Warning 4036 Character disallowed in JSON in argument 1 to function 'json_extract' at positio SET @@collation_connection= @save_collation_connection; # 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(J JSON_ARRAY_APPEND(JSON_ARRAY(l1, l2, l3, l4), '$[0]', 'k3') JSON_ARRAY_INSERT(JSON_ARR [["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, JSON_INSERT(JSON_OBJECT(l1, l2, l3, l4), '$.k3', 'v3') JSON_SET(JSON_OBJECT(l1, l2, l3, l {"k1": "v1", "k2": "v2", "k3": "v3"} {"k1": "v1", "k2": "new v2"} {"k1": "v1", "k2": "new v2"} DROP TABLE t; # # MDEV-27412: JSON_TABLE doesn't properly unquote strings # SET @data = '[{"Data": "<root language=\\"de\\"></root>"}]'; SELECT data FROM JSON_TABLE (@data, '$[*]' COLUMNS (data text PATH '$.Data')) AS t; data <root language="de"></root> # # MDEV-21530: json_extract STILL crashes in Item_func_json_extract::read_json # select null<=>json_extract('1',json_object(null,'{ }',null,null),'{}'); null<=>json_extract('1',json_object(null,'{ }',null,null),'{}') 1 Warnings: Warning 4042 Syntax error in JSON path in argument 2 to function 'json_extract' at position 1 # # MDEV-39213: json range syntax crash # SELECT JSON_EXISTS(CONCAT('[', REPEAT('[', 4000), 'Y', REPEAT(']', 4000), ', 1]'), '$[100 ex NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_exists' at position 4002 # # MDEV-35548 UBSAN: runtime error: index -1 out of bounds for type 'json_path_step_t[32]' # (aka 'struct st_json_path_step_t[32]') # SELECT JSON_EXTRACT('{a:true}','$.a')=TRUE; JSON_EXTRACT('{a:true}','$.a')=TRUE NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_extract' at position 2 SELECT JSON_EXTRACT('0E+0','$'); JSON_EXTRACT('0E+0','$') 0E+0 SELECT JSON_EXISTS(CONCAT('[', REPEAT('[', 4000), 'Y', REPEAT(']', 4000), ', 1]'), '$[100 je NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_exists' at position 4002 SELECT JSON_EXISTS('[[[[[Y[[[[n[[[[[[[[[[[n[[[Vn]]]]]]]]]]]]]],]]:]]]](', '$[0 to rr je NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_exists' at position 6 SELECT JSON_EXISTS('[[[[[Y[[[[[[[[[[[[[[[[[[Vn]]]]]]]]]]]]]],]]:]]]](', '$[0 to rrrr je NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_exists' at position 6 # # MDEV-39817 JSON_ARRAYAGG() returns garbage because of off-by-one error # set session group_concat_max_len = 9; create temporary table t (c varchar(20) character set latin1); insert into t values ('ab'), ('cd'), ('ef'); select json_arrayagg(c order by c) from t; json_arrayagg(c order by c) ["ab","cd"] Warnings: Warning 1260 Row 3 was cut by json_arrayagg() drop temporary table t; # End of 10.6 tests SELECT json_extract(t.j, '$') FROM (SELECT json_extract('["a",1]', '$') AS j) AS t; json_extract(t.j, '$') ["a", 1] SELECT json_extract(t.j, '$') FROM (SELECT json_extract('{"key":"value"}', '$') AS j) AS t; json_extract(t.j, '$') {"key": "value"} SELECT json_extract(t.j, '$[0]', '$[1]') FROM (SELECT json_extract('[{"a":1},{"b":2},{"c":3}]', '$') AS j) AS t; json_extract(t.j, '$[0]', '$[1]') [{"a": 1}, {"b": 2}] SELECT json_extract(t.j, '$') FROM (SELECT json_extract('{"name":"Ä Ö Ü ß","city":"München"}', '$') AS j) AS t; json_extract(t.j, '$') {"name": "Ä Ö Ü ß", "city": "München"} SELECT json_extract(t.j, '$.name') FROM (SELECT json_extract('{"name":"日本語テスト","val":[1,2]}', '$') AS j) AS t; json_extract(t.j, '$.name') "日本語テスト" # # MDEV-35614 JSON_UNQUOTE doesn't work with emojis # SELECT HEX(JSON_UNQUOTE('"\\ud83d\\ude0a"')) as hex_smiley; hex_smiley F09F988A set names utf8mb4; SELECT JSON_UNQUOTE('"\\ud83d\\ude0a"') as smiley; smiley �� SELECT JSON_UNQUOTE('"\\ud83d\\ude0a"') = JSON_UNQUOTE('"\\ud83d\\ude0a"') as equal_s equal_smileys 1 SELECT JSON_UNQUOTE('"\\ud83d\\ude0a"') <= JSON_UNQUOTE('"\\ud83d\\ude0a"') as less_or less_or_equal_smileys 1 set @v='{ "color":"��" }'; select @v as v, collation(@v) as collation_v; v collation_v { "color":"��" } utf8mb4_uca1400_ai_ci select json_valid(@v) as valid; valid 1 select json_extract(@v,'$.color') as color_extraction, collation(json_extract(@v,'$. color_extraction color_extraction_collation "��" utf8mb4_uca1400_ai_ci select json_unquote(json_extract(@v,'$.color')) as unquoted, collation(json_unquote( unquoted unquoted_collation �� utf8mb4_bin SELECT JSON_UNQUOTE('"\\uc080\\ude0a"') as invalid_utf8mb4; invalid_utf8mb4 "\uc080\ude0a" Warnings: Warning 4035 Broken JSON string in argument 1 to function 'json_unquote' at position 13 show warnings; Level Code Message Warning 4035 Broken JSON string in argument 1 to function 'json_unquote' at position 13 # # MDEV-31147 json_normalize does not work correctly with MSAN build # CREATE TABLE t1 (val JSON); ALTER TABLE t1 ADD COLUMN normalized_json JSON AS (JSON_NORMALIZE(val)); INSERT INTO t1 (val) VALUES ('15'); SELECT * FROM t1; val normalized_json 15 1.5E1 DROP TABLE t1; # End of 10.8 tests # # MDEV-27677: Implement JSON_OVERLAPS() # # Testing scalar json datatypes # Comparing scalar json datatypes with itself SELECT JSON_OVERLAPS('true', 'true'); JSON_OVERLAPS('true', 'true') 1 SELECT JSON_OVERLAPS('false', 'false'); JSON_OVERLAPS('false', 'false') 1 SELECT JSON_OVERLAPS('1', '1'); JSON_OVERLAPS('1', '1') 1 SELECT JSON_OVERLAPS('"string1"', '"string1"'); JSON_OVERLAPS('"string1"', '"string1"') 1 SELECT JSON_OVERLAPS('null', 'null'); JSON_OVERLAPS('null', 'null') 1 # Comparing scalar json datatypes with other scalar datatype SELECT JSON_OVERLAPS('true', 'false'); JSON_OVERLAPS('true', 'false') 0 SELECT JSON_OVERLAPS('1', '"1"'); JSON_OVERLAPS('1', '"1"') 0 SELECT JSON_OVERLAPS('1', '0'); JSON_OVERLAPS('1', '0') 0 SELECT JSON_OVERLAPS('null', '0'); JSON_OVERLAPS('null', '0') 0 SELECT JSON_OVERLAPS('"string1"', '"string2"'); JSON_OVERLAPS('"string1"', '"string2"') 0 SELECT JSON_OVERLAPS('true','["abc", 1, 2, true, false]'); JSON_OVERLAPS('true','["abc", 1, 2, true, false]') 1 SELECT JSON_OVERLAPS('true','["abc", 1, 2, [true]]'); JSON_OVERLAPS('true','["abc", 1, 2, [true]]') 0 SELECT JSON_OVERLAPS('true','{"A":true}'); JSON_OVERLAPS('true','{"A":true}') 0 # Testing non-scalar json data types # Comparing object with object (non-nested) SELECT JSON_OVERLAPS('{"A":[1, 2, 3]}','{}'); JSON_OVERLAPS('{"A":[1, 2, 3]}','{}') 0 SELECT JSON_OVERLAPS('{"A": 1}', '{"A": 1}'); JSON_OVERLAPS('{"A": 1}', '{"A": 1}') 1 SELECT JSON_OVERLAPS('{"A": 1}', '{"B": 1}'); JSON_OVERLAPS('{"A": 1}', '{"B": 1}') 0 SELECT JSON_OVERLAPS('{ "A": 1, "B": "string1" }', '{ "A": 2, "B": "string1" }') as exp; exp 1 SELECT JSON_OVERLAPS('{ "A": 1, "B": "string1" }', '{ "A": 2, "B": "string2" }') as exp; exp 0 # Comparing nested object with other nested object SELECT JSON_OVERLAPS('{ "A": 1, "B": {"C":2} }', '{ "A": 2, "B": {"C":1} }') as exp; exp 0 SELECT JSON_OVERLAPS('{ "A": 1, "B": {"C":2} }', '{ "A": 2, "B": {"C":2} }') as exp; exp 1 SELECT JSON_OVERLAPS('{ "A": { "B": true } }', '{ "A": { "B": true, "C": false } }') as exp; exp 0 SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":5}}', '{"C":3, "B":{"E":5, "D":4}}') as exp; exp 1 SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":5, "D":4}}') as exp; exp 0 SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":[5, 6, 7], "D":4}}') as exp; exp 1 SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "B":{"E":[7, 6 ,5], "D":4}}') as exp; exp 0 SELECT JSON_OVERLAPS('{"A":1, "B":{"D":4, "E":[5, 6, 7]}}', '{"C":3, "F":{"E":[5, 6, 7], "D":4}}') as exp; exp 0 # Comparing array with array (non-nested) SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, 1]') as exp; exp 1 SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, 5]'); JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, 5]') 0 SELECT JSON_OVERLAPS('[1,2,3]','[]') as exp; exp 0 # Comparing nested arrays SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, [1]]') as exp; exp 0 SELECT JSON_OVERLAPS('[1, 2, [true, false], null]', '[[1], [true, false]]') as exp; exp 1 SELECT JSON_OVERLAPS('[1, 2, 3, [4, 5, 6]]','[7, 8, 9, [6, 5, 4]]') as exp; exp 0 # Comparing one non-scalar json datatypes with another non-scalar # json datatype # Comparing array with object SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '{"A": 1}') as exp; exp 0 SELECT JSON_OVERLAPS('[1, 2, true, false, null, {"A":2}]', '{"A": 1}') as exp; exp 0 SELECT JSON_OVERLAPS('[1, {"A": 2}, {"A": 1}]', '{"A": 1}') as exp; exp 1 SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]', '{"A": 1, "B": 2}') as exp; exp 1 SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": 2}]', '{"A": 1, "B": 3}') as exp; exp 0 # Comparing nested array with object SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1, "B": 2}') as exp; exp 0 SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1, "B": 3}') as exp; exp 0 SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": 2}]]', '{"A": 1}') as exp; exp 0 # Comparing array with nested object SELECT JSON_OVERLAPS('[1, 2, true, false, {"A": 1, "B": {"C": 12}}]', '{"A": 1, "B": {"C": 12}}') as exp; exp 1 SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]', '{"A": 1, "B": {"C": 12}}') as exp; exp 0 # Comparing nested array with nested objects SELECT JSON_OVERLAPS('[1, 2, true, false, [{"A": 1, "B": {"C": 12}}]]', '{"A": 1, "B":{"C": 12}}') as exp; exp 0 SELECT JSON_OVERLAPS('[[1, 2, true, false, {"A": 1, "B": {"C": 12}}]]', '{"A": 1, "B": {"C": 12}}') as exp; exp 0 # Comparing object with array SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 2}]') as exp; exp 0 SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 3}]') as exp; exp 1 SELECT JSON_OVERLAPS('{"A": 1, "B": 3}', '[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": 3}]') as exp; exp 1 SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, 3]}', '[1, 2, true, false, {"A": 1, "B": 2}, {"A": 1, "B": [1, 2, 3]}]') as exp; exp 1 SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}', '[1, 2, true, false, {"A": 1, "B": 2}, {"A":1, "B":[1, 2, {"C": 3, "D": 5}]}]') as exp; exp 1 SELECT JSON_OVERLAPS('{"A": 1, "B": [1, 2, {"C": 3, "D": 5}]}', '[1, 2, true, false, {"A": 1, "B": 2},{"A": 1, "B": [1, 2, {"C": 3, "D": 4}]}]') as exp; exp 0 # Comparing object with nested array SELECT JSON_OVERLAPS('{"A": 1, "B": 3}','[1, 2, true, false, [{"A": 1, "B": 2}, {"A": 1, "B": 3 exp 0 # Checking errors and warnings SELECT JSON_OVERLAPS('[1,2,{"A":B}]', '{"A":B}', '{"C":"string1"}'); ERROR 42000: Incorrect parameter count in the call to native function 'JSON_OVERLAPS' SELECT JSON_OVERLAPS('[1,2,{"A":B}]'); ERROR 42000: Incorrect parameter count in the call to native function 'JSON_OVERLAPS' # # MDEV-27990: Incorrect behavior of JSON_OVERLAPS() on warning # SELECT JSON_OVERLAPS('',''); JSON_OVERLAPS('','') 0 Warnings: Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_overlaps' SELECT JSON_OVERLAPS('true','tr'); JSON_OVERLAPS('true','tr') 0 Warnings: Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_overlaps' # # MDEV-22224: Support JSON Path negative index # SET @json='{ "A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1":"value1"},[15]], true], "B": {"C": 1}, "D": 2 }'; SELECT JSON_ARRAY_APPEND(@json, '$.A[-2][-1]', 5); JSON_ARRAY_APPEND(@json, '$.A[-2][-1]', 5) {"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1": "value1"} 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"} 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"} 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"} 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": "value 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": "value 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": "val 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": "val 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": "value 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": "value 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') NULL Warnings: Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_value' 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_ 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_ 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_replac 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') NULL Warnings: Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_value' 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]') NULL Warnings: Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_value' SELECT JSON_VALUE(@json, '$[2 to 3][0]'); JSON_VALUE(@json, '$[2 to 3][0]') NULL Warnings: Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_value' # # 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 ' 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" INSERT INTO t1 (j) VALUES ('[{"key1": 3, "key2": 3}, {"key3": 3, "key4": 3}, {"key5": 3}]'); SELECT JSON_EXTRACT(j, '$[0 to 1]') FROM t1 ; JSON_EXTRACT(j, '$[0 to 1]') [{"key1": 1, "key2": 1}, {"key3": 1, "key4": 1}] [{"key1": 2, "key2": 2}, {"key3": 2, "key4": 2}] [{"key1": 3, "key2": 3}, {"key3": 3, "key4": 3}] SELECT JSON_EXTRACT(j, '$[*]') FROM t1 ; JSON_EXTRACT(j, '$[*]') [{"key1": 1, "key2": 1}, {"key3": 1, "key4": 1}] [{"key1": 2, "key2": 2}, {"key3": 2, "key4": 2}, {"key5": 2, "key6": 2}] [{"key1": 3, "key2": 3}, {"key3": 3, "key4": 3}, {"key5": 3}] DROP TABLE t1; # # MDEV-29381: JSON paths containing dashes are reported as syntax errors in procedures # SELECT JSON_EXTRACT('{ "my-key": 1 }', '$."my-key"'); JSON_EXTRACT('{ "my-key": 1 }', '$."my-key"') 1 SELECT JSON_EXTRACT('{ "my-key": 1 }', '$.my-key'); JSON_EXTRACT('{ "my-key": 1 }', '$.my-key') 1 # # MDEV-23187: Assorted assertion failures in json_find_path with certain collations # SET @save_collation_connection= @@collation_connection; SET @json='{ "A": [ [{"k":"v"},[1]],true],"B": {"C": 1} }'; SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1'); JSON_VALUE(@json,'$.A[last-1][last-1].key1') NULL SET @json='{ "A": [ [{"k":"v"},[1]],true],"B": {"C": 1} }'; SET collation_connection='ucs2_bin'; SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1'); JSON_VALUE(@json,'$.A[last-1][last-1].key1') NULL SET @json='{ "A": [ [{"k":"v"},[15]],true],"B": {"C": 1} }'; SET sql_mode=0,character_set_connection=utf32; SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1'); JSON_VALUE(@json,'$.A[last-1][last-1].key1') NULL SET @json='{ "A": [ [{"k":"v"},[15]],true],"B": {"C": 1} }'; SET sql_mode=0,character_set_connection=utf32; SELECT JSON_VALUE(@json,'$.A[last-1][last-1].key1'); JSON_VALUE(@json,'$.A[last-1][last-1].key1') NULL SET @@collation_connection= @save_collation_connection; # End of 10.9 Test # # MDEV-30691 Assertion `strlen(Ptr) == str_length' failed in void Binary_string::chop() # set @@collation_connection=utf32_czech_ci; select json_detailed('[[123],456]'); json_detailed('[[123],456]') [ [123], 456 ] set @@collation_connection=@save_collation_connection; select json_detailed('[[123],456]'); json_detailed('[[123],456]') [ [123], 456 ] # # MDEV-37428 JSON_VALUE returns NULL for a key with an empty string value rather than an empty st # SELECT JSON_VALUE(JSON_OBJECT("a", ""), '$.a') = "" AS not_null; not_null 1 # # MDEV-36319: Wrong result json_table # SET @JSON=' { "SZ": [ { "NAME": "S0", "OFFERS": [ { "NAME": "S0A0" } ] }, { "NAME": "S1", "OFFERS": [ { "NAME": "S1A0" }, { "NAME": "S1A1" } ] }, { "NAME": "S2", "OFFERS": [ { "NAME": "S2A0" } ] }, { "NAME": "S3", "OFFERS": [ { "NAME": "S3A0" } ] }, { "NAME": "S4", "OFFERS": [ { "NAME": "S4A0" } ] }, { "NAME": "S5", "OFFERS": [ { "NAME": "S5A0" } ] } ] } ' ; # Should return EMPTY result SELECT * FROM json_table(@JSON, '$.SZ[0].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; NAME # Should return S1A1 SELECT * FROM json_table(@JSON, '$.SZ[1].OFFERS[1]' COLUMNS(NAME VARCHAR(30) PATH '$.NAME')) AS t_sz; NAME S1A1 # # MDEV-25148: Unexpected success and result set upon erroneous # JSON_VALUE call # SELECT JSON_VALUE('{"a":[1,2]}', '$.a[*]'); JSON_VALUE('{"a":[1,2]}', '$.a[*]') NULL Warnings: Warning 4044 Wildcards or range in JSON path not allowed in argument 2 to function 'json_value' # # MDEV-37640: Crash at String::append with json_normalize # SELECT ( WITH x AS ( WITH x AS ( SELECT 1.000000 ) SELECT ( REPEAT ( ( json_normalize ( ' -1' ) ) , 357 ) ) x -1.0E0-1.0E0-1.0E0-1.0E0-1.0E0-1.0E0-1.0E0-1.0E0-1.0E0-1.0E0-1.0E0-1.0E0-1.0E0-1 # # MDEV-37640: Crash in JSON_KEYS # SELECT JSON_SET('{"c":4}', '$.a', 5) AS x HAVING (x IN (JSON_KEYS(x), ',')); x # # MDEV-38264 Assertion in json_find_path() fails after computing # array size of invalid json structure # SELECT JSON_QUERY('{ "A": [0,] }', '$.A[-1]'); JSON_QUERY('{ "A": [0,] }', '$.A[-1]') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_query' at position 8 SELECT JSON_VALUE('{ "A": [0,] }', '$.A[-1]'); JSON_VALUE('{ "A": [0,] }', '$.A[-1]') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_value' at position 8 SELECT JSON_VALUE('{ "A": [5,] }', '$.A[-2]'); JSON_VALUE('{ "A": [5,] }', '$.A[-2]') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_value' at position 8 SELECT JSON_VALUE('{ "A": [5] }', '$.A[-2]'); JSON_VALUE('{ "A": [5] }', '$.A[-2]') NULL SELECT JSON_LENGTH('{ "A": [0,] }', '$.A'); JSON_LENGTH('{ "A": [0,] }', '$.A') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_length' at position 11 SELECT JSON_VALID('{ "A": [0,] }'); JSON_VALID('{ "A": [0,] }') 0 Warnings: Note 4038 Syntax error in JSON text in argument 1 to function 'json_valid' at position 11 # # MDEV-38264 Assertion in json_find_path() fails after computing # array size of invalid json structure # SELECT JSON_QUERY('{ "A": [0,] }', '$.A[-1]'); JSON_QUERY('{ "A": [0,] }', '$.A[-1]') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_query' at position 8 SELECT JSON_VALUE('{ "A": [0,] }', '$.A[-1]'); JSON_VALUE('{ "A": [0,] }', '$.A[-1]') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_value' at position 8 SELECT JSON_VALUE('{ "A": [5,] }', '$.A[-2]'); JSON_VALUE('{ "A": [5,] }', '$.A[-2]') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_value' at position 8 SELECT JSON_VALUE('{ "A": [5] }', '$.A[-2]'); JSON_VALUE('{ "A": [5] }', '$.A[-2]') NULL SELECT JSON_LENGTH('{ "A": [0,] }', '$.A'); JSON_LENGTH('{ "A": [0,] }', '$.A') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_length' at position 11 SELECT JSON_VALID('{ "A": [0,] }'); JSON_VALID('{ "A": [0,] }') 0 Warnings: Note 4038 Syntax error in JSON text in argument 1 to function 'json_valid' at position 11 # # MDEV-35548 UBSAN: runtime error: index -1 out of bounds for type 'json_path_step_t[32]' # (aka 'struct st_json_path_step_t[32]') # SELECT JSON_EXTRACT('{a:true}','$.a')=TRUE; JSON_EXTRACT('{a:true}','$.a')=TRUE NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_extract' at position 2 SELECT JSON_EXTRACT('0E+0','$'); JSON_EXTRACT('0E+0','$') 0E+0 # # MDEV-26814: UBSAN: offset to nullptr in JSON_ARRAY_INSERT # SELECT JSON_ARRAY_INSERT (0,NULL,1) as j; j NULL # # MDEV-40414 Server crash in Charset::charset upon JSON operations # SELECT JSON_EQUALS('[]', JSON_ARRAY_INSERT(NULL,'$[0]',1)) AS x; x NULL SELECT JSON_OVERLAPS(JSON_ARRAY_INSERT(NULL,'$[0]',1), '[]') AS x; x NULL # # MDEV-32331 JSON path functions with no charset on path crash server # SELECT 1 FROM (SELECT CASE WHEN x * x THEN x END as a FROM ( SELECT json_array_append ( 'x' , ( 'x' % 'x' ) , 1 , 'x' , 1 ) as x ) dt2 ) dt WHERE a; 1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' SELECT 1 FROM (SELECT CASE WHEN x * x THEN x END as a FROM ( SELECT json_array_append ( 'x' , ( 'x' % 'x 1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' SELECT 1 FROM (SELECT CASE WHEN x * x THEN x END as a FROM ( SELECT json_array_insert ( '[1]' , ( 'x' % 1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' SELECT 1 FROM (SELECT CASE WHEN x * x THEN x END as a FROM ( SELECT json_insert ( '1' , ( 'x' % 'x' ) , 1 , ' 1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' SELECT 1 FROM (SELECT CASE WHEN x * x THEN x END as a FROM ( SELECT json_remove ( '[1,2]' , ( 'x' % 'x' ) 1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' SELECT 1 FROM (SELECT CASE WHEN x * x THEN x END as a FROM ( SELECT json_replace ( '1' , ( 'x' % 'x' ) , 1 , 1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' SELECT 1 FROM (SELECT CASE WHEN x * x THEN x END as a FROM ( SELECT json_set ( '1' , ( 'x' % 'x' ) , 1 , '$.a 1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'x' SELECT x FROM (SELECT 1 AS x UNION SELECT 2) AS t WHERE x IN ( SELECT JSON_REPLACE('1', UPPER(CAST x SELECT ( WITH RECURSIVE x ( x ) AS (WITH RECURSIVE x ( x ) AS ( SELECT 1 UNION SELECT x + 1 FROM x ) SELECT x NULL SELECT 1 FROM (SELECT JSON_REMOVE(46, DATE(NULL)) AS x EXCEPT SELECT 1) AS d WHERE NOT(NOT(x > 10 1 # # MDEV-40572 SIGSEGV in JSON_OVERLAPS() on a truncated nested JSON object with different char # SET @col=@@collation_connection; SET @char=@@character_set_connection; SET collation_connection=ucs2_general_ci; SELECT JSON_OVERLAPS ('{"B":{}}', '{"B":{') AS exp_ucs2; exp_ucs2 0 Warnings: Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_overlaps' SET character_set_connection=utf16; SELECT JSON_OVERLAPS ('{"B":{}}', '{"B":{') AS exp_utf16; exp_utf16 0 Warnings: Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_overlaps' SET @@collation_connection=@col; SET @@character_set_connection=@char; # # MDEV-40165 JSON_EQUAL/JSON_NORMALIZE/JSON_CONTAINS error handling # CREATE TABLE t(c1 JSON, c2 JSON); INSERT INTO t VALUES ('{"a":1,"b":2}', '{"b":2E0, "a":1E0}'), (NULL, '["a", "b"]'), ('{"r": 1}', NULL), ('[{"a": 3}, {"d": [1, 2, 3]}]', '[{"d": [1E0, 2E0, 3E0]}, {"a":3E0}]'); SELECT c1, JSON_EQUALS(c1, '{"r": 1E0}') AS je_c1_const FROM t; c1 je_c1_const {"a":1,"b":2} 0 NULL NULL {"r": 1} 1 [{"a": 3}, {"d": [1, 2, 3]}] 0 SELECT c1, c2, JSON_EQUALS(c1, c2) AS je_c1_c2 FROM t; c1 c2 je_c1_c2 {"a":1,"b":2} {"b":2E0, "a":1E0} 1 NULL ["a", "b"] NULL {"r": 1} NULL NULL [{"a": 3}, {"d": [1, 2, 3]}] [{"d": [1E0, 2E0, 3E0]}, {"a":3E0}] 0 SELECT c1, JSON_CONTAINS(c1, '{"r": 1E0}') AS jc_c1_const FROM t; c1 jc_c1_const {"a":1,"b":2} 0 NULL NULL {"r": 1} 1 [{"a": 3}, {"d": [1, 2, 3]}] 0 SELECT c1, c2, JSON_CONTAINS(c1, c2) AS jc_c1_c2 FROM t; c1 c2 jc_c1_c2 {"a":1,"b":2} {"b":2E0, "a":1E0} 1 NULL ["a", "b"] NULL {"r": 1} NULL NULL [{"a": 3}, {"d": [1, 2, 3]}] [{"d": [1E0, 2E0, 3E0]}, {"a":3E0}] 1 SELECT c1, JSON_OVERLAPS(c1, '{"r": 1E0}') AS jo_c1_const FROM t; c1 jo_c1_const {"a":1,"b":2} 0 NULL NULL {"r": 1} 1 [{"a": 3}, {"d": [1, 2, 3]}] 0 SELECT c1, c2, JSON_OVERLAPS(c1, c2) AS jo_c1_c2 FROM t; c1 c2 jo_c1_c2 {"a":1,"b":2} {"b":2E0, "a":1E0} 1 NULL ["a", "b"] NULL {"r": 1} NULL NULL [{"a": 3}, {"d": [1, 2, 3]}] [{"d": [1E0, 2E0, 3E0]}, {"a":3E0}] 1 DROP TABLE t; # End of 10.11 Test # # MDEV-32007: JSON_VALUE and JSON_EXTRACT doesn't handle dash (-) # as first character in key # CREATE TEMPORARY TABLE IF NOT EXISTS jsonTest AS SELECT '{ "-1234" : "something", "12-34" : "else", "1234-" : "and", "1234" : "match" }' AS 'message'; SELECT JSON_SEARCH(message, 'one', 'something') AS t1_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'something'))) AS t1_res JSON_SEARCH(message, 'one', 'else') AS t2_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'else'))) AS t2_result, JSON_SEARCH(message, 'one', 'and') AS t3_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'and'))) AS t3_result, JSON_SEARCH(message, 'one', 'match') AS t4_path, JSON_VALUE(message, JSON_UNQUOTE(JSON_SEARCH(message, 'one', 'match'))) AS t4_result FROM jsonTest; t1_path t1_result t2_path t2_result t3_path t3_result t4_path t4_result "$.-1234" something "$.12-34" else "$.1234-" and "$.1234" match # End of 11.0 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 }'; 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 JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2 1 SELECT JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1 JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2 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":"ab 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":"str }'; 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", "ob JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2": 0 SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "ob JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2": 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_ }'; SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "ob JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2": 1 SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "ob JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2": 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", "ob JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2": 1 SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":1, "number2":2, "number3":3, "numbe JSON_SCHEMA_VALID(@schema_obj,'{"number1":1, "number2":2, "number3":3, "number4":4, 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", "ob JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2": 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":{ JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"numbe 0 SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{ JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"numbe 1 SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{ JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"array 1 SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{ JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"numbe 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 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 JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "s 0 SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2 JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "a 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}}') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_schema_valid' at positi 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 INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd", "object1":{"key1":"val1 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 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"], DROP TABLE str_table, num_table, true_table, false_table, null_table, arr_table, obj_tab # 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 # additionalProperties exists, it must validate regardless of existence or value for unevalu # 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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 1 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": [1,2,3], "I_int":20 JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": [1,2,3], "I_int":20, "S_": 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3] JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_": 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 0 # removing patternProperties to check that validation falls back on additionalProperties an # existence of unevaluatedProperties still does not change anything because of existence of a # 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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3] JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_": 1 # Removing additionalProperties to check that validation falls back on unevaluatedProperti 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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc" 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc" 1 # Removing unevaluatedProperties has no effect on result when additionalProperties is prese 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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 1 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 1 # Checking that in absence of additionalProperties, validation falls back on evaluatedPrope 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_": JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", 1 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_": JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", 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", " JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":"str2", "S_":"a 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_": JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", 0 # Properties to check if patternProperties get evaluated but keys not found in patternPropert # 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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":2 JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_" 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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":2 JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_" 1 # Checking that in absence of additionalProperties and properties, the keys not found in patte # 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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str2", "I_int":20, JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str2", "I_int":20, "S_":" 1 # checking that in absence of properties, additionalProperties and unevaluatedPropoerties # 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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 1 # checking that additionalProperties are evaluated in absence of patternProperties and prop # 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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[ JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[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_" JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc" 0 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[ JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3] 1 # Checking that in absence of properties, patternProperties and additionalProperties, vali SET @object_schema= '{ "type":"object", "unevaluatedProperties":{"type":"string"} }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"st JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S 1 SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[ JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3] 0 SET @object_schema= '{ "type":"object", "unevaluatedProperties": false }'; SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"st JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S 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":"st JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S 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-30977: Additional key values are not validating properly when using # unevaluatedProperties with properties declared in subschemas # SET @unevaluatedProperties_schema= '{ "allOf": [ { "type": "object", "properties": { "name": { "type": "string" } } } ], "properties": { "type": { "enum": ["residential", "business"] } }, "required": ["type"], "unevaluatedProperties": false }'; SELECT JSON_SCHEMA_VALID(@unevaluatedProperties_schema, '{"name": "joe", "type": "bus JSON_SCHEMA_VALID(@unevaluatedProperties_schema, '{"name": "joe", "type": "business" 0 # # 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 # # MDEV-30690: Server crashed on function JSON_SCHEMA_VALID with incorrect input json schema # SET @schema = '{""}'; SELECT JSON_SCHEMA_VALID(@schema, '1'); JSON_SCHEMA_VALID(@schema, '1') NULL Warnings: Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_schema_valid' SET @schema = '{ "type": "string", "format" }'; SELECT JSON_SCHEMA_VALID(@schema, '1'); JSON_SCHEMA_VALID(@schema, '1') NULL Warnings: Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_schema_valid' 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}}') NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_schema_valid' at positi # # MDEV-30703: JSON_SCHEMA_VALID : Enum array must have at least one value # SET @schema = '{ "type":"array", "enum": [] }'; SELECT JSON_SCHEMA_VALID(@schema, '2'); ERROR HY000: Invalid value for keyword enum SET @schema = '{ "type":"number", "enum": [2, 2] }'; SELECT JSON_SCHEMA_VALID(@schema, '2'); ERROR HY000: Invalid value for keyword enum # # MDEV-30704: JSON_SCHEMA_VALID: multipleOf must be greater than zero # SET @schema = '{ "multipleOf": 0 }'; SELECT JSON_SCHEMA_VALID(@schema, '2'); ERROR HY000: Invalid value for keyword multipleOf SET @schema= '{ "maxLength" : -3}'; SELECT JSON_SCHEMA_VALID(@schema, '2'); ERROR HY000: Invalid value for keyword maxLength SET @schema= '{ "minLength" : -3}'; SELECT JSON_SCHEMA_VALID(@schema, '2'); ERROR HY000: Invalid value for keyword minLength SET @schema= '{ "maxProperties" : -3}'; SELECT JSON_SCHEMA_VALID(@schema, '2'); ERROR HY000: Invalid value for keyword maxProperties SET @schema= '{ "minProperties" : -3}'; SELECT JSON_SCHEMA_VALID(@schema, '2'); ERROR HY000: Invalid value for keyword minProperties SET @schema= '{ "maxItems" : -3}'; SELECT JSON_SCHEMA_VALID(@schema, '2'); ERROR HY000: Invalid value for keyword maxItems SET @schema= '{ "minItems" : -3}'; SELECT JSON_SCHEMA_VALID(@schema, '2'); ERROR HY000: Invalid value for keyword maxLength SET @schema= '{ "items" : ["str1"]}'; SELECT JSON_SCHEMA_VALID(@schema, '[]'); ERROR HY000: Invalid value for keyword items # # MDEV-30705: JSON_SCHEMA_VALID: schema with multipleOf for big value always return 1 # SET @schema = '{ "multipleOf": 2 }'; SELECT JSON_SCHEMA_VALID(@schema, '9007900000000001'); JSON_SCHEMA_VALID(@schema, '9007900000000001') 0 SELECT JSON_SCHEMA_VALID(@schema, '9007900000000060'); JSON_SCHEMA_VALID(@schema, '9007900000000060') 1 SELECT JSON_SCHEMA_VALID(@schema, '9007900000000061'); JSON_SCHEMA_VALID(@schema, '9007900000000061') 0 # # MDEV-31032: UBSAN|downcast of address X which does not point to an object of type # Item_string' in sql/json_schema.cc # SET @old_sql_mode= @@sql_mode; SET @schema='{ "type":"object","patternProperties": { "^I_": {"type":"number"},"^S_" : SET SESSION sql_mode='empty_string_is_null'; SELECT JSON_SCHEMA_VALID (@schema,'{"key1":"val0","key2":0,"I_int":0,"S_":"abc","p JSON_SCHEMA_VALID (@schema,'{"key1":"val0","key2":0,"I_int":0,"S_":"abc","prop0": 1 SET @@sql_mode= @old_sql_mode; SET @property_names='{ "PropertyNames":{ "pattern": "^I_" } }'; SET GLOBAL sql_mode=17179869183; SET @@sql_mode=DEFAULT; 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 SET @@sql_mode= @old_sql_mode; set global sql_mode=default; # # MDEV-30287: JSON_SCHEMA_VALID returns incorrect result for type=number # SET @schema= '{"type":"number"}'; SELECT JSON_SCHEMA_VALID(@schema, '3.14'); JSON_SCHEMA_VALID(@schema, '3.14') 1 SELECT JSON_SCHEMA_VALID(@schema, '0zzzz'); JSON_SCHEMA_VALID(@schema, '0zzzz') 0 Warnings: Warning 4038 Syntax error in JSON text in argument 2 to function 'json_schema_valid' at positi SELECT JSON_SCHEMA_VALID(@schema, '-#'); JSON_SCHEMA_VALID(@schema, '-#') 0 Warnings: Warning 4038 Syntax error in JSON text in argument 2 to function 'json_schema_valid' at positi # # MDEV-30689: JSON_SCHEMA_VALID for type=array return 1 for any string that starts with '[' # SET @schema_array= '{"type":"array"}'; SELECT JSON_SCHEMA_VALID(@schema_array, '['); JSON_SCHEMA_VALID(@schema_array, '[') 0 Warnings: Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_schema_valid' SELECT JSON_SCHEMA_VALID(repeat('[', 100), json_object()); JSON_SCHEMA_VALID(repeat('[', 100), json_object()) NULL Warnings: Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_schema_valid' SELECT JSON_SCHEMA_VALID(json_object(), repeat('[', 100)); JSON_SCHEMA_VALID(json_object(), repeat('[', 100)) 0 Warnings: Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_schema_valid' # # MDEV-30677: Incorrect result for "SELECT JSON_SCHEMA_VALID('{}', NULL)" # SELECT JSON_SCHEMA_VALID('{}', NULL); JSON_SCHEMA_VALID('{}', NULL) NULL SELECT JSON_SCHEMA_VALID(NULL, '{}'); JSON_SCHEMA_VALID(NULL, '{}') NULL SELECT JSON_SCHEMA_VALID(NULL, NULL); JSON_SCHEMA_VALID(NULL, NULL) NULL # # MDEV-31599: Assertion `0' failed in Item_param::can_return_value from Item::val_json, # UBSAN: member access within null pointer of type 'struct String' in # sql/item_jsonfunc.cc # PREPARE s FROM 'SELECT JSON_SCHEMA_VALID (?,''{}'') FROM DUAL'; ERROR HY000: Variable schema is not supported. # # MDEV-33015: Server crashes upon JSON_SCHEMA_VALID reading NULL from a user variable # SET @a= NULL; SELECT JSON_SCHEMA_VALID(@a,'{}'); JSON_SCHEMA_VALID(@a,'{}') NULL # End of 11.1 test # # MDEV-30145: JSON_TABLE: allow to retrieve the key when iterating on JSON objects # # Checking json table with NULL and empty json doc SELECT jt.* FROM JSON_TABLE( NULL, '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id # With Empty and NULL SELECT JSON_KEY_VALUE(NULL, '$.a'); JSON_KEY_VALUE(NULL, '$.a') NULL SELECT JSON_KEY_VALUE('', '$.a'); JSON_KEY_VALUE('', '$.a') NULL Warnings: Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_key_value' SELECT JSON_KEY_VALUE('[1,2,3]', ''); JSON_KEY_VALUE('[1,2,3]', '') NULL Warnings: Warning 4041 Unexpected end of JSON path in argument 2 to function 'json_key_value' SELECT JSON_KEY_VALUE('[1,2,3]', NULL); JSON_KEY_VALUE('[1,2,3]', NULL) NULL # With scalars SELECT JSON_KEY_VALUE('2', '$'); JSON_KEY_VALUE('2', '$') NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('2', '$'), '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id SELECT JSON_KEY_VALUE('"some_string"', '$'); JSON_KEY_VALUE('"some_string"', '$') NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('"some_string"', '$'), '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id SELECT JSON_KEY_VALUE('"some_string"', '$.a'); JSON_KEY_VALUE('"some_string"', '$.a') NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('"some_string"', '$.a'), '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id SELECT JSON_KEY_VALUE('"some_string"', '$[0]'); JSON_KEY_VALUE('"some_string"', '$[0]') NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('"some_string"', '$[0]'), '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id SELECT JSON_KEY_VALUE('false', '$[0]'); JSON_KEY_VALUE('false', '$[0]') NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('false', '$[0]'), '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id # With non-scalar # With array SELECT JSON_KEY_VALUE('[]', '[0]'); JSON_KEY_VALUE('[]', '[0]') NULL Warnings: Warning 4042 Syntax error in JSON path in argument 2 to function 'json_key_value' at position 1 SELECT JSON_KEY_VALUE('[1, 2, 3]', '$[0]'); JSON_KEY_VALUE('[1, 2, 3]', '$[0]') NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('[1, 2, 3]', '$[0]'), '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id SELECT JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0]'); JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0]') NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0]'), '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id SELECT JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0].a'); JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0].a') NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0].a'), '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id SELECT JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0][1]'); JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0][1]') NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('[[1, 2, 3], 2, 3]', '$[0][1]'), '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id SELECT JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]') as exp; exp [{"key": "key1", "value": "val1"}, {"key": "key2", "value": "val2"}] SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]'), '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id key1 val1 1 key2 val2 2 SELECT JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1].key1') as exp; exp NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1].key1'), '$[*] COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id SELECT JSON_KEY_VALUE('[[1, [{"key1":"val1", "key2":"val2"}], 3], 2, 3]', '$[0][1]') as exp; exp NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('[[1, [{"key1":"val1", "key2":"val2"}], 3], 2, 3]', '$[0][1]'), '$[*]' COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id SELECT JSON_KEY_VALUE('[[1, [{"key1":"val1", "key2":"val2"}], 3], 2, 3]', '$[0][1][0]') as exp; exp [{"key": "key1", "value": "val1"}, {"key": "key2", "value": "val2"}] SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('[[1, [{"key1":"val1", "key2":"val2"}], 3], 2, 3]', '$[0][1][0]'), '$[*] COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id key1 val1 1 key2 val2 2 # With object SELECT JSON_KEY_VALUE('{}', '$.key1'); JSON_KEY_VALUE('{}', '$.key1') NULL SELECT JSON_KEY_VALUE('{"key1":"val1", "key2":"val2"}', '$') as exp; exp [{"key": "key1", "value": "val1"}, {"key": "key2", "value": "val2"}] SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('{"key1":"val1", "key2":"val2"}', '$'), '$[*]' COLUMNS (k VARCHAR(11) PATH '$.key', v VARCHAR(5) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id key1 val1 1 key2 val2 2 SELECT JSON_KEY_VALUE('{"key1":"val1", "key2":"val2"}', '$.key1') as exp; exp NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('{"key1":"val1", "key2":"val2"}', '$.key1'), '$[*]' COLUMNS (k VARCHAR(11) PATH '$.key', v VARCHAR(5) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id SELECT JSON_KEY_VALUE('{"key1":{"a":1, "b":2}, "key2":"val2"}', '$.key1') as exp; exp [{"key": "a", "value": 1}, {"key": "b", "value": 2}] SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('{"key1":{"a":1, "b":2}, "key2":"val2"}', '$.key1'), '$[*]' COLUMNS (k VARCHAR(11) PATH '$.key', v VARCHAR(5) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id a 1 1 b 2 2 SELECT JSON_KEY_VALUE('{"key1":{"a":1, "b": [1,2,3, {"some_key":"some_val", "c":3}]}, exp [{"key": "some_key", "value": "some_val"}, {"key": "c", "value": 3}] SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('{"key1":{"a":1, "b": [1,2,3, {"some_key":"some_val", "c":3}]}, "key2" COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id some_key some_val 1 c 3 2 SELECT JSON_KEY_VALUE('{"key1":{"a":1, "b": [1,2,3, {"some_key":"some_val", "c":3}]}, exp NULL SELECT jt.* FROM JSON_TABLE( JSON_KEY_VALUE('{"key1":{"a":1, "b": [1,2,3, {"some_key":"some_val", "c":3}]}, "key2" COLUMNS (k VARCHAR(20) PATH '$.key', v VARCHAR(20) PATH '$.value', id FOR ORDINALITY)) AS jt; k v id # End of 11.2 test # # MDEV-26182: Implement json_array_intersect() # # JSON_ARRAY_INTERSECT() # Scalar as elements SET @json1= '[1,2,3]'; SET @json2= '[1,2,3]'; SELECT json_array_intersect(@json1, @json2); json_array_intersect(@json1, @json2) [1, 2, 3] SET @json1= '[1,2,3]'; SET @json2= '[1,2,4]'; SELECT json_array_intersect(@json1, @json2); json_array_intersect(@json1, @json2) [1, 2] SET @json1= '["abc","def","ghi"]'; SET @json2= '["xyz", "abc", "tuv"]'; SELECT json_array_intersect(@json1, @json2); json_array_intersect(@json1, @json2) ["abc"] SET @obj1= '[true]'; SET @obj2= '[false, "true"]'; select json_array_intersect(@obj1, @obj2); json_array_intersect(@obj1, @obj2) NULL SET @obj1= '[true]'; SET @obj2= '[false, true]'; select json_array_intersect(@obj1, @obj2); json_array_intersect(@obj1, @obj2) [true] SET @obj1= '[null, true]'; SET @obj2= '[false, null]'; select json_array_intersect(@obj1, @obj2); json_array_intersect(@obj1, @obj2) [null] # array as elements SET @json1= '[6,6,6]'; SET @json2= '[[1,2,3],[4,5,6],[1,3,2]]'; SELECT json_array_intersect(@json1, @json2); json_array_intersect(@json1, @json2) NULL SET @json1= '[[1,2,3],[4,5,6],[1,3,2]]'; SET @json2= '[[1,2,3],[4,5,6],[1,3,2]]'; SELECT json_array_intersect(@json1, @json2); json_array_intersect(@json1, @json2) [[1, 2, 3], [4, 5, 6], [1, 3, 2]] SET @json1= '[[1,2,3],[4,5,6],[1,3,2]]'; SET @json2= '[[1,2,3],[4,5,6],[1,4,2]]'; SELECT json_array_intersect(@json1, @json2); json_array_intersect(@json1, @json2) [[1, 2, 3], [4, 5, 6]] # object as elements SET @json1 = '[{"k1":"v1","k2":"v2"},{"k2":"v2"}]'; SET @json2 = '[{"kkey1":"vvalue1"},{"k2":"v2","k1":"v1"}]'; SELECT json_array_intersect(@json1, @json2); json_array_intersect(@json1, @json2) [{"k2": "v2", "k1": "v1"}] SET @json1 = '[{"k1":"v1","k2":"v2"},{"k2":"v2","k1":"v1"}]'; SET @json2 = '[{"k1":"v1","k2":"v2"},{"k1":"v1","k2":"v2"}]'; SELECT json_array_intersect(@json1, @json2); json_array_intersect(@json1, @json2) [{"k1": "v1", "k2": "v2"}, {"k1": "v1", "k2": "v2"}] # multi type elements SET @obj1= '[1,2,3, "abc", "xyz", {"key1":"val1"}, {"key2":"val2"}, [1,2,3]]'; SET @obj2= '[3.0, 4, 5, "abc", {"key1":"val1"}, [3,2,1]]'; select json_array_intersect(@obj1, @obj2); json_array_intersect(@obj1, @obj2) [3.0, "abc", {"key1": "val1"}] SET @obj1= '[1, 2, 3, "abc", "xyz", {"key1": {"key2" : [1,2,3] } }, [4,5,6] ]'; SET @obj2= '[3.0, 4, 5, "abc", {"key1": {"key2" : [3,2,1]} }, {"key1": {"key2" : [1,2,3] } }, [4, select json_array_intersect(@obj1, @obj2); json_array_intersect(@obj1, @obj2) [3.0, "abc", {"key1": {"key2": [1, 2, 3]}}, [4, 5, 6]] # Checking duplicates SET @obj1= '[1, 2, 3, 3, 3.0, "abc", true, true, {"key1":"val1"}]'; SET @obj2= '[3.0, 3, 5, "abc", "abc", true, {"key2":"val2"}, {"key1":"val1"}, {"key1":"val select json_array_intersect(@obj1, @obj2); json_array_intersect(@obj1, @obj2) [3.0, 3, "abc", true, {"key1": "val1"}] # Checking Syntax error for JSON_ARRAY_INTERSECT() SET @obj1= '[1, 2, 3, 3, 3.0, "abc", true, true, {"key1":"val1" ]'; SET @obj2= '[3.0, 3, 5, "abc", "abc", true, {"key2":"val2"}, {"key1":"val1"}, {"key1":"val select json_array_intersect(@obj1, @obj2); json_array_intersect(@obj1, @obj2) [3.0, 3, "abc", true] Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_array_intersect' at pos # Checking incorrect type for input SET @obj1= '{"key1": "val1"}'; SET @arr1= '[ 1, 2, 3 ]'; SET @num1= '2'; SET @str1= '"abc"'; SET @bool1= 'true'; select json_array_intersect(@obj1, @arr1); json_array_intersect(@obj1, @arr1) NULL select json_array_intersect(@arr1, @obj1); json_array_intersect(@arr1, @obj1) NULL Warnings: Warning 4038 Syntax error in JSON text in argument 2 to function 'json_array_intersect' at pos select json_array_intersect(@arr1, @num1); json_array_intersect(@arr1, @num1) NULL Warnings: Warning 4038 Syntax error in JSON text in argument 2 to function 'json_array_intersect' at pos select json_array_intersect(@num1, @bool1); json_array_intersect(@num1, @bool1) NULL # JSON_OBJECT_FILTER_KEYS() SET @obj1= '{ "a": 1, "b": 2, "c": 3}'; SET @obj2= '{"b" : 10, "c": 20, "d": 30}'; SELECT JSON_OBJECT_FILTER_KEYS (@obj1, json_array_intersect(json_keys(@obj1), json_k JSON_OBJECT_FILTER_KEYS (@obj1, json_array_intersect(json_keys(@obj1), json_keys(@o {"b": 2, "c": 3} SET @obj1= '{ "a": 1, "b": {"key1": {"key2":"val2"}}, "c": [1, 2, 3] }'; SET @obj2= '{"b" : 10, "c": 20, "d": 30}'; SELECT JSON_OBJECT_FILTER_KEYS (@obj1, json_array_intersect(json_keys(@obj1), json_k JSON_OBJECT_FILTER_KEYS (@obj1, json_array_intersect(json_keys(@obj1), json_keys(@o {"b": {"key1": {"key2": "val2"}}, "c": [1, 2, 3]} SET @obj1= '{ "a": 1, "b": {"key1": {"key2":"val2"}}, "c": [1, 2, 3] }'; SET @arr2='["x", "y", "z"]'; SELECT JSON_OBJECT_FILTER_KEYS(@obj1, @arr2); JSON_OBJECT_FILTER_KEYS(@obj1, @arr2) NULL SET @obj1= '{ "a": 1, "b": {"key1": {"key2":"val2"}}, "c": [1, 2, 3] }'; SET @arr2='["key2", "key1", "b"]'; SELECT JSON_OBJECT_FILTER_KEYS(@obj1, @arr1); JSON_OBJECT_FILTER_KEYS(@obj1, @arr1) NULL # Incorrect type in input returns NULL SELECT JSON_OBJECT_FILTER_KEYS(@obj1, @obj1); JSON_OBJECT_FILTER_KEYS(@obj1, @obj1) NULL SELECT JSON_OBJECT_FILTER_KEYS(@arr1, @arr1); JSON_OBJECT_FILTER_KEYS(@arr1, @arr1) NULL SET @obj1= '{ "a": 1, "b": {"key1": {"key2":"val2"}}, "c": [1, 2, 3] }'; SET @scalar1='2'; SELECT JSON_OBJECT_FILTER_KEYS(@obj1, @scalar1); JSON_OBJECT_FILTER_KEYS(@obj1, @scalar1) NULL # Checking syntax error SET @obj1= '{ "a": 1, "b": 2, "c": 3}'; SET @obj2= '{"b" : 10, "c": 20, "d" 30}'; SELECT JSON_OBJECT_FILTER_KEYS (@obj1, json_array_intersect(json_keys(@obj1), json_k JSON_OBJECT_FILTER_KEYS (@obj1, json_array_intersect(json_keys(@obj1), json_keys(@o NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_keys' at position 25 SET @obj1= '{ "a": 1, "b": {"key1": {"key2":"val2"}}, "c": [1, 2, 3] }'; SET @arr2= '[ "key2", "key1", "b" '; SELECT JSON_OBJECT_FILTER_KEYS(@obj1, @arr1); JSON_OBJECT_FILTER_KEYS(@obj1, @arr1) NULL # JSON_OBJECT_TO_ARRAY() SET @obj1= '{ "a": [1, 2, 3], "b": { "key1":"val1", "key2": {"key3":"val3"} }, "c": 3, "d" : 1, "e SELECT JSON_OBJECT_TO_ARRAY(@obj1); JSON_OBJECT_TO_ARRAY(@obj1) [["a", [1, 2, 3]], ["b", {"key1": "val1", "key2": {"key3": "val3"}}], ["c", 3], ["d", 1], ["e" SET @obj1= '{ "a": [1, 2, 3], "b": { "key1":"val1", "key2": [1, 2, 3] }, "c": 3, "d" : 1, "e": "xyz", SELECT JSON_OBJECT_TO_ARRAY(@obj1); JSON_OBJECT_TO_ARRAY(@obj1) [["a", [1, 2, 3]], ["b", {"key1": "val1", "key2": [1, 2, 3]}], ["c", 3], ["d", 1], ["e", "xyz"], # Checking syntax error SET @obj1= '{ "a": [1, 2, 3], "b": "key1": "val1", "key2": {"key3":"val3"} }, "c": 3, "d" : 1, "e" SELECT JSON_OBJECT_TO_ARRAY(@obj1); JSON_OBJECT_TO_ARRAY(@obj1) NULL Warnings: Warning 4038 Syntax error in JSON text in argument 1 to function 'json_object_to_array' at pos Checking incorrect type in argument SET @arr1= '[1, 2, 3]'; SELECT JSON_OBJECT_TO_ARRAY(@arr1); JSON_OBJECT_TO_ARRAY(@arr1) NULL # # MDEV-31411: JSON_ARRAY_INTERSECT/JSON_OBJECT_FILTER_KEYS should fetch # data from a table similar to other JSON functions # CREATE TABLE t1 ( c1 longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(c1)), c2 longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; INSERT INTO t1 VALUES('[1,2,3]', '[2, 3, 4]'), ('[2 ,3, 4]', '[4, 5, 6]'); SELECT JSON_ARRAY_INTERSECT(c1, c2) FROM t1; JSON_ARRAY_INTERSECT(c1, c2) [2, 3] [4] DROP TABLE t1; # # MDEV-31543: ASAN heap-buffer-overflow in strncpy when fetching keys using JSON_OBJECT_FI # SET @arr1='[1,2,"c"]'; SET character_set_database=ucs2; SET CHARACTER SET utf8; SET @obj1='{ "a": 1,"b": 2,"c": 3}'; SELECT JSON_OBJECT_FILTER_KEYS (@obj1,@arr1); JSON_OBJECT_FILTER_KEYS (@obj1,@arr1) NULL SET character_set_database=default; SET CHARACTER SET default; # # MDEV-33149: JSON_ARRAY_INTERSECT function crashes the server when # called with empty json arrays, UBSAN runtime error: member access # within null pointer of type 'struct String' in # Item_func_json_array_intersect::prepare_json_and_create_hash # SELECT json_array_intersect(@a,@b); json_array_intersect(@a,@b) NULL # # MDEV-36809: json_array_intersect crashs when unused table ref provided # select json_array_intersect('[["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]]', '[["2","6 result [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] SELECT ( WITH x AS ( WITH x ( x ) AS ( SELECT ( 1.000000 ) ) SELECT x FROM x ) SELECT * FROM x WHERE ( SELECT result 1.000000 # # MDEV-37864: mysql-test/mtr --cursor main.func_json fails # select json_array_intersect('[["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]]', '[["2","6 result [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] # # MDEV-33640: Server crashes at my_hash_free # SELECT JSON_SCHEMA_VALID('{"properties": "a_string": {"pattern": "^[5-9]$"}}}', '{"a_ ERROR HY000: Invalid value for keyword properties # # MDEV-38356 JSON_SCHEMA_VALID() crashes with a long enum # select json_schema_valid('{"enum":[0]}', sformat('"{:#>200}"','0')); json_schema_valid('{"enum":[0]}', sformat('"{:#>200}"','0')) 0 # # MDEV-36808 json_array_intersect incorrect results after returning NULL in table scan # CREATE TABLE t1 (full text, overlap text); INSERT INTO t1 VALUES ('["2"]', '["2"]'), ('["2"]', '["0"]'), ('["2"]', '["2"]'); SELECT full, overlap, json_array_intersect(full, overlap) as jai from t1; full overlap jai ["2"] ["2"] ["2"] ["2"] ["0"] NULL ["2"] ["2"] ["2"] DROP TABLE t1; # End of 11.4 Test # # MDEV-38033: JSON_SCHEMA_VALID function returning wrong result # SET @schema= ' { "type": "object", "properties": { "id": { "type": "string", "minLength": 1 }, "arr_fields": { "type": "array", "items": { "type": "object", "properties": { "field1": { "type": "string" }, "field2": { "type": "number" } }, "required": ["field1"] } } } }'; SET @value= ' { "id": "zero", "arr_fields": [ { "field1": "A", "field2": 123 }, { "field1": "B" } ] }'; SELECT JSON_SCHEMA_VALID(@schema, @value); JSON_SCHEMA_VALID(@schema, @value) 1 # # MDEV-38702 Behaviour of IF() with boolean JSON_EXTRACT as expression is wrong # SELECT JSON_TYPE(JSON_EXTRACT(x.val, '$.booleanValue')) AS json_type, JSON_EXTRACT(x.val, '$.booleanValue') AS value, IF(JSON_EXTRACT(x.val, '$.booleanValue'), 1, 0) AS value_if, JSON_EXTRACT(x.val, '$.booleanValue') = true AS value_eq FROM (SELECT '{"booleanValue": true}' AS val UNION ALL SELECT '{"booleanValue": false}' AS v json_type value value_if value_eq BOOLEAN true 1 1 BOOLEAN false 0 0 # # MDEV-39135 JSON_OBJECTAGG(NULL) in decimal context # create table t0(c0 boolean); insert t0 values (false); select 1 ^ json_objectagg(NULL, 'a') from t0; 1 ^ json_objectagg(NULL, 'a') NULL drop table t0; # End of 11.8 Test # Beginning of 12.3 Test # # MDEV-37072: Implement IS JSON predicate # set names utf8mb4; SELECT '[1, 2]' IS JSON; '[1, 2]' IS JSON 1 SELECT '{"key1":1, "key2":[2,3]}' IS JSON; '{"key1":1, "key2":[2,3]}' IS JSON 1 SELECT '123' IS JSON; '123' IS JSON 1 SELECT 'null' IS JSON; 'null' IS JSON 1 SELECT 'invalid' IS JSON; 'invalid' IS JSON 0 SELECT '{"key1":1, "key2":[2,3]' IS JSON; '{"key1":1, "key2":[2,3]' IS JSON 0 SELECT '[1, 2' IS JSON; '[1, 2' IS JSON 0 SELECT 'NULL' IS JSON; 'NULL' IS JSON 0 SELECT 'invalid' IS NOT JSON; 'invalid' IS NOT JSON 1 SELECT '{"key1":1, "key2":[2,3]' IS NOT JSON; '{"key1":1, "key2":[2,3]' IS NOT JSON 1 SELECT '[1, 2]' IS NOT JSON; '[1, 2]' IS NOT JSON 0 SELECT '{"key1":1, "key2":[2,3]}' IS NOT JSON; '{"key1":1, "key2":[2,3]}' IS NOT JSON 0 # Type constraints SELECT js, js IS JSON "json?", js IS JSON VALUE "value?", js IS JSON SCALAR "scalar?", js IS JSON OBJECT "object?", js IS JSON ARRAY "array?" FROM (VALUES ('123'), ('"string"'), ('{"key":1}'), ('[1,2,3]'), ('[]'), ('{}'), ('true'), ('false'), ('null')) foo(js); js json? value? scalar? object? array? 123 1 1 1 0 0 "string" 1 1 1 0 0 {"key":1} 1 1 0 1 0 [1,2,3] 1 1 0 0 1 [] 1 1 0 0 1 {} 1 1 0 1 0 true 1 1 1 0 0 false 1 1 1 0 0 null 1 1 1 0 0 # UNIQUE KEYS constraint SELECT '"a",1,1,2]' IS JSON WITH UNIQUE KEYS; '"a",1,1,2]' IS JSON WITH UNIQUE KEYS 0 SELECT '{"a": 42,"b": [{"c":1, "c":2}]}' IS JSON WITH UNIQUE KEYS; '{"a": 42,"b": [{"c":1, "c":2}]}' IS JSON WITH UNIQUE KEYS 0 SELECT '{"a": 42,"b": [{"a": {"a": [{"a" : "a", "a" : "a" }]} }]}' IS JSON WITH UNIQUE KEYS; '{"a": 42,"b": [{"a": {"a": [{"a" : "a", "a" : "a" }]} }]}' IS JSON WITH UNIQUE KEYS 0 SELECT '{"a": 42,"b": [{"a": {"a": [{"a" : "a"}]} }]}' IS JSON WITH UNIQUE KEYS; '{"a": 42,"b": [{"a": {"a": [{"a" : "a"}]} }]}' IS JSON WITH UNIQUE KEYS 1 SELECT '[{"a":1}, {"a":1}]' IS JSON WITH UNIQUE KEYS; '[{"a":1}, {"a":1}]' IS JSON WITH UNIQUE KEYS 1 SELECT '[{"a":1, "a":2}, {"b":1}]' IS JSON WITH UNIQUE KEYS; '[{"a":1, "a":2}, {"b":1}]' IS JSON WITH UNIQUE KEYS 0 SELECT '{"a": {"b":1, "b":2}}' IS JSON WITH UNIQUE KEYS; '{"a": {"b":1, "b":2}}' IS JSON WITH UNIQUE KEYS 0 SELECT '[{"a":1}, [{"b":1, "b":2}]]' IS JSON WITH UNIQUE KEYS; '[{"a":1}, [{"b":1, "b":2}]]' IS JSON WITH UNIQUE KEYS 0 SELECT '{"a": [{"b": {"c":1, "c":2}}]}' IS JSON WITH UNIQUE KEYS; '{"a": [{"b": {"c":1, "c":2}}]}' IS JSON WITH UNIQUE KEYS 0 SELECT '[{}]' IS JSON WITH UNIQUE KEYS; '[{}]' IS JSON WITH UNIQUE KEYS 1 SELECT '[1, {"a":1}, 2, {"b":2}]' IS JSON WITH UNIQUE KEYS; '[1, {"a":1}, 2, {"b":2}]' IS JSON WITH UNIQUE KEYS 1 SELECT '[{"a":1}, 1, {"a":1, "a":2}, 2]' IS JSON WITH UNIQUE KEYS; '[{"a":1}, 1, {"a":1, "a":2}, 2]' IS JSON WITH UNIQUE KEYS 0 SELECT '[{"a":1}, [{"b":1}], {"c":1}]' IS JSON WITH UNIQUE KEYS; '[{"a":1}, [{"b":1}], {"c":1}]' IS JSON WITH UNIQUE KEYS 1 SELECT '[{"a":1}, [{"b":1, "b":2}], {"c":1}]' IS JSON WITH UNIQUE KEYS; '[{"a":1}, [{"b":1, "b":2}], {"c":1}]' IS JSON WITH UNIQUE KEYS 0 SELECT '[[[[{"a":1, "a":2}]]]]' IS JSON WITH UNIQUE KEYS; '[[[[{"a":1, "a":2}]]]]' IS JSON WITH UNIQUE KEYS 0 SELECT '[[[{"a":1}]], [[{"b":1}]]]' IS JSON WITH UNIQUE KEYS; '[[[{"a":1}]], [[{"b":1}]]]' IS JSON WITH UNIQUE KEYS 1 SELECT '[[[{"a":1, "a":2}]], [[{"b":1}]]]' IS JSON WITH UNIQUE KEYS; '[[[{"a":1, "a":2}]], [[{"b":1}]]]' IS JSON WITH UNIQUE KEYS 0 SELECT '[{"a":[{"b":1, "b":2}]}, {"c":[{"d":1}]}]' IS JSON WITH UNIQUE KEYS; '[{"a":[{"b":1, "b":2}]}, {"c":[{"d":1}]}]' IS JSON WITH UNIQUE KEYS 0 SELECT '{"a":{"b":1, "c":2}, "d":{"b":1, "c":2}}' IS JSON WITH UNIQUE KEYS; '{"a":{"b":1, "c":2}, "d":{"b":1, "c":2}}' IS JSON WITH UNIQUE KEYS 1 SELECT '[{"a":[{"b":[{"c":1}]}]}, {"d":[{"e":[{"f":1, "f":2}]}]}]' IS JSON WITH UNIQUE K '[{"a":[{"b":[{"c":1}]}]}, {"d":[{"e":[{"f":1, "f":2}]}]}]' IS JSON WITH UNIQUE KEYS 0 SELECT '[{}, {"a":{}}, [{}]]' IS JSON WITH UNIQUE KEYS; '[{}, {"a":{}}, [{}]]' IS JSON WITH UNIQUE KEYS 1 SELECT '{"a":{"b":{"b":1}}, "d":{"e":{"f":1}}}' IS JSON WITH UNIQUE KEYS; '{"a":{"b":{"b":1}}, "d":{"e":{"f":1}}}' IS JSON WITH UNIQUE KEYS 1 SELECT js, js IS JSON OBJECT WITH UNIQUE KEYS "object_with_unique", js IS JSON OBJECT WITHOUT UNIQUE KEYS "object_without_unique", js IS JSON ARRAY WITH UNIQUE KEYS "array_with_unique", js IS JSON ARRAY WITHOUT UNIQUE KEYS "array_without_unique" FROM (VALUES ('{"a":1, "b":2, "c":3}'), ('{"a":1, "a":2, "a":3}'), ('[{"a":"1"},{"c":"2","c":"3"}]'), ('[{"a":"1"},{"b":"2","c":"3"}]'), ('[1,2,3]'), ('[1,1,1]'), ('[]'), ('123'), ('"string"'), ('true'), ('null')) foo(js); js object_with_unique object_without_unique array_with_unique array_without_unique {"a":1, "b":2, "c":3} 1 1 0 0 {"a":1, "a":2, "a":3} 0 1 0 0 [{"a":"1"},{"c":"2","c":"3"}] 0 0 0 1 [{"a":"1"},{"b":"2","c":"3"}] 0 0 1 1 [1,2,3] 0 0 1 1 [1,1,1] 0 0 1 1 [] 0 0 1 1 123 0 0 0 0 "string" 0 0 0 0 true 0 0 0 0 null 0 0 0 0 # Test with table data CREATE TABLE test_json ( json_col JSON, text_col TEXT ); INSERT INTO test_json VALUES ('{"name":"Alice", "age":25}', '{"name":"Alice", "age":25}'), ('[1,2,3]', '[1,2,3]'), ('42', '42'), ('"hello"', '"hello"'), ('true', 'true'), ('null', 'null'), ('"invalid"', 'invalid'), ('{"key1":1, "key2":2}', '{"key1":1, "key2":2}'), ('{"key1":1, "key1":2}', '{"key1":1, "key1":2}'); SELECT json_col, json_col IS JSON as is_json, json_col IS JSON VALUE as is_value, json_col IS JSON SCALAR as is_scalar, json_col IS JSON OBJECT as is_object, json_col IS JSON ARRAY as is_array, json_col IS JSON OBJECT WITH UNIQUE KEYS as object_with_unique, json_col IS JSON OBJECT WITHOUT UNIQUE KEYS as object_without_unique FROM test_json; json_col is_json is_value is_scalar is_object is_array object_with_unique object_withou {"name":"Alice", "age":25} 1 1 0 1 0 1 1 [1,2,3] 1 1 0 0 1 0 0 42 1 1 1 0 0 0 0 "hello" 1 1 1 0 0 0 0 true 1 1 1 0 0 0 0 null 1 1 1 0 0 0 0 "invalid" 1 1 1 0 0 0 0 {"key1":1, "key2":2} 1 1 0 1 0 1 1 {"key1":1, "key1":2} 1 1 0 1 0 0 1 SELECT text_col, text_col IS JSON as is_json, text_col IS JSON VALUE as is_value, text_col IS JSON SCALAR as is_scalar, text_col IS JSON OBJECT as is_object, text_col IS JSON ARRAY as is_array FROM test_json; text_col is_json is_value is_scalar is_object is_array {"name":"Alice", "age":25} 1 1 0 1 0 [1,2,3] 1 1 0 0 1 42 1 1 1 0 0 "hello" 1 1 1 0 0 true 1 1 1 0 0 null 1 1 1 0 0 invalid 0 0 0 0 0 {"key1":1, "key2":2} 1 1 0 1 0 {"key1":1, "key1":2} 1 1 0 1 0 SELECT COUNT(*) as total_rows, SUM(json_col IS JSON) as valid_json_count, SUM(json_col IS JSON OBJECT) as object_count, SUM(json_col IS JSON ARRAY) as array_count, SUM(json_col IS JSON SCALAR) as scalar_count FROM test_json; total_rows valid_json_count object_count array_count scalar_count 9 9 3 1 5 # Edge cases SELECT js, js IS JSON "json?", js IS JSON VALUE "value?", js IS JSON SCALAR "scalar?", js IS JSON OBJECT "object?", js IS JSON ARRAY "array?" FROM (VALUES (NULL), (''), (' '), ('\n'), ('\t'), ('{"key":1,}'), ('{"key":1, "key2":}'), ('{key:1}'), ('{"emoji":"��"}'), ('{"unicode":"\\u0041"}'), (x'48656C6C6F'), ('2'), (CONCAT('{"a":', 1, '}')), (JSON_OBJECT('a', 1, 'b', 2)), (JSON_ARRAY(1, 2, 3)), (JSON_QUOTE('hello'))) foo(js); js json? value? scalar? object? array? NULL NULL NULL NULL NULL NULL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 {"key":1,} 0 0 0 0 0 {"key":1, "key2":} 0 0 0 0 0 {key:1} 0 0 0 0 0 {"emoji":"��"} 1 1 0 1 0 {"unicode":"\u0041"} 1 1 0 1 0 Hello 0 0 0 0 0 2 1 1 1 0 0 {"a":1} 1 1 0 1 0 {"a": 1, "b": 2} 1 1 0 1 0 [1, 2, 3] 1 1 0 0 1 "hello" 1 1 1 0 0 # Large json object with unique keys CREATE PROCEDURE build_large_json() BEGIN SET @json='{"key1":1'; SET @i=2; WHILE @i <= 1000 DO SET @json = CONCAT(@json, ',"key', @i, '":', @i); SET @i = @i + 1; END WHILE; SET @json = CONCAT(@json, '}'); END| CALL build_large_json()| SELECT @json IS JSON OBJECT WITH UNIQUE KEYS AS unique_keys| unique_keys 1 DROP PROCEDURE build_large_json| DROP TABLE test_json; # Generated columns CREATE TABLE test_generated_json ( j TEXT, is_json BOOL GENERATED ALWAYS AS (j IS JSON) VIRTUAL, is_not_json BOOL GENERATED ALWAYS AS (j IS NOT JSON) VIRTUAL, is_array BOOL GENERATED ALWAYS AS (j IS JSON ARRAY) VIRTUAL, is_object BOOL GENERATED ALWAYS AS (j IS JSON OBJECT) VIRTUAL, is_scalar BOOL GENERATED ALWAYS AS (j IS JSON SCALAR) VIRTUAL, with_unique_keys BOOL GENERATED ALWAYS AS (j IS JSON OBJECT WITH UNIQUE KEYS) VIRTUAL, without_unique_keys BOOL GENERATED ALWAYS AS (j IS JSON OBJECT WITHOUT UNIQUE KEYS) VIRTUAL ); INSERT INTO test_generated_json (j) VALUES ('[1, 2, 3]'), ('{"key": "value"}'), ('3'), (NULL), ('{}'), ('invalid'), ('{"key": "value", "key2": "value2"}'), ('{"key": "value", "key": "value2"}'); SELECT j, is_json, is_not_json, is_array, is_object, is_scalar, with_unique_keys, without_unique_keys FROM test_generated_json; j is_json is_not_json is_array is_object is_scalar with_unique_keys without_unique_keys [1, 2, 3] 1 0 1 0 0 0 0 {"key": "value"} 1 0 0 1 0 1 1 3 1 0 0 0 1 0 0 NULL NULL NULL NULL NULL NULL NULL NULL {} 1 0 0 1 0 1 1 invalid 0 1 0 0 0 0 0 {"key": "value", "key2": "value2"} 1 0 0 1 0 1 1 {"key": "value", "key": "value2"} 1 0 0 1 0 0 1 DROP TABLE test_generated_json; # CHECK constraints CREATE TABLE test_check_json ( j TEXT CHECK (j IS JSON), j_array JSON CHECK (j_array IS JSON ARRAY), j_object JSON CHECK (j_object IS JSON OBJECT), j_object_with_unique_keys JSON CHECK (j_object_with_unique_keys IS JSON OBJECT WITH UNIQ ); INSERT INTO test_check_json VALUES ('[1, 2, 3]', '[1, 2]', '{"key": "value"}', '{"key": "value"}'), ('{"key": "value"}', '[1, 2]', '{"key": "value"}', '{"key": "value", "key2": "value2"}'); INSERT INTO test_check_json VALUES ('[1, 2, 3]', '{1, 2}', '{"key": "value"}', 'invalid'); ERROR 23000: CONSTRAINT `test_check_json.j_array` failed for `test`.`test_check_json` INSERT INTO test_check_json VALUES ('[1, 2, 3]', '[1, 2]', '{"key": "value"}', '{"key": "val ERROR 23000: CONSTRAINT `test_check_json.j_object_with_unique_keys` failed for `test`. DROP TABLE test_check_json; # DEFAULT clauses CREATE TABLE test_default_json ( j TEXT, is_json BOOL DEFAULT (j IS JSON), is_not_json BOOL DEFAULT (j IS NOT JSON), is_array BOOL DEFAULT (j IS JSON ARRAY), is_object BOOL DEFAULT (j IS JSON OBJECT), is_scalar BOOL DEFAULT (j IS JSON SCALAR), with_unique_keys BOOL DEFAULT (j IS JSON OBJECT WITH UNIQUE KEYS), without_unique_keys BOOL DEFAULT (j IS JSON OBJECT WITHOUT UNIQUE KEYS) ); INSERT INTO test_default_json (j) VALUES ('[1, 2, 3]'), ('{"key": "value"}'), ('3'), (NULL), ('{}'), ('{"key": "value", "key2": "value2"}'), ('{"key": "value", "key": "value2"}'); SELECT j, is_json, is_not_json, is_array, is_object, is_scalar, with_unique_keys, without_unique_keys FROM test_default_json; j is_json is_not_json is_array is_object is_scalar with_unique_keys without_unique_keys [1, 2, 3] 1 0 1 0 0 0 0 {"key": "value"} 1 0 0 1 0 1 1 3 1 0 0 0 1 0 0 NULL NULL NULL NULL NULL NULL NULL NULL {} 1 0 0 1 0 1 1 {"key": "value", "key2": "value2"} 1 0 0 1 0 1 1 {"key": "value", "key": "value2"} 1 0 0 1 0 0 1 DROP TABLE test_default_json; # # MDEV-38033: JSON_SCHEMA_VALID function returning wrong result # SET @schema= ' { "type": "object", "properties": { "id": { "type": "string", "minLength": 1 }, "arr_fields": { "type": "array", "items": { "type": "object", "properties": { "field1": { "type": "string" }, "field2": { "type": "number" } }, "required": ["field1"] } } } }'; SET @value= ' { "id": "zero", "arr_fields": [ { "field1": "A", "field2": 123 }, { "field1": "B" } ] }'; SELECT JSON_SCHEMA_VALID(@schema, @value); JSON_SCHEMA_VALID(@schema, @value) 1 # End of 12.3 tests
¤ Die Informationen auf dieser Webseite wurden nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit, noch Qualität der bereit gestellten Informationen zugesichert.0.728Bemerkung: (vorverarbeitet am 2026-10-08) ¤*Bot Zugriff |
|
||||||||||||||
2026-10-09