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..
3 Answers
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"%';
Comments
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);
1 Comment
Starx
Can't you retrieve the row and then check it later on. What you are trying to do, will create a very inefficient solution. There might be better way to do, what you are trying to do. But, if you insist on going with your idea, then better go with @Marc B's solution.
with numbers (integers) you can filter out values, with alphanumeric strings is more complicated as the value stored is "jsonencoded"
check my answer to 17955206
m Brasiliam and dont know how to create an question.. is complex to me.