英文:
How to retrieve matched data from mysql table text datatype?
问题
我有一个查询要构建，以从具有JSON格式文本值的表中获取匹配的数据。
CREATE TABLE `post_sender_detail` (
`version_id` int(10) unsigned NOT NULL,
`subject` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`tags` text DEFAULT NULL COMMENT '标签详细信息的JSON',
PRIMARY KEY (`version_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
存储的值如下：
| version_id | subject | tags |
|---|---|---|
| 83 | test sftp new series | {"1":"Two2","2":"Three 3","7":"one1"} |
| 82 | This is testing of seedlist | {"31":"one1","32":"Two2"} |
| 81 | non-amp test 001 | NULL |
| 80 | non-amp test 001 | NULL |
我想要一个类似于以下的查询：
select * from post_sender_detail where JSON_EXTRACT(tags, '$.31') IS NOT NULL;
并获得以下结果：
| version_id | subject | tags |
|---|---|---|
| 82 | This is testing of seedlist | {"31":"one1","32":"Two2"} |
我的数据库版本：
| Variable_name | Value |
|---|---|
| innodb_version | 10.4.7 |
| protocol_version | 107 |
| version | 10.4.7-MariaDB-1:10.4.7+maria~bionic-log7 |
我尝试过我的方法，但没有找到获取所需结果的方法。你们可以在这里提供帮助吗，并告诉我是否有任何方法可以从MYSQL查询中获取它吗？
谢谢。
英文:
I have one query to build to get matched data from table having value as text in json format.
CREATE TABLE `post_sender_detail` (
`version_id` int(10) unsigned NOT NULL,
`subject` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`tags` text DEFAULT NULL COMMENT 'JSON of tags details',
PRIMARY KEY (`version_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Values stored like this
| version_id | subject | tags |
|---|---|---|
| 83 | test sftp new series | {"1":"Two2","2":"Three 3","7":"one1"} |
| 82 | This is testing of seedlist | {"31":"one1","32":"Two2"} |
| 81 | non-amp test 001 | NULL |
| 80 | non-amp test 001 | NULL |
I want some query like <br>
select * from post_sender_detail where tags match the key as 31;
And get the result like
| version_id | subject | tags |
|---|---|---|
| 82 | This is testing of seedlist | {"31":"one1","32":"Two2"} |
<br> <br>
My DB version
| Variable_name | Value |
|---|---|
| innodb_version | 10.4.7 |
| protocol_version | 107 |
| version | 10.4.7-MariaDB-1:10.4.7+maria~bionic-log7 |
<br><br>I tried my way but not getting something to get the desired result. Can you guys help here and let me know if there is any way to get it from MYSQL query?
<br><br>
Thanks
答案1
得分: 1
根据@akina的评论，您可以使用JSON_KEYS将JSON中的键返回为数组，并使用MEMBER OF检查一个值是否是数组的元素：
SELECT *
FROM post_sender_detail
WHERE '31' MEMBER OF (JSON_KEYS(CAST(tags AS JSON)))
由于您使用的是mariadb 10.4.7，您可以简单地执行以下操作：
SELECT *
FROM post_sender_detail
WHERE tags LIKE '%"31":%'
英文:
As mentioned in comments by @akina you can use JSON_KEYS to returns the keys from a JSON as array, and MEMBER OF to check if a value is an element of an array :
SELECT *
FROM post_sender_detail
WHERE '31' MEMBER OF( JSON_KEYS(CAST(tags AS JSON)))
Since you are using mariadb 10.4.7 then you can simply do :
select *
from post_sender_detail
where tags like '%"31":%'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论