对于下面的表结构，一个用户表，一个角色表，用户表里面的role_id通过JSON格式保存角色id的数组。
create table user(
id int not null auto_increment primary key,
name varchar(32) not null,
role_id json not null
);
create table role(
id int not null auto_increment primary key,
name varchar(32) not null
);
insert into role(name) values ('部门1'),('部门2'),('部门3');
insert into user(name, role_id) values ('user1', '[1, 2]');
insert into user(name, role_id) values ('user2', '[2, 3]');
image-20220814234157852.png在开发中，希望返回用户信息的时候，将role_id中的id换成{id: 1, name: '部门1'} 这样的形式。
具体思路如下：
代码语言：sql复制select *
from user join json_table(user.role_id, '$[*]' columns(rid int path '$')) as t;
select *
from user join json_table(user.role_id, '$[*]' columns(rid int path '$')) as t
left join role on role.id = t.rid;
select user.id, user.name,
json_arrayagg(json_object('id', role.id, 'name', role.name)) as role
from user join json_table(user.role_id, '$[*]' columns(rid int path '$')) as t
left join role on role.id = t.rid
group by user.id;
image-20220815000324567.png
