MySQL JSON通配符使用问题：基于JSON数组过滤关联表数据

Let's break down how to dynamically match the wildcard patterns from Table A's JSON Filter array against Table B's Object column, based on a specific Identifier.

Step-by-Step Explanation #

• Extract JSON Array Elements: First, we need to turn the JSON array in Table A's Filter column into individual rows of patterns. For MySQL 8.0+ or MariaDB 10.5+, JSON_TABLE is the ideal tool—it unpacks JSON arrays into relational rows cleanly.
• Join & Match: Join these extracted patterns with Table B, using a LIKE condition that wraps each pattern in % to create the wildcard match (just like your manual LIKE '%Test1%' logic).
• Avoid Duplicates: Use DISTINCT to ensure we don't return the same UID multiple times if it matches multiple patterns from the array.

Complete SQL Query #

SELECT DISTINCT B.UID
FROM TableA A
-- Unpack the JSON Filter array into individual pattern rows
JOIN JSON_TABLE(
    A.Filter,
    '$[*]' COLUMNS(match_pattern VARCHAR(255) PATH '$')
) AS filter_patterns
-- Match Table B's Object against each wildcard pattern
JOIN TableB B ON B.Object LIKE CONCAT('%', filter_patterns.match_pattern, '%')
-- Target the specific Identifier from Table A
WHERE A.Identifier = 'Obj1';

How This Works #

• JSON_TABLE(A.Filter, '$[*]' COLUMNS(match_pattern VARCHAR(255) PATH '$')): This takes the JSON array (e.g., ["Test1","Test2"]) and creates a temporary table filter_patterns with one row per array element.
• B.Object LIKE CONCAT('%', filter_patterns.match_pattern, '%'): This replicates your manual OR-chain of LIKE conditions, but dynamically adapts to every element in the JSON array—no need to hardcode patterns.
• DISTINCT: Ensures if a single UID matches multiple patterns (e.g., an Object containing both Test1 and Test2), it only appears once in the results.

For Older MySQL Versions (Pre-8.0) #

If you're stuck on a version without JSON_TABLE, you can use a string-manipulation workaround (though it's less scalable):

SELECT DISTINCT B.UID
FROM TableA A
JOIN TableB B
WHERE A.Identifier = 'Obj1'
  AND EXISTS (
      SELECT 1
      FROM (
          SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(A.Filter, '[', ''), ']', ''), ',', n), ',', -1) AS pattern
          FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers
          WHERE n <= JSON_LENGTH(A.Filter)
      ) patterns
      WHERE B.Object LIKE CONCAT('%', TRIM(BOTH '"' FROM patterns.pattern), '%')
  );

Note: Adjust the numbers subquery to cover the maximum possible length of your JSON array.

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