时间戳

首页 > 解决方案 > MySQL JSON_SEARCH 用于嵌套对象的多个参数

mysql - MySQL JSON_SEARCH 用于嵌套对象的多个参数

问题描述

我有一个包含 json 格式字符串的数据库表：

CREATE TABLE `template` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TemplateData` longtext NOT NULL,
  PRIMARY KEY (`Id`)
);

INSERT INTO template (Id, TemplateData) VALUES
(1, '[]'),
(2, '[{"type":"template","id":1}]'),
(3, '[{"type":"other", "id":1}]'),
(4, '[{"type":"template","id":3},{"type":"template","id":1}]'),
(5, '[{"type":"template","id":2}]');

http://sqlfiddle.com/#!9/739f3a

背景：这些记录是前端构建动态视图的模板。每个模板都可以包含另一个模板。所以根据上面的数据，记录#2是一个模板，里面使用了另一个模板#1。将其视为可重复使用的部件。

在 json 中，我有一个包含多种类型对象的数组。在我的示例中，有两个不同的变体：{type: "template", id: number}和{"type": "other", "id": number}.

服务器架构

生产：

MySQL 服务器版本 8.0.21。

发展：

MariaDB 服务器版本 10.4.11

我想通过 SELECT 检索什么

我需要使用特定其他模板的所有模板的列表。我想选择所有包含$[*].type='template'AND对象的记录$[*].id=1。

我已经尝试过的

我使用 JSON_SEARCH() 和 JSON_EXTRACT() 进行了一些试验，但无法获得我预期的行：

SELECT
    Id,
    JSON_EXTRACT(TemplateData,
                 JSON_UNQUOTE(
                 REPLACE(JSON_SEARCH(TemplateData,
                                     'all',
                                     'template'),
                         '.type"',
                         '.id"'))) AS includedTemplateId 
FROM    template 
HAVING  includedTemplateId = 1

仅返回一条 ID 为 2 的记录，但不返回 ID 为 4 的记录，因为带有“all”的 JSON_SEARCH 提供了一个路径数组，但 JSON_EXTRACT 不允许路径为数组。

什么是不可能的

我也尝试使用简单的 LIKE 表达式，但如果 order 或 objects 参数不同（pe: {id: number, type: "template"}）或使用空格或不同的引号，则问题结束，则 like 不匹配。

附加目标

这将是最完美的结果，如果我在模板 ID #1 之后也获得记录 #5 进行搜索，因为 #5 使用 #2，它使用 #1。但这将是下一个层次。

标签： mysqlsqljsonpathmysql-jsonjson-extract

解决方案

MySQL 8.0.21 的解决方案：

SELECT template.id
FROM template
CROSS JOIN JSON_TABLE( template.TemplateData,
                       "$[*]" COLUMNS( type VARCHAR(254) PATH "$.type",
                                         id INT PATH "$.id" )
                     ) AS jsontable
WHERE jsontable.type = 'template'
  AND jsontable.id = 1;

小提琴

如果模板对象可能在单独的值中重复，则添加 DISTINCT。

关于 MariaDB 有什么建议吗？

适用于 MariaDB 的解决方案草案。

WITH RECURSIVE
cte1 AS ( SELECT MAX(LENGTH(TemplateData) - LENGTH(REPLACE(TemplateData, '{', ''))) max_obj_count
          FROM template ),
cte2 AS ( SELECT 1 num
          UNION ALL
          SELECT num + 1
          FROM cte2
          WHERE num < ( SELECT max_obj_count
                        FROM cte1 ) )
SELECT DISTINCT
       template.id
FROM template
CROSS JOIN cte2
WHERE LOCATE('"type":"template"' ,SUBSTRING_INDEX(SUBSTRING_INDEX(template.TemplateData, '}', cte2.num), '{', -1))
  AND LOCATE('"id":1' ,SUBSTRING_INDEX(SUBSTRING_INDEX(template.TemplateData, '}', cte2.num), '{', -1))

问题 - 此代码严格搜索'"type":"template"'和'"id":1'子字符串 - 即它不会找到值被写入的行，例如，'"type" : "template"'（多余的空格字符）或'"id":"1"'（引用的值）。

如果要消除此问题，则必须再SUBSTRING_INDEX(SUBSTRING_INDEX(template.TemplateData, '}', cte2.num), '{', -1)输入一个 CTE，将其从所有[]{}字符中清除，然后在 WHERE 中将该值包装{}并处理为 JSON 对象。

推荐阅读