MySQL/MariaDB嵌套JSON多条件查询及模板递归关联需求

针对你的场景，我给你分两种情况提供适配MySQL 8.0.21和MariaDB 10.4.11的健壮解决方案：

1. 基础查询：获取直接引用指定模板的记录 #

你之前遇到的问题是JSON_SEARCH/JSON_EXTRACT无法处理数组中多个匹配元素的情况，这里用JSON_TABLE就能完美解决——它可以把JSON数组拆成关系型的行数据，让你像操作普通表一样筛选条件。

查询直接引用模板ID=1的SQL如下：

SELECT DISTINCT t.Id, t.TemplateData
FROM template t
JOIN JSON_TABLE(
    t.TemplateData,
    '$[*]' COLUMNS(
        type VARCHAR(20) PATH '$.type',
        template_id INT PATH '$.id'
    )
) jt
WHERE jt.type = 'template' AND jt.template_id = 1;

这个语句会把每条记录的TemplateData数组拆成独立的行，然后筛选出type为template且id等于1的条目，最后用DISTINCT避免同一条记录被多次匹配。执行后会精准返回Id=2和4的记录，完全符合你的预期。

和LIKE表达式不同，这个方法完全基于JSON结构解析，不受属性顺序、空格或引号格式变化的影响，稳定性拉满。

2. 进阶查询：递归获取所有直接+间接引用的记录 #

如果要包含间接引用（比如Id=5引用Id=2，而Id=2引用Id=1，所以查询Id=1时要返回Id=2、4、5），可以用**递归CTE（公共表表达式）**来实现，这两个数据库版本都支持这个特性。

对应的SQL语句：

WITH RECURSIVE referenced_templates AS (
    -- 第一步：先找出直接引用目标模板的记录
    SELECT t.Id, t.TemplateData
    FROM template t
    JOIN JSON_TABLE(
        t.TemplateData,
        '$[*]' COLUMNS(
            type VARCHAR(20) PATH '$.type',
            template_id INT PATH '$.id'
        )
    ) jt
    WHERE jt.type = 'template' AND jt.template_id = 1

    UNION ALL

    -- 第二步：递归查找引用了已找到模板的记录
    SELECT t.Id, t.TemplateData
    FROM template t
    JOIN JSON_TABLE(
        t.TemplateData,
        '$[*]' COLUMNS(
            type VARCHAR(20) PATH '$.type',
            template_id INT PATH '$.id'
        )
    ) jt
    JOIN referenced_templates rt ON jt.template_id = rt.Id
    WHERE t.Id NOT IN (SELECT Id FROM referenced_templates) -- 防止循环引用导致无限递归
)
SELECT DISTINCT Id, TemplateData FROM referenced_templates;

这个逻辑的核心是：

• 初始查询先拿到直接引用目标模板的记录（Id=2、4）；
• 递归步骤会不断查找那些引用了当前结果集中模板的记录，比如Id=5引用了Id=2，所以会被加入结果；
• 最后的WHERE t.Id NOT IN (...)是为了避免循环引用（如果你的模板存在互相引用的情况），防止无限递归。

执行这个语句后，会返回Id=2、4、5的记录，满足你需要的间接关联查询需求。

内容的提问来源于stack exchange，提问作者MIB