Here's the problem:
We have a MySQL database in which we store JSON strings. Until now this was no problem, but suddenly our client requested we do search/order/other operations with these objects. Since the projects is at a fairly late state, going to Mongo is not a choice the team leader wants to make, so I'm asking is there a quick way to use these JSON strings?
JSON as intention
There is no way to work with JSON in MySQL. Some DBMS may support JSON, but that's out of the question, and, besides, any sort of such "support" is only about performing some JSON-specific operations, but not about modelling your architecture (and those two things are completely different) More, in full sense, model concept (i.e. relations) for MySQL is different than JSON is: as a Relational DBMS, it follows relational data model, and JSON is completely different format. You will store it as plain string value, thus, impossible to do anything with it in some other way, if not use string functions. So even working with JSON you will do that not inside relational model, thus, it won't be possible to maintain relational features, such as referential integrity, for instance.
Options to resolve
You have several options:
CREATE FUNCTION statement). This will require specific skills and time, of course. The bad things: bugs. Even if you'll be able to create those functions faster than re-structure your architecture or migrate to Mongo, you will never be certain of quality of those functions. There's no way to test that code natively. However, I may give hint for the case of user-land functions - you may use mysql-unit to test your stored code, if your MySQL is 5.6 or higher (well, I've written this tool, but.. it may contain bugs too)"Standard" functions
Finally, if you're running MySQL 5.7, then there may be a ray of hope with pre-release JSON functions - so, you may try to use alfa-version of JSON functionality, which currently exists for MySQL 5.7. But I would not (strongly) recommend to use that in real project since those functions are neither well-tested nor complete at all. But, to install those function, you'll need to download corresponding package, then plug them into your server, like:
CREATE FUNCTION json_append RETURNS string SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_valid RETURNS integer SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_extract RETURNS string SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_replace RETURNS string SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_remove RETURNS string SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_set RETURNS string SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_merge RETURNS string SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_search RETURNS string SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_contains_key RETURNS integer SONAME 'libmy_json_udf.so';
And after that you'll be able to try them.
between separate entities inside JSON entries? No. Do you really can maintain referential integrity with JSON data? No. I may continue, but concept was shown: if you can work with JSON structures, it doesn't mean they are aligned with the model itself. But thanks for dv explain.If you are not using any MySQL specific functionality, you could switch over relatively painlessly to PostgreSQL, and utilize its JSONB data type.
If you look at the official wiki page regarding its inclusion in PostgreSQL 9.4, then you'll see that with this, you are able to store raw JSON and query against it. As an example, given this JSON structure (I replaced the PostgreSQL-specific column types with standard types, except for the JSONB type:
table booksdata (
title text not null,
isbn text not null primary key,
pubinfo jsonb not null
)
You can then query against it to find the average cost of all books from a single publisher like this:
SELECT avg((pubinfo #>> '{"cost"}')::NUMERIC)
FROM booksdata
WHERE pubinfo @> '{ "publisher" : "It Books" }';
MySQL5.7 now supports a JSON data type - https://dev.mysql.com/doc/refman/5.7/en/json.html
Seems they didn't want to get left out of the NoSQL conversation.
mysql> SELECT JSON_ARRAY('a', 1, NOW());
