Entries tagged as mysql js
Related tags
json ajax coding mysql mysql 8.0 mysql json php php oo php.next acquisation buffer christmas closures conferences cta debugging development dtrace froscon froscon10 fun google goto gsoc gsoc09 ide improvements information_schema ipc ipc08 ipc10 iterator job launchpad memcache memcached memcche mysql analytics mysql cluster mysql fabric mysql plugins mysql proxy mysql storage engine mysql56 mysqlde mysqli mysqlnd mysqlnd plugins mysqlnd_msSep 17: 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.