json - 将 JSON 数组上的 MariaDB 表连接到文本字段
问题描述
表crons
有一个名为的 JSON 字段surveys
,其中包含一个 UUID 的 JSON 数组,例如:
[
"65f42c322ea442099612cad251be1dac",
"43788fe207b143dc9eac4da38c036af6"
]
表1(计划):
CREATE TABLE "crons" (
"id" int NOT NULL AUTO_INCREMENT,
"depts" longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
"surveys" longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
PRIMARY KEY ("id"),
CONSTRAINT "CONSTRAINT_1" CHECK (((`depts` is null) or json_valid(`depts`))),
CONSTRAINT "CONSTRAINT_2" CHECK (((`surveys` is null) or json_valid(`surveys`))),
CONSTRAINT "crons_chk_1" CHECK (json_valid(`depts`)),
CONSTRAINT "crons_chk_2" CHECK (json_valid(`surveys`))
)
表2(调查):
表surveys
包含survey_uuid
作为 varchar 字符串的字段。
CREATE TABLE "surveys" (
"id" int NOT NULL AUTO_INCREMENT,
"survey_uuid" varchar(32) NOT NULL,
PRIMARY KEY ("id"),
UNIQUE KEY "surveys_survey_uuid_uindex" ("survey_uuid"),
CONSTRAINT "survey_json" CHECK (((`survey_json` is null) or json_valid(`survey_json`)))
)
我需要返回surveys.survey_uuid
(varchar) 包含在crons.surveys
(JSON array)中的所有行
试图:
SELECT *
FROM crons c
JOIN
surveys s
ON (JSON_SEARCH(c.surveys, 'one', s.survey_uuid) IS NOT NULL)
WHERE start_date < NOW()
AND end_date > NOW()
AND JSON_SEARCH(c.depts, 'one', 'sales') IS NOT NULL
注意:MariaDB 将 JSON 存储为长文本,但所有 JSON 函数都有效。我已经验证这JSON_SEARCH(depts, 'one', 'sales') IS NOT NULL
是搜索 JSON 字段数组的有效方法。
在 MariaDB 中你不能做的一件事是,CAST(something AS JSON)
但你可以CAST(jsonfield AS char)
解决方案
如果要搜索 中surveys
至少有一个匹配行的所有行crons
,则应使用以下 SQL:
SELECT s.id, s.survey_uuid, c.id, c.depts, c.surveys
FROM surveys s JOIN crons c
ON (JSON_SEARCH(c.surveys, 'all', s.survey_uuid) IS NOT NULL)
ORDER BY s.id;
如果要包含surveys
其中没有行的行crons
,请使用LEFT JOIN
.
使用以下数据:
MariaDB [test]> select * from crons;
+----+--------------+--------------------------------------------------------------+
| id | depts | surveys |
+----+--------------+--------------------------------------------------------------+
| 1 | {"d1": "d1"} | ["B8606CA2F4000004"] |
| 2 | {"d2": "d2"} | ["B8606CA2F4000005"] |
| 3 | {"d3": "d3"} | ["B8606CA2F4000004", "B8606CA2F4000006"] |
| 4 | {"d4": "d4"} | ["B8606CA2F4000004", "B8606CA2F4000005", "B8606CA2F4000007"] |
| 5 | {"d5": "d5"} | [""] |
| 6 | {"d6": "d6"} | [""] |
+----+--------------+--------------------------------------------------------------+
6 rows in set (0.000 sec)
MariaDB [test]> select * from surveys;
+----+------------------+
| id | survey_uuid |
+----+------------------+
| 5 | B8606CA2F4000004 |
| 6 | B8606CA2F4000005 |
| 7 | B8606CA2F4000006 |
| 8 | B8606CA2F4000007 |
| 9 | B8606CA2F4000008 |
| 10 | B8606CA2F4000009 |
| 11 | B8606CA2F400000A |
+----+------------------+
7 rows in set (0.000 sec)
查询的结果是:
MariaDB [test]> SELECT s.id, s.survey_uuid, c.id, c.depts, c.surveys FROM surveys s JOIN crons c ON (JSON_SEARCH(c.surveys, 'all', s.survey_uuid) IS NOT NULL) ORDER BY s.id;
+----+------------------+----+--------------+--------------------------------------------------------------+
| id | survey_uuid | id | depts | surveys |
+----+------------------+----+--------------+--------------------------------------------------------------+
| 5 | B8606CA2F4000004 | 1 | {"d1": "d1"} | ["B8606CA2F4000004"] |
| 5 | B8606CA2F4000004 | 3 | {"d3": "d3"} | ["B8606CA2F4000004", "B8606CA2F4000006"] |
| 5 | B8606CA2F4000004 | 4 | {"d4": "d4"} | ["B8606CA2F4000004", "B8606CA2F4000005", "B8606CA2F4000007"] |
| 6 | B8606CA2F4000005 | 2 | {"d2": "d2"} | ["B8606CA2F4000005"] |
| 6 | B8606CA2F4000005 | 4 | {"d4": "d4"} | ["B8606CA2F4000004", "B8606CA2F4000005", "B8606CA2F4000007"] |
| 7 | B8606CA2F4000006 | 3 | {"d3": "d3"} | ["B8606CA2F4000004", "B8606CA2F4000006"] |
| 8 | B8606CA2F4000007 | 4 | {"d4": "d4"} | ["B8606CA2F4000004", "B8606CA2F4000005", "B8606CA2F4000007"] |
+----+------------------+----+--------------+--------------------------------------------------------------+
7 rows in set (0.000 sec)
推荐阅读
- phpmailer - 如何确保 html 电子邮件中的 html 一致性?
- angular - 如何正确链接导入的拦截器
- javascript - 如何在 html 中使用 field-wrap 标签添加复选框?
- apache - org.apache.fop.fo.flow.ExternalGraphic 捕获并记录 ImageException 我想自己处理
- java - 使用变量类实例化抽象类
- java - Kafka kstream-kstream 加入滑动窗口内存使用量随着时间的推移而增长,直到 OOM
- python - 我在 Windows 中安装机器人框架时遇到环境错误,在允许 python 根目录中的所有权限后发生事件
- ios - “使用未解析的标识符‘sqlite3_key’”
- dax - 从单个单元格中提取唯一值
- excel - 打印故障导致另一个子程序