I am new to mariadb, and I was using MySQL to develop something, after switching to mariadb it is not working, the error part I find is located at 'JSON_ARRAYAGG', in previously I was using JSON_ARRAYAGG to combine result as array by Group by as below
SELECT column1, JSON_ARRAYAGG(column2) AS column2 FROM table GROUP BY column1;
Transformation
column1 column2 column1 column2
1 a 1 ['a','b']
1 b 2 ['cc','dd']
2 cc ---> 3 ['e']
2 dd
3 e
Is there a way to do so in mariadb? Thank you in advance!
Creating an aggregate function in MariaDB v10.3.3 :
DELIMITER //
DROP FUNCTION IF EXISTS JSON_ARRAYAGG//
CREATE AGGREGATE FUNCTION IF NOT EXISTS JSON_ARRAYAGG(next_value TEXT) RETURNS TEXT
BEGIN
DECLARE json TEXT DEFAULT '[""]';
DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN json_remove(json, '$[0]');
LOOP
FETCH GROUP NEXT ROW;
SET json = json_array_append(json, '$', next_value);
END LOOP;
END //
DELIMITER ;
And use it like this:
SELECT column1, JSON_ARRAYAGG(column2) AS column2 FROM table GROUP BY column1;
will work.
You can emulate it by wrapping the GROUP_CONCAT
with brackets using CONCAT
.
SELECT column1, CONCAT('[', GROUP_CONCAT(column2), ']') AS column2 FROM table GROUP BY column1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With