Johannes Schlüter

Recently there was a question about which clients were connected to a server being asked in the MySQL Community Slack. The relevant information is available from performance schema, as most connectors will send information about themselves when connecting:

select * from performance_schema.session_connect_attrs;
+----------------+-----------------+------------------------+------------------+
| PROCESSLIST_ID | ATTR_NAME       | ATTR_VALUE             | ORDINAL_POSITION |
+----------------+-----------------+------------------------+------------------+
|            130 | _pid            | 17412                  |                0 |
|            130 | _platform       | x86_64                 |                1 |
|            130 | _os             | Linux-5.4.0            |                2 |
|            130 | _source_host    | maniacmansion          |                3 |
|            130 | _client_name    | mysql-connector-nodejs |                4 |
|            130 | _client_version | 8.0.22                 |                5 |
|            130 | _client_license | GPL-2.0                |                6 |
|            130 | program_name    | mysqlx-shell           |                7 |
|            129 | _client_name    | libmysqlxclient        |                0 |
|            129 | _client_version | 8.0.21                 |                1 |
|            129 | _os             | Linux                  |                2 |
|            129 | _platform       | x86_64                 |                3 |
|            129 | _client_license | GPL                    |                4 |
|            129 | _pid            | 17257                  |                5 |
|            129 | program_name    | mysqlsh                |                6 |
|            131 | _pid            | 17510                  |                0 |
|            131 | _platform       | x86_64                 |                1 |
|            131 | _os             | Linux                  |                2 |
|            131 | _client_name    | libmysql               |                3 |
|            131 | os_user         | johannes               |                4 |
|            131 | _client_version | 8.0.22                 |                5 |
|            131 | program_name    | mysql                  |                6 |
+----------------+-----------------+------------------------+------------------+
22 rows in set (0.0027 sec)

Now this gives us quite some information, but the person asking wanted to have it presented in a nice, transposed way, where for isntance the _client_name and _client_version appeared in their own column. As I like MySQL's JSON features I decided to use those for doing this. (There are other ways, which in many cases might be better, see Window functions, CTEs, sub-selects, ... this here is only to show JSON things)

First feature I needed is JSON Aggregation. JSON aggregation works similar to GROUP_CONCAT or other aggregations: If we have a GROUP BY clause we can build a JSON object, taking one column as key and one column as value:

SELECT
    PROCESSLIST_ID,
   JSON_PRETTY(JSON_OBJECTAGG(ATTR_NAME, ATTR_VALUE)) attribs 
FROM performance_schema.session_connect_attrs
GROUP BY PROCESSLIST_ID;

| PROCESSLIST_ID | attribs                                       |

|            129 | {
                    "_os": "Linux",
                    "_pid": "17257",
                    "_platform": "x86_64",
                    "_client_name": "libmysqlxclient",
                    "program_name": "mysqlsh",
                    "_client_license": "GPL",
                    "_client_version": "8.0.21"
                   }
|            130 | {
                    "_os": "Linux-5.4.0",
                    "_pid": "17412",
                    "_platform": "x86_64",
                    "_client_name": "mysql-connector-nodejs",
                    "_source_host": "maniacmansion",
                    "program_name": "mysqlx-shell",
                    "_client_license": "GPL-2.0",
                    "_client_version": "8.0.22"
                   }
|            131 | {
                    "_os": "Linux",
                    "_pid": "17510",
                    "os_user": "johannes",
                    "_platform": "x86_64",
                    "_client_name": "libmysql",
                    "program_name": "mysql",
                    "_client_version": "8.0.22"
                   }             

With this all data is properly aggregated. Now we can use my friend JSON_TABLE to convert the JSON data back into a table:

SELECT
  PROCESSLIST_ID,
   i.* 
FROM
   (
       SELECT
           PROCESSLIST_ID,
           JSON_OBJECTAGG(ATTR_NAME, ATTR_VALUE) j
       FROM performance_schema.session_connect_attrs
       GROUP BY PROCESSLIST_ID
   ) s,
   JSON_TABLE(
       s.j, 
       '$' COLUMNS (
               client_name VARCHAR(100) PATH "$._client_name",
               client_version VARCHAR(100) PATH "$._client_version"
       )
   ) AS i ;
+----------------+------------------------+----------------+
| PROCESSLIST_ID | client_name            | client_version |
+----------------+------------------------+----------------+
|            129 | libmysqlxclient        | 8.0.21         |
|            130 | mysql-connector-nodejs | 8.0.22         |
|            131 | libmysql               | 8.0.22         |
+----------------+------------------------+----------------+

Any yay, we got a readable result about which active session is using which connector and can identify outdated ones. Of course we have all SQL things avaialble, thus adding a WHERE i.client_version != '8.0.22' would filter the result accordingly.

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.