A Little Noise

September 8, 2016

Aggregate JSON function in MySQL

Filed under: MySQL FAQ — snoyes @ 9:22 am

There is not yet an equivalent to GROUP_CONCAT that produces a JSON array. (There is in MySQL 8, but that’s not GA yet.) Until then, you can hack it together with string functions:

SELECT * FROM t;
+------+--------+
| id   | data   |
+------+--------+
|    1 | First  |
|    2 | Second |
+------+--------+

SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', id, 'value', data) SEPARATOR ', '), ']') AS j FROM t;
+-------------------------------------------------------------+
| j                                                           |
+-------------------------------------------------------------+
| [{"id": 1, "value": "First"}, {"id": 2, "value": "Second"}] |
+-------------------------------------------------------------+

Or you can use all JSON functions but hack the grouping:

SELECT j FROM (
       SELECT
         @c := @c + 1 AS c,
         @j := JSON_MERGE(@j, JSON_OBJECT('id', id, 'value', data)) AS j
       FROM t
       JOIN (SELECT @c := 0,  @j := JSON_ARRAY()) dt1
     ) dt2 ORDER BY c DESC LIMIT 1;
+-------------------------------------------------------------+
| j                                                           |
+-------------------------------------------------------------+
| [{"id": 1, "value": "First"}, {"id": 2, "value": "Second"}] |
+-------------------------------------------------------------+

1 Comment »

  1. This would be a good use for a query rewrite plugin. Select /*+JSON*/ …

    You would get a one row resultset with the results of the query encoded in one column with alias “JSON”.

    I don’t know if you can get the comments from the parser though. Also some clients strip comments.

    Comment by Justin Swanhart — September 11, 2016 @ 8:06 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress