June 8, 2011
MYSQL SELECT and JSON_ENCODE
Question by Gabriel Santos
I need to select a key from a json_encodED array from mysql..
SELECT * FROM json_field_table WHERE {var from JSON encoded array} = {expected value}
or something..
How I can do this?
PS.: Poor English, I know..
Answer by Marc B
You’d have to use substring matching. MySQL doesn’t have anything to deal with JSON data and treats it like it does any other piece of random text.
SELECT ... WHERE the_json_field LIKE '%"var":"value"%';
Answer by Starx
Well, Gabriel, despite the nature of your question. I am supposing, your problem might be, you need to read a JSON value and based on that values, you need to retrieve the record set from the table. If this is the case, here is your solution.
// A sample json string
$json = '{ "field1": "value1", "field2": "value2" }';
// Here we will convert the json string into readable assocative array
$json_array = json_decode($json,true);
//Next we will use it on a query
$query = "SELECT * json_field_table WHERE `".$json_array['field1']."` = 'Some Value' ";
//Execute the query
$result = mysql_query($query);
