MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT,
       `user_name` varchar(96) NOT NULL,
      PRIMARY KEY (`id`)
    ) ;
    
CREATE TABLE `order` (
      `id` int NOT NULL AUTO_INCREMENT,
      `user_id` int DEFAULT NULL,
      `item_name` varchar(96) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `ix_order_user_id` (`user_id`),
      CONSTRAINT `order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
    ) ;

INSERT into user(user_name)
    values ('user1'),
           ('user2'),
           ('user3'),
           ('user4'),
           ('user5'),
           ('user6'),
           ('user7');
          
INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user1'), "apple");
INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user1'), "banana");
INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user1'), "orange");

INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user2'), "apple");
INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user2'), "orange");

INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user3'), "apple");
INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user3'), "banana");

INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user4'), "apple");

INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user5'), "orange");

INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user6'), "banana");

INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user7'), "banana");
INSERT into `order` (user_id, item_name) values ((select id from user where user.user_name = 'user7'), "grapes");
SELECT * FROM user;
SELECT * FROM `order`;


id
user_name


1
user1

2
user2

3
user3

4
user4

5
user5

6
user6

7
user7




id
user_id
item_name


1
1
apple

2
1
banana

3
1
orange

4
2
apple

5
2
orange

6
3
apple

7
3
banana

8
4
apple

9
5
orange

10
6
banana

11
7
banana

12
7
grapes


SELECT user.user_name,
       CAST(JSON_ARRAYAGG(`order`.item_name) AS CHAR) items_list
FROM user
JOIN `order` ON user.id = `order`.user_id
GROUP BY user.user_name
ORDER BY GROUP_CONCAT(`order`.item_name);


user_name
items_list


user4
["apple"]

user3
["apple", "banana"]

user1
["apple", "banana", "orange"]

user2
["apple", "orange"]

user6
["banana"]

user7
["banana", "grapes"]

user5
["orange"]

id	user_id	item_name
1	1	apple
2	1	banana
3	1	orange
4	2	apple
5	2	orange
6	3	apple
7	3	banana
8	4	apple
9	5	orange
10	6	banana
11	7	banana
12	7	grapes

user_name	items_list
user4	["apple"]
user3	["apple", "banana"]
user1	["apple", "banana", "orange"]
user2	["apple", "orange"]
user6	["banana"]
user7	["banana", "grapes"]
user5	["orange"]