MySQL中提取复杂JSON指定键所有对应值的技术求助

Hey there! Let's tackle this problem step by step. When dealing with deeply nested JSON in MySQL where you don't know the exact path to a key like Acronym, we have a few solid approaches depending on your MySQL version.

Solution for MySQL 8.0+ (Recommended) #

MySQL 8.0 introduced powerful JSON functions and recursive CTEs that make this task straightforward. Here are two ways to get what you need:

1. Extract All Acronym Values AND Their Full Paths #

This recursive CTE traverses every node in your JSON structure, tracks the full path of each key, and filters for entries where the key is Acronym:

WITH RECURSIVE json_traversal AS (
    -- Start with the root JSON object/array
    SELECT
        '$' AS full_path,
        json_data AS node_value
    FROM your_table
    UNION ALL
    -- Recursively process object keys
    SELECT
        CONCAT(jt.full_path, '.', obj_key) AS full_path,
        JSON_EXTRACT(jt.node_value, CONCAT('$.', obj_key)) AS node_value
    FROM json_traversal jt
    JOIN JSON_TABLE(
        JSON_KEYS(jt.node_value),
        '$[*]' COLUMNS(obj_key VARCHAR(255) PATH '$')
    ) AS object_keys
    WHERE JSON_TYPE(jt.node_value) = 'OBJECT'
    UNION ALL
    -- Recursively process array indices
    SELECT
        CONCAT(jt.full_path, '[', arr_idx, ']') AS full_path,
        JSON_EXTRACT(jt.node_value, CONCAT('$[', arr_idx, ']')) AS node_value
    FROM json_traversal jt
    JOIN JSON_TABLE(
        JSON_LENGTH(jt.node_value),
        '$' COLUMNS(arr_idx INT PATH '$' START AT 0)
    ) AS array_indices
    WHERE JSON_TYPE(jt.node_value) = 'ARRAY'
)
-- Filter for nodes where the final key is "Acronym"
SELECT
    full_path,
    JSON_UNQUOTE(node_value) AS acronym_value
FROM json_traversal
WHERE SUBSTRING_INDEX(full_path, '.', -1) = 'Acronym'
  AND JSON_TYPE(node_value) NOT IN ('OBJECT', 'ARRAY');

2. Simplified: Extract Only Acronym Values (No Paths) #

If you don't need the full path and just want all matching values, use MySQL's recursive JSONPath wildcard ($**.Acronym) to target every occurrence of the key, regardless of nesting:

SELECT
    JSON_UNQUOTE(JSON_EXTRACT(json_data, path)) AS acronym_value
FROM your_table,
     JSON_TABLE(
         -- Find all paths to "Acronym" keys
         JSON_SEARCH(json_data, 'all', NULL, NULL, '$**.Acronym'),
         '$[*]' COLUMNS(path VARCHAR(255) PATH '$')
     ) AS matching_paths;

The $**.Acronym pattern tells MySQL to look for the Acronym key at every level of the JSON structure, and JSON_SEARCH('all') returns all matching paths (not just the first one).

Solution for MySQL 5.7 (Older Versions) #

MySQL 5.7 lacks recursive CTEs and JSON_TABLE, so we'll create a custom recursive function to traverse the JSON and collect all Acronym values:

DELIMITER //
CREATE FUNCTION extract_all_acronyms(json_input JSON) RETURNS TEXT
BEGIN
    DECLARE result TEXT DEFAULT '';
    DECLARE keys_list JSON;
    DECLARE total_items INT;
    DECLARE counter INT DEFAULT 0;
    DECLARE current_key VARCHAR(255);
    DECLARE current_val JSON;

    -- Handle JSON objects
    IF JSON_TYPE(json_input) = 'OBJECT' THEN
        SET keys_list = JSON_KEYS(json_input);
        SET total_items = JSON_LENGTH(keys_list);
        WHILE counter < total_items DO
            SET current_key = JSON_UNQUOTE(JSON_EXTRACT(keys_list, CONCAT('$[', counter, ']')));
            SET current_val = JSON_EXTRACT(json_input, CONCAT('$.', current_key));
            
            IF current_key = 'Acronym' THEN
                SET result = CONCAT(result, JSON_UNQUOTE(current_val), ',');
            ELSE
                -- Recurse into nested objects/arrays
                SET result = CONCAT(result, extract_all_acronyms(current_val));
            END IF;
            
            SET counter = counter + 1;
        END WHILE;
    -- Handle JSON arrays
    ELSEIF JSON_TYPE(json_input) = 'ARRAY' THEN
        SET total_items = JSON_LENGTH(json_input);
        WHILE counter < total_items DO
            SET current_val = JSON_EXTRACT(json_input, CONCAT('$[', counter, ']'));
            SET result = CONCAT(result, extract_all_acronyms(current_val));
            SET counter = counter + 1;
        END WHILE;
    END IF;

    -- Remove trailing comma and return
    RETURN TRIM(TRAILING ',' FROM result);
END //
DELIMITER ;

To use this function:

SELECT extract_all_acronyms(json_data) AS acronym_values FROM your_table;

It will return all Acronym values as a comma-separated string (e.g., SGML,HTML,XHTML if there are multiple matches).

Why Your Previous Query Returned NULL #

Chances are, you were using a fixed path like $.Acronym which only looks for the key at the root level. Since your JSON is nested, that path doesn't exist—hence the NULL result. The solutions above account for any level of nesting and arrays, so they'll find all instances of the Acronym key.

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