select json_valid('[1, 2]');
json_valid('[1, 2]')
1
select json_valid('"string"}');
json_valid('"string"}')
0
select json_valid('{"key1":1, "key2":[2,3]}');
json_valid('{"key1":1, "key2":[2,3]}')
1
select json_valid('[false, true, null]');
json_valid('[false, true, null]')
1
select json_valid(repeat('[', 1000));
json_valid(repeat('[', 1000))
0
select json_valid(repeat('{"a":', 1000));
json_valid(repeat('{"a":', 1000))
0
select json_value('{"key1":123}', '$.key2');
json_value('{"key1":123}', '$.key2')
NULL
select json_value('{"key1":123}', '$.key1');
json_value('{"key1":123}', '$.key1')
123
select json_value('{"key1":[1,2,3]}', '$.key1');
json_value('{"key1":[1,2,3]}', '$.key1')
NULL
select json_value('{"key1": [1,2,3], "key1":123}', '$.key1');
json_value('{"key1": [1,2,3], "key1":123}', '$.key1')
123
select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z');
JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z')
Mon"t"y
select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2');
json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2')
NULL
select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1');
json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1')
{"a":1, "b":[1,2]}
select json_query('{"key1": 1}', '$.key1');
json_query('{"key1": 1}', '$.key1')
NULL
select json_query('{"key1":123, "key1": [1,2,3]}', '$.key1');
json_query('{"key1":123, "key1": [1,2,3]}', '$.key1')
[1,2,3]
select json_query('{"key1":123, "key1": [1,2,3]}', concat('$', repeat('.k', 1000)));
json_query('{"key1":123, "key1": [1,2,3]}', concat('$', repeat('.k', 1000)))
NULL
select json_array();
json_array()
[]
select json_array(1);
json_array(1)
[1]
select json_array(1, "text", false, null);
json_array(1, "text", false, null)
[1, "text", false, null]
select json_array_append('["a", "b"]', '$', FALSE);
json_array_append('["a", "b"]', '$', FALSE)
["a", "b", false]
select json_array_append('{"k1":1, "k2":["a", "b"]}', '$.k2', 2);
json_array_append('{"k1":1, "k2":["a", "b"]}', '$.k2', 2)
{"k1": 1, "k2": ["a", "b", 2]}
select json_array_append('["a", ["b", "c"], "d"]', '$[0]', 2);
json_array_append('["a", ["b", "c"], "d"]', '$[0]', 2)
[["a", 2], ["b", "c"], "d"]
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x');
json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x')
["a", "x", {"b": [1, 2]}, [3, 4]]
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2]', 'x');
json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2]', 'x')
["a", {"b": [1, 2]}, "x", [3, 4]]
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[3]', 'x');
json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[3]', 'x')
["a", {"b": [1, 2]}, [3, 4], "x"]
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[4]', 'x');
json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[4]', 'x')
["a", {"b": [1, 2]}, [3, 4], "x"]
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x');
json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x')
["a", {"b": ["x", 1, 2]}, [3, 4]]
select json_array_insert('true', '$', 1);
json_array_insert('true', '$', 1)
NULL
select json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2][1]', 'y');
json_array_insert('["a", {"b": [1, 2]}, [3, 4]]', '$[2][1]', 'y')
["a", {"b": [1, 2]}, [3, "y", 4]]
select json_contains('{"k1":123, "k2":345}', '123', '$.k1');
json_contains('{"k1":123, "k2":345}', '123', '$.k1')
1
select json_contains('"you"', '"you"');
json_contains('"you"', '"you"')
1
select json_contains('"youth"', '"you"');
json_contains('"youth"', '"you"')
0
select json_contains('[1]', '[1]', '$', '$[0]');
ERROR 42000: Incorrect parameter count in the call to native function 'json_contains'
select json_contains('', '', '$');
json_contains('', '', '$')
NULL
Warnings:
Warning	4037	Unexpected end of JSON text in argument 1 to function 'json_contains'
select json_contains('null', 'null', '$');
json_contains('null', 'null', '$')
1
select json_contains('"10"', '"10"', '$');
json_contains('"10"', '"10"', '$')
1
select json_contains('"10"', '10', '$');
json_contains('"10"', '10', '$')
0
select json_contains('10.1', '10', '$');
json_contains('10.1', '10', '$')
0
select json_contains('10.0', '10', '$');
json_contains('10.0', '10', '$')
1
select json_contains('[1]', '1');
json_contains('[1]', '1')
1
select json_contains('[2, 1]', '1');
json_contains('[2, 1]', '1')
1
select json_contains('[2, [2, 3], 1]', '1');
json_contains('[2, [2, 3], 1]', '1')
1
select json_contains('[4, [2, 3], 1]', '2');
json_contains('[4, [2, 3], 1]', '2')
1
select json_contains('[2, 1]', '[1, 2]');
json_contains('[2, 1]', '[1, 2]')
1
select json_contains('[2, 1]', '[1, 0, 2]');
json_contains('[2, 1]', '[1, 0, 2]')
0
select json_contains('[2, 0, 3, 1]', '[1, 2]');
json_contains('[2, 0, 3, 1]', '[1, 2]')
1
select json_contains('{"b":[1,2], "a":1}', '{"a":1, "b":2}');
json_contains('{"b":[1,2], "a":1}', '{"a":1, "b":2}')
1
select json_contains('{"a":1}', '{}');
json_contains('{"a":1}', '{}')
1
select json_contains('[1, {"a":1}]', '{}');
json_contains('[1, {"a":1}]', '{}')
1
select json_contains('[1, {"a":1}]', '{"a":1}');
json_contains('[1, {"a":1}]', '{"a":1}')
1
select json_contains('[{"abc":"def", "def":"abc"}]', '["foo","bar"]');
json_contains('[{"abc":"def", "def":"abc"}]', '["foo","bar"]')
0
select json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]');
json_contains('[{"abc":"def", "def":"abc"}, "bar"]', '["bar", {}]')
1
select json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}');
json_contains('[{"a":"b"},{"c":"d"}]','{"c":"d"}')
1
select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]");
json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[1]")
1
select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[10]");
json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.key2[10]")
0
select json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.ma");
json_contains_path('{"key1":1, "key2":[2,3]}', "oNE", "$.ma")
0
select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1");
json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1")
1
select json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1", "$.ma");
json_contains_path('{"key1":1, "key2":[2,3]}', "one", "$.key1", "$.ma")
1
select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.ma");
json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.ma")
0
select json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.key2");
json_contains_path('{"key1":1, "key2":[2,3]}', "aLl", "$.key1", "$.key2")
1
select json_contains_path('{ "a": true }', NULL, '$.a' );
json_contains_path('{ "a": true }', NULL, '$.a' )
NULL
select json_contains_path('{ "a": true }', 'all', NULL );
json_contains_path('{ "a": true }', 'all', NULL )
NULL
select json_contains_path('{"a":{"b":"c"}}', 'one', '$.a.*');
json_contains_path('{"a":{"b":"c"}}', 'one', '$.a.*')
1
select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1");
json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1")
"asd"
select json_extract('{"key1":"asd", "key2":[2,3]}', "$.keyX", "$.keyY");
json_extract('{"key1":"asd", "key2":[2,3]}', "$.keyX", "$.keyY")
NULL
select json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2");
json_extract('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2")
["asd", [2, 3]]
select json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2");
json_extract('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2")
[5, [2, 3]]
select json_extract('{"key0":true, "key1":"qwe"}', "$.key1");
json_extract('{"key0":true, "key1":"qwe"}', "$.key1")
"qwe"
select json_extract(json_object('foo', 'foobar'),'$');
json_extract(json_object('foo', 'foobar'),'$')
{"foo": "foobar"}
select json_extract('[10, 20, [30, 40]]', '$[2][*]');
json_extract('[10, 20, [30, 40]]', '$[2][*]')
[30, 40]
select json_extract('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]');
json_extract('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]')
[{"a": 3}, 30, 40]
select json_extract('1', '$');
json_extract('1', '$')
1
select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]');
json_extract('[10, 20, [30, 40], 1, 10]', '$[1]')
20
select json_extract('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]');
json_extract('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]')
[20]
select json_extract( '[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a');
json_extract( '[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a')
[[3, 4]]
select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word');
json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.k1', 'word')
{"a": 1, "b": {"c": 1, "k1": "word"}, "d": [1, 2]}
select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3);
json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.d[3]', 3)
{"a": 1, "b": {"c": 1}, "d": [1, 2, 3]}
select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2);
json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.a[2]', 2)
{"a": [1, 2], "b": {"c": 1}, "d": [1, 2]}
select json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word');
json_insert('{"a":1, "b":{"c":1}, "d":[1, 2]}', '$.b.c', 'word')
{"a": 1, "b": {"c": 1}, "d": [1, 2]}
select json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
json_set('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]')
{"a": 10, "b": [2, 3], "c": "[true, false]"}
select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]')
{"a": 10, "b": [2, 3]}
select json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]');
json_replace('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.b', '[true, false]')
{"a": 10, "b": "[true, false]"}
set @j = '["a", ["b", "c"], "d"]';
select json_remove(@j, '$[0]');
json_remove(@j, '$[0]')
[["b", "c"], "d"]
select json_remove(@j, '$[1]');
json_remove(@j, '$[1]')
["a", "d"]
select json_remove(@j, '$[2]');
json_remove(@j, '$[2]')
["a", ["b", "c"]]
set @j = '{"a": 1, "b": [2, 3]}';
select json_remove(@j, '$.b');
json_remove(@j, '$.b')
{"a": 1}
select json_remove(@j, '$.a');
json_remove(@j, '$.a')
{"b": [2, 3]}
select json_object();
json_object()
{}
select json_object("ki", 1, "mi", "ya");
json_object("ki", 1, "mi", "ya")
{"ki": 1, "mi": "ya"}
create table t1 as select json_object('id', 87, 'name', 'carrot') as f;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f` varchar(32) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
f
{"id": 87, "name": "carrot"}
drop table t1;
select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2");
json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2")
1
select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]");
json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]")
1
select json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]");
json_exists('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]")
0
select json_quote('"string"');
json_quote('"string"')
"\"string\""
create table t1 as select json_quote('foo');
select * from t1;
json_quote('foo')
"foo"
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `json_quote('foo')` varchar(38) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select json_merge('string');
ERROR 42000: Incorrect parameter count in the call to native function 'json_merge'
select json_merge('string', 123);
json_merge('string', 123)
NULL
Warnings:
Warning	4038	Syntax error in JSON text in argument 1 to function 'json_merge_preserve' at position 1
select json_merge('"string"', 123);
json_merge('"string"', 123)
["string", 123]
select json_merge('[1, 2]', '[true, false]');
json_merge('[1, 2]', '[true, false]')
[1, 2, true, false]
select json_merge('{"1": 2}', '{"true": false}');
json_merge('{"1": 2}', '{"true": false}')
{"1": 2, "true": false}
select json_merge('{"1": 2}', '{"true": false}', '{"3": 4}');
json_merge('{"1": 2}', '{"true": false}', '{"3": 4}')
{"1": 2, "true": false, "3": 4}
select json_merge(NULL,json_object('foo', 1));
json_merge(NULL,json_object('foo', 1))
NULL
select json_merge('a','b');
json_merge('a','b')
NULL
Warnings:
Warning	4038	Syntax error in JSON text in argument 1 to function 'json_merge_preserve' at position 1
select json_merge('{"a":"b"}','{"c":"d"}');
json_merge('{"a":"b"}','{"c":"d"}')
{"a": "b", "c": "d"}
SELECT JSON_MERGE('[1, 2]', '{"id": 47}');
JSON_MERGE('[1, 2]', '{"id": 47}')
[1, 2, {"id": 47}]
select json_type('{"k1":123, "k2":345}');
json_type('{"k1":123, "k2":345}')
OBJECT
select json_type('[123, "k2", 345]');
json_type('[123, "k2", 345]')
ARRAY
select json_type("true");
json_type("true")
BOOLEAN
select json_type('123');
json_type('123')
INTEGER
select json_type('123.12');
json_type('123.12')
DOUBLE
select json_keys('{"a":{"c":1, "d":2}, "b":2}');
json_keys('{"a":{"c":1, "d":2}, "b":2}')
["a", "b"]
select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.a");
json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.a")
["c", "d"]
select json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.b");
json_keys('{"a":{"c":1, "d":2}, "b":2}', "$.b")
NULL
select json_keys('foo');
json_keys('foo')
NULL
Warnings:
Warning	4038	Syntax error in JSON text in argument 1 to function 'json_keys' at position 1
select json_keys('{"a":{"c":1, "d":2}, "b":2, "c":1, "a":3, "b":1, "c":2}');
json_keys('{"a":{"c":1, "d":2}, "b":2, "c":1, "a":3, "b":1, "c":2}')
["a", "b", "c"]
select json_keys('{"c1": "value 1", "c1": "value 2"}');
json_keys('{"c1": "value 1", "c1": "value 2"}')
["c1"]
SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
select json_search(@j, 'one', 'abc');
json_search(@j, 'one', 'abc')
"$[0]"
select json_search(@j, 'all', 'abc');
json_search(@j, 'all', 'abc')
["$[0]", "$[2].x"]
select json_search(@j, 'all', 'abc', NULL, '$[2]');
json_search(@j, 'all', 'abc', NULL, '$[2]')
"$[2].x"
select json_search(@j, 'all', 'abc', NULL, '$');
json_search(@j, 'all', 'abc', NULL, '$')
["$[0]", "$[2].x"]
select json_search(@j, 'all', '10', NULL, '$');
json_search(@j, 'all', '10', NULL, '$')
"$[1][0].k"
select json_search(@j, 'all', '10', NULL, '$[*]');
json_search(@j, 'all', '10', NULL, '$[*]')
"$[1][0].k"
select json_search(@j, 'all', '10', NULL, '$[*][0].k');
json_search(@j, 'all', '10', NULL, '$[*][0].k')
"$[1][0].k"
select json_search(@j, 'all', '10', NULL, '$**.k');
json_search(@j, 'all', '10', NULL, '$**.k')
"$[1][0].k"
create table t1( json_col text );
insert into t1 values
('{ "a": "foobar" }'),
('{ "a": "foobar", "b": "focus", "c": [ "arm", "foot", "shoulder" ] }');
select json_search( json_col, 'all', 'foot' ) from t1;
json_search( json_col, 'all', 'foot' )
NULL
"$.c[1]"
drop table t1;
select json_unquote('"abc"');
json_unquote('"abc"')
abc
select json_unquote('abc');
json_unquote('abc')
abc
create table t1 (c VARCHAR(8)) DEFAULT CHARSET=latin1;
insert into t1 values ('abc'),('def');
select json_object('foo', json_unquote(json_object('bar', c)),'qux', c) as fld from t1;
fld
{"foo": "{\"bar\": \"abc\"}", "qux": "abc"}
{"foo": "{\"bar\": \"def\"}", "qux": "def"}
drop table t1;
select json_object("a", json_object("b", "abcd"));
json_object("a", json_object("b", "abcd"))
{"a": {"b": "abcd"}}
select json_object("a", '{"b": "abcd"}');
json_object("a", '{"b": "abcd"}')
{"a": "{\"b\": \"abcd\"}"}
select json_object("a", json_compact('{"b": "abcd"}'));
json_object("a", json_compact('{"b": "abcd"}'))
{"a": {"b": "abcd"}}
select json_compact(NULL);
json_compact(NULL)
NULL
select json_depth(json_compact(NULL));
json_depth(json_compact(NULL))
NULL
select json_depth('[[], {}]');
json_depth('[[], {}]')
2
select json_depth('[[[1,2,3],"s"], {}, []]');
json_depth('[[[1,2,3],"s"], {}, []]')
4
select json_depth('[10, {"a": 20}]');
json_depth('[10, {"a": 20}]')
3
select json_length('');
json_length('')
NULL
Warnings:
Warning	4037	Unexpected end of JSON text in argument 1 to function 'json_length'
select json_length('{}');
json_length('{}')
0
select json_length('[1, 2, {"a": 3}]');
json_length('[1, 2, {"a": 3}]')
3
select json_length('{"a": 1, "b": {"c": 30}}', '$.b');
json_length('{"a": 1, "b": {"c": 30}}', '$.b')
1
select json_length('{"a": 1, "b": {"c": 30}}');
json_length('{"a": 1, "b": {"c": 30}}')
2
select json_length('{}{');
json_length('{}{')
NULL
Warnings:
Warning	4038	Syntax error in JSON text in argument 1 to function 'json_length' at position 3
create table json (j INT);
show create table json;
Table	Create Table
json	CREATE TABLE `json` (
  `j` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table json;
select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' );
json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2]' )
1
select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' );
json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0]' )
1
select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0]' );
json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0]' )
1
select json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0][0]' );
json_length( '[ 1, [ 2, 3, 4 ], 5 ]', '$[2][0][0][0]' )
1
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2]' );
json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2]' )
2
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0]' );
json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0]' )
2
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0]' );
json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0]' )
2
select json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0][0]' );
json_length( '[ 1, [ 2, 3, 4 ], {"a":5, "b":6} ]', '$[2][0][0][0]' )
2
select json_length( '{"a":{"b":{"d":1}}, "a":{"c":{"d":1, "j":2}}}', '$.a[0][0][0].c' );
json_length( '{"a":{"b":{"d":1}}, "a":{"c":{"d":1, "j":2}}}', '$.a[0][0][0].c' )
2
select json_set('1', '$[0]', 100);
json_set('1', '$[0]', 100)
100
select json_set('1', '$[0][0]', 100);
json_set('1', '$[0][0]', 100)
100
select json_set('1', '$[1]', 100);
json_set('1', '$[1]', 100)
[1, 100]
select json_set('{"a":12}', '$[0]', 100);
json_set('{"a":12}', '$[0]', 100)
100
select json_set('{"a":12}', '$[0].a', 100);
json_set('{"a":12}', '$[0].a', 100)
{"a": 100}
select json_set('{"a":12}', '$[0][0].a', 100);
json_set('{"a":12}', '$[0][0].a', 100)
{"a": 100}
select json_set('{"a":12}', '$[0][1].a', 100);
json_set('{"a":12}', '$[0][1].a', 100)
{"a": 12}
select json_value('{"\\"key1":123}', '$."\\"key1"');
json_value('{"\\"key1":123}', '$."\\"key1"')
123
select json_value('{"\\"key1\\"":123}', '$."\\"key1\\""');
json_value('{"\\"key1\\"":123}', '$."\\"key1\\""')
123
select json_value('{"key 1":123}', '$."key 1"');
json_value('{"key 1":123}', '$."key 1"')
123
select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]");
json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[2]")
1
select json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[3]");
json_contains_path('{"a":[{"c":[1,{"a":[0,1,2]},3]}], "b":[1,2,3]}', 'one', "$**.a[3]")
0
select json_extract( '[1]', '$[0][0]' );
json_extract( '[1]', '$[0][0]' )
1
select json_extract( '[1]', '$[1][0]' );
json_extract( '[1]', '$[1][0]' )
NULL
select json_extract( '[1]', '$**[0]' );
json_extract( '[1]', '$**[0]' )
[1]
select json_extract( '[1]', '$**[0][0]' );
json_extract( '[1]', '$**[0][0]' )
[1]
select json_insert('1', '$[0]', 4);
json_insert('1', '$[0]', 4)
1
select json_replace('1', '$[0]', 4);
json_replace('1', '$[0]', 4)
4
select json_set('1', '$[0]', 4);
json_set('1', '$[0]', 4)
4
select json_set('1', '$[1]', 4);
json_set('1', '$[1]', 4)
[1, 4]
select json_replace('1', '$[1]', 4);
json_replace('1', '$[1]', 4)
1
SELECT json_insert('[]', '$[0][0]', 100);
json_insert('[]', '$[0][0]', 100)
[]
SELECT json_insert('1', '$[0][0]', 100);
json_insert('1', '$[0][0]', 100)
1
SELECT json_replace('1', '$[0][0]', 100);
json_replace('1', '$[0][0]', 100)
100
SELECT json_replace('[]', '$[0][0]', 100);
json_replace('[]', '$[0][0]', 100)
[]
SELECT json_set('[]', '$[0][0]', 100);
json_set('[]', '$[0][0]', 100)
[]
SELECT json_set('[]', '$[0][0][0]', 100);
json_set('[]', '$[0][0][0]', 100)
[]
SELECT JSON_search( '{"": "a"}', "one", 'a');
JSON_search( '{"": "a"}', "one", 'a')
"$."
select json_merge('{"a":"b"}', '{"a":"c"}') ;
json_merge('{"a":"b"}', '{"a":"c"}')
{"a": ["b", "c"]}
select json_merge('{"a":{"x":"b"}}', '{"a":"c"}') ;
json_merge('{"a":{"x":"b"}}', '{"a":"c"}')
{"a": [{"x": "b"}, "c"]}
select json_merge('{"a":{"u":12, "x":"b"}}', '{"a":{"x":"c"}}') ;
json_merge('{"a":{"u":12, "x":"b"}}', '{"a":{"x":"c"}}')
{"a": {"u": 12, "x": ["b", "c"]}}
select json_merge('{"a":{"u":12, "x":"b", "r":1}}', '{"a":{"x":"c", "r":2}}') ;
json_merge('{"a":{"u":12, "x":"b", "r":1}}', '{"a":{"x":"c", "r":2}}')
{"a": {"u": 12, "x": ["b", "c"], "r": [1, 2]}}
select json_compact('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}');
json_compact('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}')
{"a":1,"b":[1,2,3],"c":{"aa":"v1","bb":"v2"}}
select json_loose('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}');
json_loose('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}')
{"a": 1, "b": [1, 2, 3], "c": {"aa": "v1", "bb": "v2"}}
select json_detailed('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}');
json_detailed('{"a":1, "b":[1,2,3], "c":{"aa":"v1", "bb": "v2"}}')
{
    "a": 1,
    "b": 
    [
        1,
        2,
        3
    ],
    "c": 
    {
        "aa": "v1",
        "bb": "v2"
    }
}
SELECT JSON_search( '{"x": "\\""}', "one", '"');
JSON_search( '{"x": "\\""}', "one", '"')
"$.x"
SELECT JSON_search( '{"x": "\\""}', "one", '\\"');
JSON_search( '{"x": "\\""}', "one", '\\"')
"$.x"
set @save_max_allowed_packet=@@max_allowed_packet;
set @save_net_buffer_length=@@net_buffer_length;
set @@global.net_buffer_length=1024;
set @@global.max_allowed_packet=2048;
connect  newconn, localhost, root,,;
show variables like 'net_buffer_length';
Variable_name	Value
net_buffer_length	1024
show variables like 'max_allowed_packet';
Variable_name	Value
max_allowed_packet	2048
select json_array(repeat('a',1024),repeat('a',1024));
json_array(repeat('a',1024),repeat('a',1024))
NULL
Warnings:
Warning	1301	Result of json_array() was larger than max_allowed_packet (2048) - truncated
select json_object("a", repeat('a',1024),"b", repeat('a',1024));
json_object("a", repeat('a',1024),"b", repeat('a',1024))
NULL
Warnings:
Warning	1301	Result of json_object() was larger than max_allowed_packet (2048) - truncated
connection default;
set @@global.max_allowed_packet = @save_max_allowed_packet;
set @@global.net_buffer_length = @save_net_buffer_length;
disconnect newconn;
create table t1(j longtext, p longtext);
insert into t1 values
('{"a":1,"b":2,"c":3}','$.a'),
('{"a":1,"b":2,"c":3}','$.b'),
('{"a":1,"b":2,"c":3}','$.c');
select j, p, json_remove(j, p) from t1;
j	p	json_remove(j, p)
{"a":1,"b":2,"c":3}	$.a	{"b": 2, "c": 3}
{"a":1,"b":2,"c":3}	$.b	{"a": 1, "c": 3}
{"a":1,"b":2,"c":3}	$.c	{"a": 1, "b": 2}
drop table t1;
SET @str = 'bar', @path = '$';
SELECT JSON_SEARCH('{"foo":"bar"}', 'all' , @str, '%', @path);
JSON_SEARCH('{"foo":"bar"}', 'all' , @str, '%', @path)
"$.foo"
SELECT JSON_VALUE('[{"foo": 1},"bar"]', '$[*][0]');
JSON_VALUE('[{"foo": 1},"bar"]', '$[*][0]')
bar
CREATE TABLE t1 (f INT NOT NULL);
INSERT INTO t1 VALUES (0);
SELECT JSON_KEYS(f) FROM t1 ORDER BY 1;
JSON_KEYS(f)
NULL
DROP TABLE t1;
SELECT JSON_EXTRACT( '{"foo":"bar"}', '$[*].*' );
JSON_EXTRACT( '{"foo":"bar"}', '$[*].*' )
NULL
SELECT JSON_EXTRACT( '{"foo":"bar"}', '$[*]' );
JSON_EXTRACT( '{"foo":"bar"}', '$[*]' )
NULL
select JSON_EXTRACT('{"name":"value"}', '$.name') = 'value';
JSON_EXTRACT('{"name":"value"}', '$.name') = 'value'
1
select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = true;
JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = true
1
select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = false;
JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = false
0
select JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = 1;
JSON_EXTRACT('{\"asdf\":true}', "$.\"asdf\"") = 1
1
select JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"');
JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"')
"\u00f6"
select JSON_EXTRACT('{"foo": "bar" foobar foo invalid ', '$.foo');
JSON_EXTRACT('{"foo": "bar" foobar foo invalid ', '$.foo')
NULL
Warnings:
Warning	4038	Syntax error in JSON text in argument 1 to function 'json_extract' at position 15
SELECT JSON_OBJECT('foo', '`');
JSON_OBJECT('foo', '`')
{"foo": "`"}
SELECT JSON_OBJECT("foo", "bar`bar");
JSON_OBJECT("foo", "bar`bar")
{"foo": "bar`bar"}
SELECT JSON_SET('{}', '$.age', 87);
JSON_SET('{}', '$.age', 87)
{"age": 87}
SELECT JSON_MERGE('[]', '{"c":"d"}');
JSON_MERGE('[]', '{"c":"d"}')
[{"c": "d"}]
SET @str = "{\"\\u00e4\\u00f6\":\"yes\"}";
SET @path = "$.\"\\u00e4\\u00f6\"";
select @str, @path, JSON_EXTRACT(@str, @path);
@str	@path	JSON_EXTRACT(@str, @path)
{"\u00e4\u00f6":"yes"}	$."\u00e4\u00f6"	"yes"
SET @str = "{\"\\u00e4\":\"yes\"}";
SET @path = "$.\"\\u00e4\"";
select @str, @path, JSON_EXTRACT(@str, @path);
@str	@path	JSON_EXTRACT(@str, @path)
{"\u00e4":"yes"}	$."\u00e4"	"yes"
select json_array(5,json_query('[1,2]','$'));
json_array(5,json_query('[1,2]','$'))
[5, [1,2]]
SELECT JSON_ARRAY('1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú') AS json_data;
json_data
["1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú"]
SELECT JSON_OBJECT("user","Jožko Mrkvičká") as json_data;
json_data
{"user": "Jožko Mrkvičká"}
select json_contains_path('{"foo":"bar"}', 'one', '$[]');
json_contains_path('{"foo":"bar"}', 'one', '$[]')
NULL
Warnings:
Warning	4042	Syntax error in JSON path in argument 3 to function 'json_contains_path' at position 3
select JSON_VALID(0x36f0c8dccd83c5eac156da);
JSON_VALID(0x36f0c8dccd83c5eac156da)
0
create table t1(a double not null);
insert into t1 values (2),(1);
select 1 from t1 where json_extract(a,'$','$[81]');
1
drop table t1;
select json_extract('{"test":8.437e-5}','$.test');
json_extract('{"test":8.437e-5}','$.test')
8.437e-5
select json_value('{"b":true}','$.b')=1;
json_value('{"b":true}','$.b')=1
1
CREATE TABLE t1 (c VARCHAR(8));
INSERT INTO t1 VALUES ('foo'),('bar');
SELECT * FROM t1 WHERE c IN (JSON_EXTRACT('{"a":"b"}', '$.*'));
c
DROP TABLE t1;
#
# MDEV-16814 CREATE TABLE SELECT JSON_QUOTE(multibyte_charset_expr) makes a field of a wrong length
#
CREATE TABLE t1 AS SELECT
JSON_QUOTE(_latin1'foo') AS c1,
JSON_QUOTE(_utf8'foo') AS c2;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(38) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `c2` varchar(38) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
#
# MDEV-16054 simple json functions flatline cpu on garbage input.
#
select json_array(1,uuid(),compress(5.140264e+307));
json_array(1,uuid(),compress(5.140264e+307))
NULL
#
# MDEV-16869 String functions don't respect character set of JSON_VALUE.
#
create table t1(json_col TEXT) DEFAULT CHARSET=latin1;
insert into t1 values (_latin1 X'7B226B657931223A2253EC227D');
select JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' from t1;
JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC'
1
select REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' from t1;
REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC'
1
drop table t1;
#
# MDEV-16750 JSON_SET mishandles unicode every second pair of arguments.
#
SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6);
JSON_SET('{}', '$.a', _utf8 0xC3B6)
{"a": "�"}
SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6);
JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6)
{"a": "�", "b": "�"}
SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6');
JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6')
{"a": "�", "x": 1, "b": "�"}
#
# MDEV-17121 JSON_ARRAY_APPEND
#
select json_array_append('[             ]', '$', 'aue');
json_array_append('[             ]', '$', 'aue')
["aue"]
#
# MDEV-17018 JSON_SEARCH and User-Defined Variables.
#
SET @`json` := '["A", [{"B": "1"}], {"C": "AB"}, {"D": "BC"}]', @`value` := 'AB';
SELECT JSON_SEARCH(@`json`, 'one', @`value`);
JSON_SEARCH(@`json`, 'one', @`value`)
"$[2].C"
SET @`json` := NULL, @`value` := NULL;
#
# MDEV-17001 JSON_MERGE returns nullwhen merging empty array.
#
SELECT JSON_MERGE('[1]', '[]');
JSON_MERGE('[1]', '[]')
[1]
#
# MDEV-16174 Assertion `0' failed in Type_handler_string_result::
#       make_sort_key(uchar*, Item*, const SORT_FIELD_ATTR*, Sort_param*)
#
SET sql_mode='';
CREATE TABLE t1 (fld varchar(16) NOT NULL);
CREATE TABLE t2 SELECT JSON_ARRAY_INSERT(fld, '$.[0]', '0') FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `JSON_ARRAY_INSERT(fld, '$.[0]', '0')` varchar(25) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1, t2;
SET sql_mode=default;
#
# MDEV-17454 JSON_VALID( '{"a":1]' ) evaluates to 1
#
select JSON_VALID( '{"a":1]' );
JSON_VALID( '{"a":1]' )
0
#
# MDEV-18886 JSON_ARRAY() does not recognise JSON argument.
#
SELECT JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket'));
JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket'))
["str", {"plugin": "unix_socket"}]
SELECT CHARSET(JSON_ARRAY());
CHARSET(JSON_ARRAY())
latin1
SELECT CHARSET(JSON_OBJECT());
CHARSET(JSON_OBJECT())
latin1
#
# MDEV-13992 Implement JSON_MERGE_PATCH
#
CREATE TABLE merge_t(
id INT PRIMARY KEY AUTO_INCREMENT,
target VARCHAR(100), patch VARCHAR(100)
);
INSERT INTO merge_t(target, patch) VALUES
('{"a":"b"}', '{"a":"c"}'),
('{"a":"b"}', '{"b":"c"}'),
('{"a":"b"}', '{"a":null}'),
('{"a":"b", "b":"c"}', '{"a":null}'),
('{"a":["b"]}', '{"a":"c"}'),
('{"a":"c"}', '{"a":["b"]}'),
('{"a": {"b":"c"}}', '{"a": {"b":"d", "c":null}}'),
('{"a":[{"b":"c"}]}', '{"a": [1]}'),
('["a","b"]', '["c","d"]'),
('{"a":"b"}', '["c"]'),
('{"a":"foo"}', 'null'),
('{"a":"foo"}', '"bar"'),
('{"e":null}', '{"a":1}'),
('[1,2]', '{"a":"b", "c":null}'),
('{}', '{"a":{"bb":{"ccc":null}}}'),
(NULL, '{}'),
('{}', NULL);
SELECT id, target, patch,
JSON_MERGE_PATCH(target, patch) AS merged,
JSON_EXTRACT(JSON_MERGE_PATCH(target, patch), '$.a') AS a
FROM merge_t ORDER BY id;
id	target	patch	merged	a
1	{"a":"b"}	{"a":"c"}	{"a": "c"}	"c"
2	{"a":"b"}	{"b":"c"}	{"a": "b", "b": "c"}	"b"
3	{"a":"b"}	{"a":null}	{}	NULL
4	{"a":"b", "b":"c"}	{"a":null}	{"b": "c"}	NULL
5	{"a":["b"]}	{"a":"c"}	{"a": "c"}	"c"
6	{"a":"c"}	{"a":["b"]}	{"a": ["b"]}	["b"]
7	{"a": {"b":"c"}}	{"a": {"b":"d", "c":null}}	{"a": {"b": "d"}}	{"b": "d"}
8	{"a":[{"b":"c"}]}	{"a": [1]}	{"a": [1]}	[1]
9	["a","b"]	["c","d"]	["c", "d"]	NULL
10	{"a":"b"}	["c"]	["c"]	NULL
11	{"a":"foo"}	null	null	NULL
12	{"a":"foo"}	"bar"	"bar"	NULL
13	{"e":null}	{"a":1}	{"e": null, "a": 1}	1
14	[1,2]	{"a":"b", "c":null}	{"a": "b"}	"b"
15	{}	{"a":{"bb":{"ccc":null}}}	{"a": {"bb": {}}}	{"bb": {}}
16	NULL	{}	NULL	NULL
17	{}	NULL	NULL	NULL
DROP TABLE merge_t;
SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}');
JSON_MERGE_PATCH('{"a":"b"}', NULL, '{"c":"d"}')
NULL
SELECT JSON_MERGE_PATCH(NULL, '[1,2,3]');
JSON_MERGE_PATCH(NULL, '[1,2,3]')
[1, 2, 3]
SELECT JSON_MERGE_PATCH(NULL, 'a');
JSON_MERGE_PATCH(NULL, 'a')
NULL
Warnings:
Warning	4038	Syntax error in JSON text in argument 2 to function 'json_merge_patch' at position 1
SELECT JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}');
JSON_MERGE_PATCH('{"a":"b"}', NULL, '[1,2,3]', '{"c":null,"d":"e"}')
{"d": "e"}
SELECT JSON_MERGE_PATCH();
ERROR 42000: Incorrect parameter count in the call to native function 'JSON_MERGE_PATCH'
SELECT JSON_MERGE_PATCH('{}');
ERROR 42000: Incorrect parameter count in the call to native function 'JSON_MERGE_PATCH'
SELECT JSON_MERGE_PATCH('{', '[1,2,3]');
JSON_MERGE_PATCH('{', '[1,2,3]')
NULL
Warnings:
Warning	4037	Unexpected end of JSON text in argument 1 to function 'json_merge_patch'
SELECT JSON_MERGE_PATCH('{"a":"b"}', '[1,');
JSON_MERGE_PATCH('{"a":"b"}', '[1,')
NULL
Warnings:
Warning	4037	Unexpected end of JSON text in argument 2 to function 'json_merge_patch'
#
# MDEV-22976 CAST(JSON_EXTRACT() AS DECIMAL) does not handle boolean values
#
SELECT
CAST(JSON_EXTRACT('{"x":true}', '$.x') AS DOUBLE) AS cf,
CAST(JSON_EXTRACT('{"x":true}', '$.x') AS DECIMAL) AS cd;
cf	cd
1	1
SELECT
CAST(JSON_EXTRACT('{"x":false}', '$.x') AS DOUBLE) AS cf,
CAST(JSON_EXTRACT('{"x":false}', '$.x') AS DECIMAL) AS cd;
cf	cd
0	0
#
# MDEV-24585 Assertion `je->s.cs == nice_js->charset()' failed in json_nice.
#
SELECT JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' );
JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' )
["x"]
#
# MDEV-18284 JSON casting using JSON_COMPACT doesn't always work
#            with values from subqueries
#
CREATE TABLE json_test(a JSON, b JSON);
INSERT INTO json_test VALUES ("[1,2,3]", '{"a":"foo"}');
SELECT * FROM json_test;
a	b
[1,2,3]	{"a":"foo"}
SELECT json_object("a", json_compact(a), "b", b)
FROM (SELECT * FROM json_test) AS json_test_values;
json_object("a", json_compact(a), "b", b)
{"a": [1,2,3], "b": {"a":"foo"}}
SELECT json_object("a", json_compact(a), "b", json_compact(b))
FROM (SELECT * FROM json_test) AS json_test_values;
json_object("a", json_compact(a), "b", json_compact(b))
{"a": [1,2,3], "b": {"a":"foo"}}
DROP TABLE json_test;
#
# End of 10.2 tests
#
#
# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
#
SELECT
JSON_VALID('{"id": 1, "name": "Monty"}') AS json_valid,
JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") AS json_exists,
JSON_CONTAINS('{"A": 0, "B": {"C": 1}, "D": 2}', '2', '$.A') AS ison_contains,
JSON_CONTAINS_PATH('{"A": 1, "B": [2], "C": [3, 4]}', 'one', '$.A', '$.D') AS json_contains_path;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					json_valid	3	1	1	Y	32896	0	63
def					json_exists	3	1	1	Y	32896	0	63
def					ison_contains	3	1	1	Y	32896	0	63
def					json_contains_path	3	1	1	Y	32896	0	63
json_valid	json_exists	ison_contains	json_contains_path
1	1	0	1
SELECT
JSON_LENGTH('{"a": 1, "b": {"c": 30}}') AS json_length,
JSON_DEPTH('[10, {"a": 20}]') AS json_depnth;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					json_length	3	10	1	Y	32896	0	63
def					json_depnth	3	10	1	N	32897	0	63
json_length	json_depnth
2	3
#
# MDEV-19670 json escaped unicode parse error
#
SELECT json_valid('{"value":"\\ud83d\\ude0a"}');
json_valid('{"value":"\\ud83d\\ude0a"}')
1
SELECT json_valid('{"test": "\\ud83d\\ude0b"}');
json_valid('{"test": "\\ud83d\\ude0b"}')
1
#
# MDEV-19670 json escaped unicode parse error
#
SELECT JSON_VALID('{"admin\\"": null}'), '{"admin\\"": null}'
    UNION
