Is there any way to use json objects in SQL

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?

回答1:

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:

Migrate to Postgree SQL as it has extended support for json, since version 9.4 it's jsonb and it's even faster. This might be the best option since it's RDBMS and therefore the migration won't be that hard like for truly document-oriented DBMS.
Or migrate to Mongo now (if that's your intention), before it's too late. Take in account, that Mongo is completely different thing than RDBMS, it's document-oriented. I guess this is the best option both for your project and for your client (and your task would be to explain that)
Change entire architecture so you won't store JSON objects and will work with normalized (in terms of relation DB) entities. This means - yes, entire refactoring of all code, changing all relations etc. In real situation it's just theoretical option, you won't be given neither time nor money for that.
Implement your own JSON library for MySQL. Is it hard? Depends of what you will do with your JSON, but JSON is public format, so you will know what to do, at least. You may do it either as UDF or at user-land (so with 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.

回答2:

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" }';

回答3:

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());