mysql-json\u搜索嵌套对象的多个参数

vjhs03f7 于 2021-07-27 发布在 Java

关注(0)|答案(1)|浏览(374)

我有一个包含json格式字符串的db表：

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

我要通过选择检索的内容

我需要一个所有模板的列表，这是使用一个特定的其他模板。我要选择所有记录，其中包含 $[*].type='template' 以及 $[*].id=1 .
根据给定的记录，我想检索行#2和#4，因为它们都包含一个匹配这两个参数的对象。复杂度在#4上，在数组索引1处有记录。
我不想要#1，因为数组中没有元素
我不想要3，因为 $[0].type 不是模板

我已经试过了

我使用json\u search（）和json\u 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\u search提供了一个路径数组，但是json\u extract不允许path是一个数组。

什么是不可能的

我也尝试过使用一个简单的like表达式，但是如果order或objects参数不同，就解决了这个问题（例如： {id: number, type: "template"} )或者用空格或不同的引号表示不匹配。

附加目标

如果在模板id 1之后搜索时也得到记录5，这将是最理想的结果，因为5使用2，2使用1。但这将是下一个层次。

sql mysql mysql-json json-extract JsonPath

来源：https://stackoverflow.com/questions/63169674/mysql-json-search-for-multiple-arguments-of-nested-objects

1条答案

按热度按时间

iq3niunx1#

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的解决方案草案。

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对象处理。

赞(0）回复(0）举报 2021-07-27

我来回答