mysql 遍历json数组中

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

[
    {"name": "Alice", "age": 30},
    {"name": "Bob", "age": 25},
    {"name": "Charlie", "age": 35}
]

SELECT id, JSON_UNQUOTE(JSON_EXTRACT(data, CONCAT('$[', idx, '].name'))) AS name
FROM users, JSON_TABLE(data, '$[*]' COLUMNS(idx FOR ORDINALITY)) AS jt;

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY,
    data JSON
);

-- 插入示例数据
INSERT INTO users (id, data) VALUES
(1, '[{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}, {"name": "Charlie", "age": 35}]');

-- 查询所有用户的名字
SELECT id, JSON_UNQUOTE(JSON_EXTRACT(data, CONCAT('$[', idx, '].name'))) AS name
FROM users, JSON_TABLE(data, '$[*]' COLUMNS(idx FOR ORDINALITY)) AS jt;