Using JSON features to restructure results

Using JSON features to restructure results

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.