MySQL JSON aggregatable functions

Question

MySQL since version 5.7 has some fundamental JSON support, however I've been looking through the documentation but couldn't find anything JSON aggregatable functions.

Are there any JSON aggregate functions in MySQL 5.7+?

For example if you have query:

SELECT id, GROUP_CONCAT(name) FROM given_names GROUP BY id

You'd get a result like:

id | name
1  | Jon,Smith
2  | Hubert,Blaine,Wolfeschlegelsteinhausenbergerdorff
....

Is there an equivalent way to get a JSON array with?

My concern is with properly escaping strings because when you use GROUP_CONCAT if the string contains the separator it is near impossible to differentiate the parts afterwards.

The JSON functions seem so antithetical to the idea of relational data, I just can't figure out why they're there. What's wrong with spitting sql out to json_encode? — Strawberry
– Strawberry, Commented Jan 27, 2017 at 13:48
@Strawberry I'm not arguing for JSON functions in a relational database, although I think MySQL is just trying to invade the NoSQL landscape by becoming "relational and then some" but I'm just saying, since JSON is already in there, why not JSON aggregation. The reason I ask is that I could eliminate a lot of rows with replicated data if such a thing existed (in this example it would just be id but there could be more). — apokryfos
– apokryfos, Commented Jan 27, 2017 at 14:21

apokryfos · Accepted Answer · 2019-08-28 21:25:19Z

2

As of MySQL 5.7.22 it is possible using either JSON_ARRAYAGG and JSON_OBJECTAGG. Examples taken from the documentation:

JSON_ARRAYAGG

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+--------+
| o_id | attribute | value  |
+------+-----------+--------+
|    2 | color     | red    |
|    2 | fabric    | silk   |
|    3 | color     | green  |
|    3 | shape     | square |
+------+-----------+--------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes 
       FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.00 sec)

JSON_OBJECTAGG

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+--------+
| o_id | attribute | value  |
+------+-----------+--------+
|    2 | color     | red    |
|    2 | fabric    | silk   |
|    3 | color     | green  |
|    3 | shape     | square |
+------+-----------+--------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
       FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value)      |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

edited Aug 28, 2019 at 21:25

answered Aug 28, 2019 at 16:15

apokryfos

40.9k11 gold badges85 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Karwin Over a year ago

Those functions are also in MySQL 5.7.22. Cf. dev.mysql.com/doc/refman/5.7/en/…

Collectives™ on Stack Overflow

MySQL JSON aggregatable functions

1 Answer 1

JSON_ARRAYAGG

JSON_OBJECTAGG

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

JSON_ARRAYAGG

JSON_OBJECTAGG

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related