从MySQL中的JSON对象中提取密钥对值

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    info JSON
);

INSERT INTO users (info) VALUES ('{"name": "Alice", "age": 30, "email": "alice@example.com"}');
INSERT INTO users (info) VALUES ('{"name": "Bob", "age": 25, "email": "bob@example.com"}');

SELECT id, JSON_EXTRACT(info, '$.name') AS name, JSON_EXTRACT(info, '$.age') AS age, JSON_EXTRACT(info, '$.email') AS email FROM users;

SELECT id, info->>'$.name' AS name, info->>'$.age' AS age, info->>'$.email' AS email FROM users;

SELECT id, info->>'$.address' AS address FROM users;

SELECT id, info->>'$.address' AS address FROM users WHERE JSON_CONTAINS(info, '"address"', '$');

SELECT id, info->>'$.age' AS age FROM users;

SELECT id, CAST(info->>'$.age' AS UNSIGNED) AS age FROM users;