1

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
);
5
  • 2
    is there a good cause, why you not simply update your database there should be not problem doing so.
    – nbk
    Mar 20 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! Mar 20 at 11:27
  • Besides JSON_ARRAYAGG returns [NULL] dbfiddle.uk/…
    – nbk
    Mar 20 at 11:55
  • Also, could you provide a fiddle (dbfiddle.uk) with your table structures and sample data? Mar 20 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 at 17:29
2

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;
1
  • Thanks this really helps a lot!
    – nichen
    Jun 22 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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