How to get a JSON array without JSON_ARRAYAGG in MySQL

Question

I can't use a JSON_ARRAYAGG function introduced in MySQL 5.7.22 because I'm using MySQL 5.7.20. I would like to get a JSON array of a column values like I would get if I could use the function: SELECT JSON_ARRAYAGG(column_name) FROM table_name;

I got to this piece of SQL: SELECT JSON_ARRAY(GROUP_CONCAT(column_name)) FROM table_name; which works almost like JSON_ARRAYAGG. The problem is when there is no rows it returns [null] instead of null.

What is the most efficient way of replacing a JSON_ARRAYAGG function with some equivalent? I would like to avoid something like this:

SELECT IF(
  (SELECT GROUP_CONCAT(column_name) FROM table_name) IS NOT NULL,
  (SELECT JSON_ARRAY(GROUP_CONCAT(column_name)) FROM table_name),
  NULL
);

Answer 1

Finally I've found a solution. I create a string array using CONCAT and GROUP_CONCAT functions and then cast it to JSON.

SELECT JSON_REPLACE(
    data,
    '$.numbers',
    IF(
      (
        SELECT COUNT(number) FROM numbers
        WHERE JSON_CONTAINS(data, CAST(numbers.id AS CHAR), '$.numbers') = 1
      ) > 0,
      (
        SELECT CAST(CONCAT('[', GROUP_CONCAT(CONCAT('"', number, '"')), ']') AS JSON) FROM numbers
        WHERE JSON_CONTAINS(data, CAST(numbers.id AS CHAR), '$.numbers') = 1
      ),
      NULL
    )
) AS json FROM foo;