如何在MariaDB中用JSON数组匹配提取另一JSON数组的匹配值？

你的问题核心是没有正确拆分JSON数组元素并逐个匹配主表数据，导致json_search无法正确定位匹配项。以下是针对MySQL 8.0+版本的可行方案：

核心思路 #

1. 将pdet表中ingAll字段的JSON数组拆分为单行数据，让每个食材元素单独成为一行记录；
2. 匹配ing_master表中的ingName，筛选出存在于主表的食材；
3. 按产品sync_sku重新聚合匹配的食材，生成目标JSON数组。

具体SQL语句 #

SELECT 
    p.sync_sku,
    p.ingAll,
    -- 用IFNULL处理无匹配项的情况，返回空数组而非NULL
    IFNULL(JSON_ARRAYAGG(m.item), JSON_ARRAY()) AS ingMatch
FROM pdet p
LEFT JOIN JSON_TABLE(
    p.ingAll,
    '$[*]' COLUMNS(item VARCHAR(255) PATH '$')
) m ON EXISTS (
    SELECT 1 FROM ing_master s WHERE s.ingName = m.item
)
GROUP BY p.sync_sku, p.ingAll;

语句说明 #

• JSON_TABLE：MySQL 8.0引入的函数，用于将JSON数组转换为关系型表结构，这里把ingAll的每个元素提取为item字段；
• EXISTS：检查当前拆分出的食材是否存在于ing_master表中，避免无效的关联；
• JSON_ARRAYAGG：将分组后的匹配食材重新聚合为JSON数组，IFNULL确保无匹配时返回空数组[]而非NULL。

适配低版本MySQL（无JSON_TABLE） #

如果你的MySQL版本低于8.0，可以借助数字序列表配合JSON_EXTRACT实现拆分，示例如下（需预先创建包含连续数字的辅助表nums，数字范围覆盖最大数组长度）：

SELECT 
    p.sync_sku,
    p.ingAll,
    IFNULL(JSON_ARRAYAGG(JSON_UNQUOTE(JSON_EXTRACT(p.ingAll, CONCAT('$[', n.num, ']')))), JSON_ARRAY()) AS ingMatch
FROM pdet p
LEFT JOIN nums n ON n.num < JSON_LENGTH(p.ingAll)
LEFT JOIN ing_master s ON s.ingName = JSON_UNQUOTE(JSON_EXTRACT(p.ingAll, CONCAT('$[', n.num, ']')))
WHERE s.ingName IS NOT NULL
GROUP BY p.sync_sku, p.ingAll;

验证结果 #

运行上述8.0+版本的SQL后，将得到你预期的目标结果：

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