DROP DATABASE IF EXISTS json_arrayagg_db;
Warnings:
Note	1008	Can't drop database 'json_arrayagg_db'; database doesn't exist
CREATE DATABASE json_arrayagg_db;
USE json_arrayagg_db;
CREATE TABLE t1 (a INT, b INT)ENGINE=COLUMNSTORE;
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))ENGINE=COLUMNSTORE;
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)ENGINE=COLUMNSTORE;
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)
[1,1,1,1]	[0,0,0,0]
DROP TABLE t1;
#
# Aggregation of strings with quoted
#
CREATE TABLE t1 (a VARCHAR(80))ENGINE=COLUMNSTORE;
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))ENGINE=COLUMNSTORE;
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","!","Hello","World","This","Will","Work","!"]
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]	["This","Will","Work","!","This","Will","Work","!"]
[3,3]	NULL
#
# DISTINCT and LIMIT
#
SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1;
JSON_ARRAYAGG(b LIMIT 1)
["Hello","World","This","Will","Work","!","Hello","World","This","Will","Work","!"]
SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1;
JSON_ARRAYAGG(b LIMIT 2)
["Hello","World","This","Will","Work","!","Hello","World","This","Will","Work","!"]
SELECT JSON_ARRAYAGG(b LIMIT 1) FROM t1 GROUP BY b;
JSON_ARRAYAGG(b LIMIT 1)
NULL
["!","!"]
["Hello","Hello"]
["This","This"]
["Will","Will"]
["Work","Work"]
["World","World"]
SELECT JSON_ARRAYAGG(b LIMIT 2) FROM t1 GROUP BY a;
JSON_ARRAYAGG(b LIMIT 2)
NULL
["Hello","World","Hello","World"]
["This","Will","Work","!","This","Will","Work","!"]
SELECT JSON_ARRAYAGG(DISTINCT a) FROM t1;
JSON_ARRAYAGG(DISTINCT a)
[3,2,1]
SELECT JSON_ARRAYAGG(DISTINCT b) FROM t1;
JSON_ARRAYAGG(DISTINCT b)
["Will","World","Work","!","This","Hello"]
SELECT JSON_ARRAYAGG(DISTINCT a LIMIT 2) FROM t1;
JSON_ARRAYAGG(DISTINCT a LIMIT 2)
[3,2,1]
SELECT JSON_ARRAYAGG(DISTINCT b LIMIT 2) FROM t1;
JSON_ARRAYAGG(DISTINCT b LIMIT 2)
["Will","World","Work","!","This","Hello"]
#
# 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, "This"],[2, "Will"],[2, "Work"],[2, "!"],[2, "This"],[2, "Will"],[2, "Work"],[2, "!"]]
[[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": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "Work"},{"a": 2, "b": "!"},{"a": 2, "b": "This"},{"a": 2, "b": "Will"},{"a": 2, "b": "Work"},{"a": 2, "b": "!"}]
[{"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
#
#
DROP TABLE t1;
#
#
CREATE TABLE t1 (a INT)ENGINE=COLUMNSTORE;
SELECT JSON_ARRAYAGG(a) FROM t1;
JSON_ARRAYAGG(a)
NULL
DROP TABLE t1;
#
#
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}]}
#
#
CREATE TABLE t1(a INT, b INT)ENGINE=COLUMNSTORE;
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)
[3,2,1]
INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL);
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)
[3,2,1]
DROP TABLE t1;
CREATE TABLE t1(a VARCHAR(10), b INT)ENGINE=COLUMNSTORE;
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)
[3,2,1]
INSERT INTO t1 VALUES (NULL,NULL), (NULL,NULL);
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)
[3,2,1]
DROP TABLE t1;
#
#
CREATE TABLE t1(a VARCHAR(255))ENGINE=COLUMNSTORE;
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"]
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"]
DROP TABLE t1;
set group_concat_max_len=64;
create table t1 (a varchar(254))ENGINE=COLUMNSTORE;
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
drop table t1;
SET group_concat_max_len= default;
CREATE TABLE t1(id int, name varchar(50))ENGINE=COLUMNSTORE;
CREATE TABLE t2(id int, owner_id int)ENGINE=COLUMNSTORE;
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;
#
#
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"}}
#
#
CREATE TABLE t (a VARCHAR(8))ENGINE=COLUMNSTORE;
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;
DROP DATABASE IF EXISTS json_arrayagg_db;