Mysql WHERE query query is in json array
I set up a query where it picks a few things.
$stm = $db->query('SELECT startUser, forUser, percentage, time, taskTitle, taskDesc, color FROM admin_task WHERE forUser = "'.$queryanswer.'")');
But for the user it is like this in the database:
["demo"," user"]
How to check if forUser (json array above) has a demo? can i even check it out?
source to share
I think you can only achieve this in Mysql 5.7.
In version 5.7, you can do something like:
SELECT JSON_EXTRACT(json_field, '$.name');
and it will only fetch the name key from the json object.
Search for all elements tagged with "JavaScript":
SELECT * FROM `table` WHERE JSON_CONTAINS(json_field, '["JavaScript"]');
Find all elements with tags starting with 'Java':
SELECT * FROM `table` WHERE JSON_SEARCH(json_field, 'one', 'Java%') IS NOT NULL;
use "one" to find the first match or "all" to find all matches
You can extract the Twitter nickname using the JSON path:
SELECT name, json_field->"$.twitter" AS `twitter` FROM `user`;
You can also reference the JSON path in the WHERE clause to only return users with a Twitter account:
SELECT name, profile->"$.twitter" AS `twitter` FROM `user` WHERE profile->"$.twitter" IS NOT NULL;
You can do more things like:
-
Generating JSON Values
-
Normalizing, merging, and auto-updating JSON values
-
Finding and changing JSON values
-
Comparing and ordering JSON values
-
Aggregating JSON Values
for more information see: https://dev.mysql.com/doc/refman/5.7/en/json.html
source to share