Johannes Schlüter

In MySQL 5.7 the new JSON support had been added. The JSON support consists out of three features:

• The JSON Datatype
• A set of JSON functions
• Generated columns

All three features 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. A tool we got for a while is GROUP_CONCAT. With GROUP_CONCAT we can get some of our nested information:

mysql> SELECT blog.*, 
              GROUP_CONCAT(blog_comment.id) AS comments
         FROM blog
         JOIN blog_comment ON blog.id = blog_comment.blog_id
     GROUP BY blog_id;
+----+-------------+----------------------+----------+
| id | title       | body                 | comments |
+----+-------------+----------------------+----------+
|  1 | Hello World | This is my new Blog! | 1,2      |
|  2 | spam trap   | spam goes here       | 3        |
+----+-------------+----------------------+----------+
2 rows in set (0.07 sec)

This gives us the ids for all comments to a specific blog post. But it's not really nice to process. To improve this we can try to abuse GROUP_CONCAT like this:

mysql> SELECT blog.*, 
              CONCAT('[', GROUP_CONCAT( JSON_OBJECT("author", blog_comment.author, "body", blog_comment.body)), ']') AS comments
         FROM blog
         JOIN blog_comment ON blog.id = blog_comment.blog_id
     GROUP BY blog_id;
+----+-------------+----------------------+--------------------------------------------------------------------------------------------------------+
| id | title       | body                 | comments                                                                                               |
+----+-------------+----------------------+--------------------------------------------------------------------------------------------------------+
|  1 | Hello World | This is my new Blog! | [{"body": "Great blog!", "author": "Your Friend"},{"body": "Great new blog", "author": "Best Friend"}] |
|  2 | spam trap   | spam goes here       | [{"body": "Buy these pills to enlarge your blog", "author": "Visitor"}]                                |
+----+-------------+----------------------+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

But this has multiple issues, not only is it kind of ugly and error-prone to build our JSON string manually using CONCAT() like this, but also GROUP_CONCAT uses an internal buffer with a default size of only 1024, which, while configurable, can easily be too little for our use case.

Now in the new development milestone release for MySQL 8.0 we can see two new aggregation functions: JSON_ARRAYAGG() and JSON_OBJECTAGG() which build proper JSON arrays or objects without any need for a hack:

mysql> SELECT blog.*,
              JSON_ARRAYAGG( JSON_OBJECT("author", blog_comment.author, "body", blog_comment.body) ) AS comments
         FROM blog
         JOIN blog_comment ON blog.id = blog_comment.blog_id
     GROUP BY blog_id;           
+----+-------------+----------------------+--------------------------------------------------------------------------------------------------------+
| id | title       | body                 | comments                                                                                               |
+----+-------------+----------------------+--------------------------------------------------------------------------------------------------------+
|  1 | Hello World | This is my new Blog! | [{"body": "Great blog!", "author": "Your Friend"},{"body": "Great new blog", "author": "Best Friend"}] |
|  2 | spam trap   | spam goes here       | [{"body": "Buy these pills to enlarge your blog", "author": "Visitor"}]                                |
+----+-------------+----------------------+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

This is now quite nice and by using the proper aggregation function we can be sure that our result will always be valid JSON, which allows further easy processing.

In a few previous blog posts I was already talking about changes in PHP's trunk, like some BC break or some new features, time to continue:

JSON is the modern data format used in "AJAX" applications. As the leading language for the Web PHP course has support for handling JSON data: You can pass any data to json_encode() and the function will create a JSON representation of this data:

<?php
echo json_encode(array(1,2,3,4));
?>

[1,2,3,4]

This also works for objects:

<?php
$o = new stdclass;
$o->a = 42;
echo json_encode($o);
?>

{"a":42}

Wonderful. But the world isn't that easy. For many PHP objects the JSON-representation of the data is a bit more complex.for instance what about private properties or maybe you want to calculate some inner values? - In PHP 5.3 you were on your own. but thanks to Sara there's hope in sight: the new interface JsonSerializable. Classes implementing this interface have to provide a method jsonSerialize() which will be called by json_encode() and has to return a JSON-compatible representation of the data by doing whatever you want. So let's take a look:

<?php
class JsonTest implements JsonSerializable {
    private $a, $b;

    public function __construct($a, $b) {
        $this->a = $a;
        $this->b = $b;
    }

    public function jsonSerialize() {
        return $this->a + $this->b;
    }
}

echo json_encode(new JsonTest(23, 42));
?>

65

Now this example in itself is of course useless, but let's create a bigger structure, which includes a few of these objects:

<?php
$data = array(
    new stdClass();
    new JsonTest(1,2),
    new JsonTest(3,4),
    array(5,6)
);
echo json_encode($data);
?>

[{},3,7,[5,6]]

Of course you'd usually have more complex serialization logic, but that's left to you.

Now almost certainly somebody will ask "and what about the other way round?" - The only answer there is: Sorry there we can't do much. JSON doesn't encode and meta-information so our generic parser in json_decode() can't do anything special. But anyways: The new interface will certainly be useful.

At last a short disclaimer: PHP trunk is a development tree. Features in their may be changed at anytime and might not be released. Feedback is welcome.