Entries tagged as json
Related tags
ajax amber road brendand gregg coding dtrace hardware php php oo php.next solaris storage sun microsystems web 2.0 youtube .net anniversary array assembler banshee BarCamp bazaar berkeley db birthday boredom Bryan Cantrill c# christmas comments conferences cvs database db debugging delphi development dsp DTrace ego english events exchange firefox frustration fun gecko german git google goto gsoc gsoc08 gsoc09 improvements ipc08 iterator java javafx mysql acquisation buffer closures cta froscon froscon10 ide information_schema ipc ipc10 job launchpad memcache memcached memcche mysql 8.0 mysql analytics mysql cluster mysql fabric mysql js mysql json mysql plugins mysql proxy mysql storage engine mysql56 mysqlde mysqli mysqlnd mysqlnd plugins mysqlnd_ms apc api design barcamp bc beer garden berlin best practice charsets commits computer science 101 data center easter encoding exception file upload froscon08 guidlines oop performance php 4 php 5.4 php coding php references php.iterator planet php command line opensource pdo php 5 php 6 php extensions php interactive php qa php releases php session php shell php53 processes sqlite sqlite3 type hints unicodeSep 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.
Sep 15: MySQL 8.0: JSON Aggregation functions

In MySQL 5.7 the new JSON support had been added. The JSON support consists out of three features:
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.
Jun 3: Jason, let me help you!

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.