Mysql query WHERE column is in json array

Question

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?

Answer 1

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

Answer 2

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

Answer 3

SELECT startUser, 
forUser, 
percentage, 
time, 
taskTitle, 
taskDesc, 
color 
FROM admin_task WHERE JSON_SEARCH(forUser -> '$[*]', 'one', 'demo');