Two weeks ago I announced new maintenance release of JSON UDFs: 0.2.1. It not only contains bug fixes, how you can expect from a maintenance release, but also contains improvements in build and test scripts.

First improvement is the easier way to build JSON UDFs on Windows. In the first version building on Windows was a pane: you had to build PCRE library, copy four files to directories where Visual Studio can find them and only then build JSON functions themselves. Now you can build this way too, but only if you really wish.

By default cmake will create appropriate makefiles for bundled PCRE sources and for JSON functions. Only command you need to prepare sources is:

"C:\Program Files (x86)\CMake 2.8\bin\cmake.exe" -G "Visual Studio 11 Win64" . -DMYSQL_DIR="/path/to/mysql/installation"   

And then you can build functions:

devenv my_json_udf.sln …

After I released maintenance release of JSON UDFs last week it is time to think about which features I should implement in upcoming major version.

Many users asked me about the possibility to explicitly specify if they want to append last element to a JSON array. This feature can be made for two functions: json_append and json_set.

I have four ideas of how to implement this. All have pros and contras.

1. 
   
2. Create new function called json_append_last which will work exactly like json_append, but it will add the element to the end of array. I.e., for JSON document {"colors": ["red", "green", "blue"]} json_append_last(doc, 'colors', 'orange') returns {"colors": ["red", "green", "blue", "orange"]}
   
   …

Today new version of JSON UDF functions: 0.2.1 was released. This is maintenance release which added no new functionality and only contains bug fixes. However, it also includes improvements for build ans test procedures. As usual, you can download source and binary packages at MySQL Labs. Binary packages were build for MySQL server 5.6.14. If you want to use other version of the server, you need to recompile functions.

What was changed? Let me quote the ChangeLog.

Functionality added or changed:

Added cmake option WITH_PCRE which alolows to specify if existent or bundled version of PCRE  should be used. Bundled is default on Windows. To compile with bundled version, run: "cmake . -DMYSQL_DIR=/path/to/mysql/dir -DWITH_PCRE=bundled", to turn bundled version off on Windows, run: "cmake . …

When I designed first version of JSON UDFs which was reviewed only internally, I let all functions to validate input and output JSON. But my colleagues told me to remove this functionality, because it makes such functions, as json_search, json_replace or json_contains_key deadly slow if they find the occurrence in the beginning of the long document. And first published version of JSON UDFs: 0.2.0 has not this functionality. What we expected is that users would call json_valid if they want to be 100% sure the document is valid.

But I was not surprised that some users expect JSON functions to work as it was in the first version: validate first, then process. For example, Ammon Sutherland writes: "json_set - according to the documentation a sort of 'INSERT... ON DUPLICATE KEY UPDATE' function which …

JSON UDFs got own category at MySQL Bugs Database: "Server: JSON UDF"

Use this category to post new bug reports and vote for existent.

I have blogged on using MariaDB Dynamic Columns already, and I hope this was useful and introduction. I have a few more things on this subject though, but one so far little known and used feature is the Client API for Dynamic Columns, see the MariaDB Knowledge Base for details. What this is all about is that Dynamic Columns were originally envisioned as a means of managing the "dynamic columns" used by Cassandra when using the MariaDB Cassandra Storage Engine. But as we realize, this is the server side of things (the Storage Engine) but there is a corresponding client library also, that is part of the MariaDB Client API.

As you have seen if you have read my previous blog on this subject, or whatever is written about MariaDB Dynamic Columns elsewhere, …

Yesterday Ulf Wendel created great blog post with deep analysis of JSON UDF functions which I presented at MySQL Connect at September, 21.

Ulf found few bugs in these functions, I reported them all at bugs.mysql.com. You can find numbers in my comment to his blog post. But he also raised concerns, which can not be considered pure bugs, rather feature requests, or even design especiallities.

* First concern, of course, is the documentation. Ulf writes: "Here’s what the README, the only documentation available apart from the *.c[omment]/*.h[elp] files".

I agree single README file is not enough, but this is still Labs project for which I can not abuse MySQL documentation team for making proper documentation for me. But you still can find more information, than single README file. And these are slides from …

MariaDB has a feature called Dynamic Columns which is not in MySQL, and this feature requires some explanation. It is used for example by the Cassandra Storage Engine, which is also unique to MariaDB, and as this is a schema-less database, which means we need a way to handle the fact that one one end MariaDB has a fixed set of columns defined by the schema, and on the other end, Cassandra provides any kind of attribute that the developer feels he wants to have for a particular "row" (which is a row in MariaDB but is not called a row in Cassandra).

But ignoring the Cassandra engine for a while, let's look at what us mere mortals, brought up on mothers milk, SQL and Relational Theory under the watching eyes of E.F. Codd, can use this for, and fact is that it can be quite useful. All in all, what Dynamic Columns provide here is a means of adding non-schema data to a row in a structured way, you know where you used to emulate an …

I have patched up a MariaDB version with JSON support, just for the fun of it. This is not the best version of MariaDB around, as I am not a MariaDB developer by any means, and although I have played with the MySQL and MariaDB sources before, I have never attemped to look like I know it in detail. So although this works, it's probably full of memory leaks, errors and bad code (my additions, that is).

That said, it actually works. Surprise! For a simple prototype, that is to show off a specific feature.

So, this is what I have: I grabbed MariaDB 10.0.2 sources and worked from there. To support the JSON specifics, I included the Jansson C-library for JSON. So far so good, then I wanted a JSON datatype, that was the first step. Adding a new datatype to MariaDB / MySQL is a pretty major undertaking though, so I decided to try something different, I decided to kidnap …

As you might know, I'm a big fan of JSON. One big reason is that I believe that JSON is closer to most developers view on data, whereas the Relational SQL based model is closer to what someone working with data itself or someone working with infrastructure. What I mean here is that neither view is wrong, but they are different.

So, given that, can we merge the Object JSON world with the relational model? Well, not JSON, but Hibernate does it quite well. This is one of my objects to the NoSQL world, that the datamodel is closely linked to the application at hand, and less so to data itself and to other applications. Stuff such as accounts, privileges, accounting data, orders and many other things are global, and are not specifically connected a specific application, but in many NoSQL applications, this is what it ends up being.

And there are not that many good solutions, how can I easily explore data in a NoSQL …