I have setup an query where it selects multiple things.
$stm = $db->query('SELECT startUser, forUser, percentage, time, taskTitle, taskDesc, color FROM admin_task WHERE forUser = "'.$queryanswer.'")');
But for user is like this in the DB:
["demo"," user"]
How can I check if forUser (the json array above) has demo? can i even check this?
I think you can achieve this only in Mysql 5.7.
In version 5.7 you can do something like:
SELECT JSON_EXTRACT(json_field, '$.name');
and it will extract only the name key from json object.
Search all items with the 'JavaScript' tag:
SELECT * FROM `table` WHERE JSON_CONTAINS(json_field, '["JavaScript"]');
Find all items with tags starting '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 a JSON path:
SELECT name, json_field->"$.twitter" AS `twitter` FROM `user`;
You can also reference a 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:
Creating JSON Values
Normalization, Merging, and Autowrapping of JSON Values
Searching and Modifying JSON Values
Comparison and Ordering of JSON Values
Aggregation of JSON Values
for more info please refer to: https://dev.mysql.com/doc/refman/5.7/en/json.html
If it truly is a JSON field, in 5.7 you can do this:
SELECT
startUser,
forUser,
percentage,
time,
taskTitle,
taskDesc,
color
FROM admin_task
WHERE JSON_CONTAINS(forUser->'$[*]', JSON_ARRAY("somestring"))
SELECT startUser,
forUser,
percentage,
time,
taskTitle,
taskDesc,
color
FROM admin_task WHERE JSON_SEARCH(forUser -> '$[*]', 'one', 'demo');
mongodb