Select from mysql database json objects

Question

I need to store a json string in database and to select entries depending on the values of json object. Is there any way to do this selection using mysql ? (i am using php, mysql 5.1)

Answer 1

A JSON object is basically a list of key/value pairs. If you know beforehand what these keys will be, then you can create a table with those key names as columns. You split up your JSON and insert each value into its respective column.

If you don't know beforehand what the keys in the JSON will be, then you could create a table with object ID, key and value columns. You store each key/value pair in its own row. It can be harder to write queries for this, though, but that depends on what you want to query.

Answer 2

MySQL doesn't have a native JSON type, so it won't know how to query against a JSON value. If you really need to be able to do this, you'll either need to normalize/serialize the JSON data into standard MySQL data types (e.g., split the value across multiple columns and rows), or use a database that can actually understand JSON (e.g., CouchDB or MongoDB).

Answer 3

With php 5.2 you could use the php JSON library. You might look into using regular expressions, either in php or in MySQL (http://dev.mysql.com/doc/refman/5.1/en/regexp.html) to extract the values elegantly.

Answer 4

Why don't you use a document database instead of MySQL? They were created just for this purpose. Have a look at MongoDB.

If MongoDB is not an option, you could also dump a relevant subset of the data in a Lucene index (see Zend_Search_Lucene) and search the Lucene index to retrieve the record ID's of the objects that match your search criteria (and retrieve these from MySQL afterwards).