MariaDB中JSON_SET无通配符时更新JSON数组指定元素的方案

Got it, since JSON_SET doesn't support wildcard paths for targeting array elements directly, we can use a combination of MySQL's JSON functions to unpack the array, modify the specific entry, then repack it back into the JSON structure. Here's a practical implementation tailored to your scenario:

Full Update Query #

UPDATE table_name t
JOIN (
    SELECT 
        id,
        JSON_ARRAYAGG(
            CASE 
                WHEN j.student_id = '5' THEN JSON_SET(j.student, '$.name', 'bbb')
                ELSE j.student
            END
        ) AS updated_student_array
    FROM table_name,
         JSON_TABLE(
             class,
             '$.student[*]' COLUMNS (
                 student JSON PATH '$',
                 student_id VARCHAR(10) PATH '$.student_id'
             )
         ) j
    -- Uncomment this line if you only want to update a specific row (e.g., id=1)
    -- WHERE t.id = 1
    GROUP BY id
) jt ON t.id = jt.id
SET t.class = JSON_SET(t.class, '$.student', jt.updated_student_array);

How This Works #

Let's break down each part to make it clear:

• JSON_TABLE: This function unpacks the student array from the class JSON column into a relational table-like structure. We extract both the full student JSON object and the student_id value to pinpoint which entry needs updating.
• CASE Statement: For each unpacked student entry, we check if the student_id matches '5'. If it does, we use JSON_SET to overwrite the name field with 'bbb'; otherwise, we keep the original student object unchanged.
• JSON_ARRAYAGG: This aggregates all the (modified or original) student objects back into a single JSON array, ready to replace the original array.
• JOIN & UPDATE: We join the original table with our subquery result, then use JSON_SET to swap out the old student array in the class column with our updated version.

Quick Notes #

• This requires MySQL 8.0 or newer since JSON_TABLE was introduced in that release.
• If you need to update all rows in the table (not just a specific id), remove the WHERE t.id = 1 clause from the subquery.
• Double-check that the data type for student_id in JSON_TABLE matches your actual data (in your example it's a string '5', so we use VARCHAR).

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