SELECT JSON_VALID('{"\\"admin": null}'), '{"\\"admin": null}'
      UNION
SELECT JSON_VALID('{"\\"": null}'), '{"\\"": null}';
JSON_VALID('{"admin\\"": null}')	{"admin\"": null}
1	{"admin\"": null}
1	{"\"admin": null}
1	{"\"": null}
#
# End of 10.3 tests
#
#
# Start of 10.4 tests
#
#
# MDEV-16351 JSON_OBJECT() treats hybrid functions with boolean arguments as numbers
#
SELECT
JSON_OBJECT("cond", true) AS j1,
JSON_OBJECT("cond", COALESCE(true, false)) j2,
JSON_OBJECT("cond", COALESCE(COALESCE(true, false))) j3;
j1	{"cond": true}
j2	{"cond": true}
j3	{"cond": true}
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
SELECT JSON_OBJECT('x',(SELECT MAX(a)=4 FROM t1));
JSON_OBJECT('x',(SELECT MAX(a)=4 FROM t1))
{"x": false}
SELECT JSON_OBJECT('x',(SELECT MAX(a)=3 FROM t1));
JSON_OBJECT('x',(SELECT MAX(a)=3 FROM t1))
{"x": true}
SELECT JSON_OBJECT('x',(SELECT MAX(a)=2 FROM t1));
JSON_OBJECT('x',(SELECT MAX(a)=2 FROM t1))
{"x": false}
SELECT JSON_OBJECT('x',MAX(a=4)) FROM t1;
JSON_OBJECT('x',MAX(a=4))
{"x": false}
SELECT JSON_OBJECT('x',MAX(a=3)) FROM t1;
JSON_OBJECT('x',MAX(a=3))
{"x": true}
SELECT JSON_OBJECT('x',MAX(a=2)) FROM t1;
JSON_OBJECT('x',MAX(a=2))
{"x": true}
SELECT JSON_OBJECT('x',(SELECT MAX(a=4) FROM t1));
JSON_OBJECT('x',(SELECT MAX(a=4) FROM t1))
{"x": false}
SELECT JSON_OBJECT('x',(SELECT MAX(a=3) FROM t1));
JSON_OBJECT('x',(SELECT MAX(a=3) FROM t1))
{"x": true}
SELECT JSON_OBJECT('x',(SELECT MAX(a=2) FROM t1));
JSON_OBJECT('x',(SELECT MAX(a=2) FROM t1))
{"x": true}
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=4 FROM t1))='{"x": true}' THEN a END;
a
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=4 FROM t1))='{"x": false}' THEN a END;
a
1
2
3
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=3 FROM t1))='{"x": true}' THEN a END;
a
1
2
3
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=3 FROM t1))='{"x": false}' THEN a END;
a
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=2 FROM t1))='{"x": true}' THEN a END;
a
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a)=2 FROM t1))='{"x": false}' THEN a END;
a
1
2
3
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=4) FROM t1))='{"x": true}' THEN a END;
a
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=4) FROM t1))='{"x": false}' THEN a END;
a
1
2
3
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=3) FROM t1))='{"x": true}' THEN a END;
a
1
2
3
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=3) FROM t1))='{"x": false}' THEN a END;
a
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{"x": true}' THEN a END;
a
1
2
3
SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{"x": false}' THEN a END;
a
DROP TABLE t1;
#
# MDEV-16620 JSON_ARRAYAGG
#
#
# Integer aggregation
#
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1, 1),(2, 1), (1, 1),(2, 1), (3, 2),(2, 2),(2, 2),(2, 2);
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
JSON_VALID(JSON_ARRAYAGG(a))
1
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
JSON_ARRAYAGG(a)	JSON_ARRAYAGG(b)
[1,2,1,2,3,2,2,2]	[1,1,1,1,2,2,2,2]
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b;
JSON_ARRAYAGG(a)	JSON_ARRAYAGG(b)
[1,2,1,2]	[1,1,1,1]
[3,2,2,2]	[2,2,2,2]
DROP TABLE t1;
#
# Real aggregation
#
CREATE TABLE t1 (a FLOAT, b DOUBLE, c DECIMAL(10, 2));
INSERT INTO t1 VALUES (1.0, 2.0, 3.0),(1.0, 3.0, 9.0),(1.0, 4.0, 16.0),(1.0, 5.0, 25.0);
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
JSON_VALID(JSON_ARRAYAGG(a))
1
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b), JSON_ARRAYAGG(c) FROM t1;
JSON_ARRAYAGG(a)	JSON_ARRAYAGG(b)	JSON_ARRAYAGG(c)
[1,1,1,1]	[2,3,4,5]	[3.00,9.00,16.00,25.00]
DROP TABLE t1;
#
# Boolean aggregation
#
CREATE TABLE t1 (a BOOLEAN, b BOOLEAN);
INSERT INTO t1 VALUES (TRUE, TRUE), (TRUE, FALSE), (FALSE, TRUE), (FALSE, FALSE);
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
JSON_VALID(JSON_ARRAYAGG(a))
1
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
JSON_ARRAYAGG(a)	JSON_ARRAYAGG(b)
[1,1,0,0]	[1,0,1,0]
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b;
JSON_ARRAYAGG(a)	JSON_ARRAYAGG(b)
[1,0]	[0,0]
[1,0]	[1,1]
SELECT JSON_ARRAYAGG(TRUE), JSON_ARRAYAGG(FALSE) FROM t1;
JSON_ARRAYAGG(TRUE)	JSON_ARRAYAGG(FALSE)
[true,true,true,true]	[false,false,false,false]
DROP TABLE t1;
#
# Aggregation of strings with quoted
#
CREATE TABLE t1 (a VARCHAR(80));
INSERT INTO t1 VALUES
('"double_quoted_value"'), ("'single_quoted_value'"),
('"double_quoted_value"'), ("'single_quoted_value'");
SELECT JSON_VALID(JSON_ARRAYAGG(a)) FROM t1;
JSON_VALID(JSON_ARRAYAGG(a))
1
SELECT JSON_ARRAYAGG(a) FROM t1;
JSON_ARRAYAGG(a)
["\"double_quoted_value\"","'single_quoted_value'","\"double_quoted_value\"","'single_quoted_value'"]
DROP TABLE t1;
#
# Strings and NULLs
#
CREATE TABLE t1 (a INT, b VARCHAR(80));
INSERT INTO t1 VALUES
(1, "Hello"),(1, "World"), (2, "This"),(2, "Will"), (2, "Work"),(2, "!"), (3, NULL),
(1, "Hello"),(1, "World"), (2, "This"),(2, "Will"), (2, "Work"),(2, "!"), (3, NULL);
SELECT JSON_VALID(JSON_ARRAYAGG(b)) FROM t1;
JSON_VALID(JSON_ARRAYAGG(b))
1
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
JSON_ARRAYAGG(a)	JSON_ARRAYAGG(b)
[1,1,2,2,2,2,3,1,1,2,2,2,2,3]	["Hello","World","This","Will","Work","!",null,"Hello","World","This","Will","Work","!",null]
SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY a;
JSON_ARRAYAGG(a)	JSON_ARRAYAGG(b)
[1,1,1,1]	["Hello","World","Hello","World"]
[2,2,2,2,2,2,2,2]	["!","Work","Will","This","Will","This","!","Work"]
[3,3]	[null,null]
#
# DISTINCT and LIMIT
#
SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1;
JSON_ARRAYAGG(b LIMIT 1)
["Hello"]
SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1;
JSON_ARRAYAGG(b LIMIT 2)
["Hello","World"]
SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1 GROUP BY b;
JSON_ARRAYAGG(b LIMIT 1)
[null]
["!"]
["Hello"]
["This"]
["Will"]
["Work"]
["World"]
SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1 GROUP BY a;
JSON_ARRAYAGG(b LIMIT 2)
["Hello","World"]
["!","Work"]
[null,null]
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
JSON_ARRAYAGG(DISTINCT a)
[1,2,3]
SELECT JSON_ARRAYAGG(DISTINCT b) FROM t1;
JSON_ARRAYAGG(DISTINCT b)
[null,"!","Hello","This","Will","Work","World"]
SELECT JSON_ARRAYAGG(DISTINCT a LIMIT 2) FROM t1;
JSON_ARRAYAGG(DISTINCT a LIMIT 2)
[1,2]
SELECT JSON_ARRAYAGG(DISTINCT b LIMIT 2) FROM t1;
JSON_ARRAYAGG(DISTINCT b LIMIT 2)
[null,"!"]
#
# JSON aggregation
#
SELECT JSON_VALID(JSON_ARRAYAGG(JSON_ARRAY(a, b))) FROM t1;
JSON_VALID(JSON_ARRAYAGG(JSON_ARRAY(a, b)))
1
SELECT JSON_ARRAYAGG(JSON_ARRAY(a, b)) FROM t1;
JSON_ARRAYAGG(JSON_ARRAY(a, b))
[[1, "Hello"],[1, "World"],[2, "This"],[2, "Will"],[2, "Work"],[2, "!"],[3, null],[1, "Hello"],[1, "World"],[2, "This"],[2, "Will"],[2, "Work"],[2, "!"],[3, null]]
SELECT JSON_ARRAYAGG(JSON_ARRAY(a, b)) FROM t1 GROUP BY a;
JSON_ARRAYAGG(JSON_ARRAY(a, b))
[[1, "Hello"],[1, "World"],[1, "Hello"],[1, "World"]]
[[2, "!"],[2, "Work"],[2, "Will"],[2, "This"],[2, "Will"],[2, "This"],[2, "!"],[2, "Work"]]
[[3, null],[3, null]]
SELECT JSON_VALID(JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b))) FROM t1;
JSON_VALID(JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)))
1
SELECT JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) FROM t1;
JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b))
[{"a": 1, "b": "Hello"},{"a": 1, "b": "World"},{"a": 2, "b": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "Work"},{"a": 2, "b": "!"},{"a": 3, "b": null},{"a": 1, "b": "Hello"},{"a": 1, "b": "World"},{"a": 2, "b": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "Work"},{"a": 2, "b": "!"},{"a": 3, "b": null}]
SELECT JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) FROM t1 GROUP BY a;
JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b))
[{"a": 1, "b": "Hello"},{"a": 1, "b": "World"},{"a": 1, "b": "Hello"},{"a": 1, "b": "World"}]
[{"a": 2, "b": "!"},{"a": 2, "b": "Work"},{"a": 2, "b": "Will"},{"a": 2, "b": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "This"},{"a": 2, "b": "!"},{"a": 2, "b": "Work"}]
[{"a": 3, "b": null},{"a": 3, "b": null}]
#
# Error checks
#
SELECT JSON_ARRAYAGG(a, b) FROM t1;
ERROR 42000: Incorrect parameter count in the call to native function 'JSON_ARRAYAGG'
SELECT JSON_ARRAYAGG(JSON_ARRAYAGG(a, b)) FROM t1;
ERROR 42000: Incorrect parameter count in the call to native function 'JSON_ARRAYAGG'
SELECT JSON_ARRAYAGG(JSON_ARRAYAGG(a)) FROM t1;
ERROR HY000: Invalid use of group function
#
# MDEV-16620 JSON_OBJECTAGG
#
SELECT JSON_OBJECTAGG(a, b) FROM t1;
JSON_OBJECTAGG(a, b)
{"1":"Hello", "1":"World", "2":"This", "2":"Will", "2":"Work", "2":"!", "3":null, "1":"Hello", "1":"World", "2":"This", "2":"Will", "2":"Work", "2":"!", "3":null}
SELECT JSON_OBJECTAGG(a) FROM t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') FROM t1' at line 1
DROP TABLE t1;
#
# End of 10.4 tests
#
#
# MDEV-16620 JSON_ARRAYAGG
#
CREATE TABLE t1 (a INT);
SELECT JSON_ARRAYAGG(a) FROM t1;
JSON_ARRAYAGG(a)
NULL
DROP TABLE t1;
#
# MDEV-21915 Server crashes in copy_fields,Item_func_group_concat::add
while using json_arrayagg() as a window function
#
select json_arrayagg(a) over () from (select 1 a) t;
ERROR 42000: This version of MariaDB doesn't yet support 'JSON_ARRAYAGG() aggregate as window function'
select json_objectagg(a, b) over () from (select 1 a, 2 b) t;
ERROR 42000: This version of MariaDB doesn't yet support 'JSON_OBJECTAGG() aggregate as window function'
SELECT JSON_ARRAYAGG(NULL) FROM (SELECT 1 AS t) AS A;
JSON_ARRAYAGG(NULL)
[null]
SELECT JSON_ARRAYAGG("null") FROM (SELECT 1 AS t) AS A;
JSON_ARRAYAGG("null")
["null"]
create view v as (select json_arrayagg(json_object("type", "permPeriod", "id", "asd")) as JSON_DATA);
select * from v;
JSON_DATA
[{"type": "permPeriod", "id": "asd"}]
drop view v;
select json_arrayagg(a order by a asc) from (select 1 a union select 2 a) t;
json_arrayagg(a order by a asc)
[1,2]
select json_object('x', json_arrayagg(json_object('a', 1)));
json_object('x', json_arrayagg(json_object('a', 1)))
{"x": [{"a": 1}]}
#
# MDEV-22011: DISTINCT with JSON_ARRAYAGG gives wrong results
#
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
SELECT JSON_ARRAYAGG(a) FROM t1;
JSON_ARRAYAGG(a)
[1,2,3,1,2,3]
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
JSON_ARRAYAGG(DISTINCT a)
[1,2,3]
INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL);
SELECT JSON_ARRAYAGG(a) FROM t1;
JSON_ARRAYAGG(a)
[1,2,3,1,2,3,null,null]
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
JSON_ARRAYAGG(DISTINCT a)
[null,1,2,3]
DROP TABLE t1;
CREATE TABLE t1(a VARCHAR(10), b INT);
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
SELECT JSON_ARRAYAGG(a) FROM t1;
JSON_ARRAYAGG(a)
["1","2","3","1","2","3"]
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
JSON_ARRAYAGG(DISTINCT a)
["1","2","3"]
INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL);
SELECT JSON_ARRAYAGG(a) FROM t1;
JSON_ARRAYAGG(a)
["1","2","3","1","2","3",null,null]
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
JSON_ARRAYAGG(DISTINCT a)
[null,"1","2","3"]
DROP TABLE t1;
#
# MDEV-22840: JSON_ARRAYAGG gives wrong results with NULL values and ORDER by clause
#
CREATE TABLE t1(a VARCHAR(255));
INSERT INTO t1 VALUES ('red'),('blue');
SELECT JSON_ARRAYAGG(a) FROM t1;
JSON_ARRAYAGG(a)
["red","blue"]
SELECT JSON_ARRAYAGG(a ORDER BY a DESC) FROM t1;
JSON_ARRAYAGG(a ORDER BY a DESC)
["red","blue"]
SELECT JSON_ARRAYAGG(a ORDER BY a ASC) FROM t1;
JSON_ARRAYAGG(a ORDER BY a ASC)
["blue","red"]
INSERT INTO t1 VALUES (NULL);
SELECT JSON_ARRAYAGG(a) FROM t1;
JSON_ARRAYAGG(a)
["red","blue",null]
SELECT JSON_ARRAYAGG(a ORDER BY a DESC) FROM t1;
JSON_ARRAYAGG(a ORDER BY a DESC)
["red","blue",null]
SELECT JSON_ARRAYAGG(a ORDER BY a ASC) FROM t1;
JSON_ARRAYAGG(a ORDER BY a ASC)
[null,"blue","red"]
DROP TABLE t1;
set group_concat_max_len=64;
create table t1 (a varchar(254));
insert into t1 values (concat('x64-', repeat('a', 60)));
insert into t1 values (concat('x64-', repeat('b', 60)));
insert into t1 values (concat('x64-', repeat('c', 60)));
select json_arrayagg(a) from t1;
json_arrayagg(a)
["x64-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"]
Warnings:
Warning	1260	Row 1 was cut by JSON_ARRAYAGG()
drop table t1;
SET group_concat_max_len= default;
create table t1 (col1 json);
insert into t1 values('{"color":"red", "size":1}' );
insert into t1 values('{"color":"blue", "size":2}' );
select JSON_ARRAYAGG(col1) from t1;
JSON_ARRAYAGG(col1)
[{"color":"red", "size":1},{"color":"blue", "size":2}]
drop table t1;
#
# MDEV-23029: JSON_OBJECTAGG returns NULL when used together with GROUP BY
#
CREATE TABLE t1 (e INT, a VARCHAR(255), v VARCHAR(255));
INSERT INTO t1 VALUES (0, 'a1', '1') , (0, 'a2', '2') , (1, 'b1', '3');
EXPLAIN SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e, B.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
1	SIMPLE	B	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e, B.a;
e	JSON_OBJECTAGG(B.a, B.v)
0	{"a1":"1", "a1":"1", "a1":"1"}
0	{"a2":"2", "a2":"2", "a2":"2"}
1	{"b1":"3", "b1":"3", "b1":"3"}
CREATE VIEW v AS SELECT JSON_OBJECTAGG(a, e) FROM t1;
SELECT * FROM v;
JSON_OBJECTAGG(a, e)
{"a1":0, "a2":0, "b1":1}
DROP VIEW v;
DROP TABLE t1;
#
# MDEV-23004 When using GROUP BY with JSON_ARRAYAGG with joint table, the square brackets are not included.
#
CREATE TABLE t1(id int primary key, name varchar(50));
CREATE TABLE t2(id int, owner_id int);
INSERT INTO t1 VALUES (1, "name1"), (2, "name2"), (3, "name3");
INSERT INTO t2 VALUES (1, 1), (2, 1), (3, 2), (4, 3);
SELECT t1.id, JSON_ARRAYAGG(JSON_OBJECT('id',t2.id) ORDER BY t2.id) as materials
from t1 LEFT JOIN t2 on t1.id = t2.owner_id
GROUP BY t1.id ORDER BY id;
id	materials
1	["{\"id\": 1}","{\"id\": 2}"]
2	["{\"id\": 3}"]
3	["{\"id\": 4}"]
DROP TABLE t1;
DROP TABLE t2;
#
# MDEV-27018 IF and COALESCE lose "json" property
#
SELECT json_object('a', if(1, json_object('b', 'c'), json_object('e', 'f')));
json_object('a', if(1, json_object('b', 'c'), json_object('e', 'f')))
{"a": {"b": "c"}}
SELECT json_object('a', coalesce(json_object('b', 'c')));
json_object('a', coalesce(json_object('b', 'c')))
{"a": {"b": "c"}}
#
# End of 10.5 tests
#