MySQL查询WHERE列在json数组中 [英] Mysql query WHERE column is in json array

问题描述

我已经设置了一个查询，可以在其中选择多项内容.

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

如何检查forUser(上面的json数组)是否具有演示?我什至可以检查一下吗?

How can I check if forUser (the json array above) has demo? can i even check this?

推荐答案

我认为您只能在Mysql 5.7中实现.

I think you can achieve this only in Mysql 5.7.

在5.7版中，您可以执行以下操作:

In version 5.7 you can do something like:

SELECT JSON_EXTRACT(json_field, '$.name');

，它将仅从json对象中提取名称键.

and it will extract only the name key from json object.

搜索带有"JavaScript"标记的所有项目:

SELECT * FROM `table` WHERE JSON_CONTAINS(json_field, '["JavaScript"]');

查找所有带有以"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

您可以使用JSON路径提取Twitter昵称:

SELECT name, json_field->"$.twitter" AS `twitter` FROM `user`;

您还可以在WHERE子句中引用JSON路径，以仅返回具有Twitter帐户的用户:

SELECT name, profile->"$.twitter" AS `twitter` FROM `user` WHERE profile->"$.twitter" IS NOT NULL;

您可以做更多的事情，例如:

You can do more things like:

• 创建JSON值

• Creating JSON Values

JSON值的规范化，合并和自动包装

Normalization, Merging, and Autowrapping of JSON Values

搜索和修改JSON值

JSON值的比较和排序

Comparison and Ordering of JSON Values

JSON值的聚合

有关更多信息，请参考: https://dev.mysql .com/doc/refman/5.7/en/json.html

for more info please refer to: https://dev.mysql.com/doc/refman/5.7/en/json.html

这篇关于MySQL查询WHERE列在json数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！