MySQL Varchar – How to Use Numeric Operators with Varchar Values

MySQL

Here detail my table :

id | meta

01 | {"key1":"value1","data":"10"}

02 | {"key1":"valuex","data":"15","key3":"y"}

03 | {"data":"20","key3":"x"}

*id is primary key
*meta is field that store json data

At this table, each record having category and each category having difference data

The problem is, can we select record that only having data greater than 15 ?

That record with id 02 and 03

Best Answer

The short answer is that you shouldn't. It is almost certainly possible using the various string manipulation functions to find and extract the right value then cast it to a numeric for comparison, but that would be massively inefficient, difficult to get right (you have to account for the keys you are looking for appearing as partial matches within other key names or values for instance, you would be trying to write a full JSON parser in SQL string functions), and a pain to maintain if your requirements change over time.

If you genuinely need to operate in this manner, consider using a document-store database (like many of the noSQL options that are out there), particularly one designed around JSON as the storage format (CouchDB, Mongo, ...) as that seems to be the format you are using.

Otherwise if you want (or have) to use a relational database then store your data in a relational manner. Of course if you have little control of what keys will be in the data then you migth be forced to use the key/value pair structure (also called the EAV (Entity-Attribute-Value) model and often considered an anti-pattern also) so this may not be optimal depending on your application.

Some relational databases are starting to offer direct support for indexing properties found in arbitrary JSON strings (see http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/ for notes on the support recently added to postgres) allowing you to mix both worlds, but I can't comment on the relative efficiency of this compared to a pure relational model or a pure document store based model as I've never used such features myself yet (and anyway, mySQL isn't one of DBMSs that support it).

Related Question