MySQL中JSON数组的一点处理

原创

一介程序员

修改于 2022-08-15 00:20:29

2.4K0

修改于 2022-08-15 00:20:29

对于下面的表结构，一个用户表，一个角色表，用户表里面的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;