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
);

• nbk: is there a good cause, why you not simply update your database there should be not problem doing so.
• Vérace: Hi/Szia, and welcome to forum! As @nbk says, an in-place upgrade should be painless - it's only when changing major versions (i.e. 5.7 - 8.xx), that you have to backup/restore!
• nbk: Besides JSON_ARRAYAGG returns [NULL] dbfiddle.uk/…
• Vérace: Also, could you provide a fiddle (dbfiddle.uk) with your table structures and sample data?
• Szymon Czembor: I don't have a permission to do such upgrade. @nbk In your example a table is not empty. JSON_ARRAYAGG returns null if there are no rows in a table. Here is an example of what I'm trying to do: db-fiddle.com/f/kSK45W5t3WEaMAWiAqrrCw/0

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;