mysql 操作json数组

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    preferences JSON
);

INSERT INTO users (name, preferences) VALUES
('Alice', '[{"key": "theme", "value": "dark"}, {"key": "notifications", "value": true}]'),
('Bob', '[{"key": "theme", "value": "light"}, {"key": "notifications", "value": false}]');

SELECT id, name, preferences->'$.key' AS key, preferences->'$.value' AS value
FROM users, JSON_TABLE(preferences, '$[*]' COLUMNS (key VARCHAR(255) PATH '$.key', value JSON PATH '$.value')) AS jt;

SELECT id, name, preferences->'$.key' AS key, preferences->'$.value' AS value
FROM users, JSON_TABLE(preferences, '$[*]' COLUMNS (key VARCHAR(255) PATH '$.key', value JSON PATH '$.value')) AS jt
WHERE jt.key = 'theme';

UPDATE users
SET preferences = JSON_SET(preferences, '$[0].value', 'light')
WHERE id = 1;