Looking for more information on how to do PHP the right way? Check out PHP: The Right Way

Johannes Schlüter:
JSON Aggregation functions
Sep 16, 2016 @ 11:53:25

Johannes Schlüter has written up an article covering an interesting feature of the latest MySQL server release related to its JSON column handling: JSON Aggregation functions.

[The JSON datatype, functions and generated columns] combined allow building very powerful applications without committing to a fixed data structure on every part, but I was missing one thing a lot: Aggregation.

A topic I'm thinking a lot about is finding ways to select nested data. In MySQL we typically have two ways for this. Either one sends multiple queries to retrieve different nesting levels or one builds JOINs which tend to deliver repetitive responses.

He points out that one solution is the GROUP_CONCAT function, but the results of this can be difficult to work with and larger data may override the small memory space allowed. Instead he introduces two new functions in v8 to help with this nested data selection: JSON_ARRAYAGG and JSON_OBJECTAGG. He provides a brief example of the use of these two functions together to replace the "concat" handling from before.

tagged: mysql json function v8 release aggregation tutorial

Link: http://schlueters.de/blog/archives/183-MySQL-8.0-JSON-Aggregation-functions.html