CODEWITHSUNDEEP
phpmysqljson
Gabriel Santos
Gabriel Santos

Reputation: 4974

MYSQL SELECT and JSON_ENCODE

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..

Upvotes: 1

Views: 3728

Answers (3)

alo Malbarez
alo Malbarez

Reputation: 374

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

Upvotes: 0

Starx
Starx

Reputation: 78981

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

Upvotes: 0

Marc B
Marc B

Reputation: 360662

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

Upvotes: 4

Related Questions