对于下面的表结构,一个用户表,一个角色表,用户表里面的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]');
在开发中,希望返回用户信息的时候,将role_id
中的id
换成{id: 1, name: '部门1'}
这样的形式。
具体思路如下:
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;