[MCOL-5227] Implement JSON_ARRAYAGG Created: 2022-09-21  Updated: 2023-02-06  Resolved: 2022-12-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 22.08.1
Fix Version/s: 22.08.8

Type: New Feature Priority: Major
Reporter: Roman Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MCOL-5224 JSON_ARRAYAGG join failing Closed
is duplicated by MCOL-5224 JSON_ARRAYAGG join failing Closed
PartOf
includes MCOL-785 Implement DISTRIBUTED JSON functions Closed
Assigned for Review: Roman Roman

 Description   

JSON_ARRAYAGG is the function which output and arguments are very similar to GROUP_CONCAT.
The suggested solution would be to analyze arguments of GROUP_CONCAT and replace JSON_ARRAYAGG with GROUP_CONCAT if possible. One can optionally mask some of the original GROUP_CONCAT arguments that are not used by JSON_ARRAYAGG.



 Comments   
Comment by Roman [ 2022-10-31 ]

4QA There is one of three JSON_* functions left. For more information on how to test this plz take a look at MDB's JSON_ARRAYAGG page.

Comment by Daniel Lee (Inactive) [ 2022-12-05 ]

Build verified: 23.02

engine: a1d89d8f311d8187d3357536a64d77ef6f9c2b8e
server: bf7f6987c8fb7ceda9ae048ada129d11798d4392
buildNo: 6151

MariaDB example:

MariaDB [mytest]> CREATE TABLE t1 (a INT, b INT) engine=columnstore;
 
Query OK, 0 rows affected (0.141 sec)
 
 
 
MariaDB [mytest]> INSERT INTO t1 VALUES (1, 1),(2, 1), (1, 1),(2, 1), (3, 2),(2, 2),(2, 2),(2, 2);
 
Query OK, 8 rows affected (0.158 sec)
 
Records: 8  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [mytest]> 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] |
 
+-------------------+-------------------+
 
1 row in set (0.028 sec)
 
 
 
MariaDB [mytest]> 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]        |
 
+------------------+------------------+
 
2 rows in set (0.013 sec)
 
 
 
Oracle example:

MariaDB [mytest]> CREATE TABLE id_table (id int) engine=columnstore;
Query OK, 0 rows affected (0.132 sec)

MariaDB [mytest]> INSERT INTO id_table VALUES(624);
Query OK, 1 row affected (0.099 sec)

MariaDB [mytest]> INSERT INTO id_table VALUES(null);
Query OK, 1 row affected (0.057 sec)

MariaDB [mytest]> INSERT INTO id_table VALUES(925);
Query OK, 1 row affected (0.055 sec)

MariaDB [mytest]> INSERT INTO id_table VALUES(585);
Query OK, 1 row affected (0.061 sec)

MariaDB [mytest]> SELECT JSON_ARRAYAGG(id ORDER BY id) ID_NUMBERS FROM id_table;
---------------

ID_NUMBERS

---------------

[585,624,925]

---------------
1 row in set (0.031 sec)

 

Generated at Thu Feb 08 02:56:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.