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 GROUP_CONCAT(column_name) FROM table_name) IS NOT NULL,
  (SELECT JSON_ARRAY(GROUP_CONCAT(column_name)) FROM table_name),
  • 2
    is there a good cause, why you not simply update your database there should be not problem doing so.
    – nbk
    Mar 20, 2021 at 11:25
  • 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!
    – Vérace
    Mar 20, 2021 at 11:27
  • Besides JSON_ARRAYAGG returns [NULL] dbfiddle.uk/…
    – nbk
    Mar 20, 2021 at 11:55
  • Also, could you provide a fiddle (dbfiddle.uk) with your table structures and sample data?
    – Vérace
    Mar 20, 2021 at 16:29
  • 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 Mar 21, 2021 at 17:29

1 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 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
) AS json FROM foo;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.