MySQL 8.0

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

CREATE TABLE owners (id INT, owner CHAR(4), box VARCHAR(255));
INSERT INTO owners VALUES
(1   ,   'Nick'     ,  '[]'),
(2   ,   'Jack'     ,  '[]'),
(3   ,   'Jane'     ,  '[]');
SELECT * FROM owners;


id
owner
box


1
Nick
[]

2
Jack
[]

3
Jane
[]


CREATE TABLE Storage (id INT, fruit VARCHAR(6), owner_id INT);
INSERT INTO Storage VALUES
(1   ,  'Apple'  ,   1),
(2   ,  'Apple'  ,   3),
(3   ,  'Banana' ,   2),
(4   ,  'Banana' ,   1),
(5   ,  'Banana' ,   1),
(6   ,  'orange' ,   1),
(7   ,  'Apple'  ,   3),
(8   ,  'Orange' ,   2),
(9   ,  'Apple'  ,   2);
SELECT * FROM Storage;


id
fruit
owner_id


1
Apple
1

2
Apple
3

3
Banana
2

4
Banana
1

5
Banana
1

6
orange
1

7
Apple
3

8
Orange
2

9
Apple
2


SELECT owners.id, 
       JSON_OBJECTAGG(fruits.fruit, COALESCE(boxes.cnt, 0)) boxes
FROM (SELECT DISTINCT fruit
      FROM Storage) fruits
CROSS JOIN owners
LEFT JOIN (SELECT owners.id, Storage.fruit, COUNT(*) cnt
           FROM owners
           JOIN Storage ON owners.id = Storage.owner_id
           GROUP BY owners.id, Storage.fruit) boxes ON owners.id = boxes.id
                                                         AND fruits.fruit = boxes.fruit
GROUP BY owners.id;


id
boxes


1
{"Apple": 1, "Banana": 2, "orange": 1}

2
{"Apple": 1, "Banana": 1, "orange": 1}

3
{"Apple": 2, "Banana": 0, "orange": 0}

id	fruit	owner_id
1	Apple	1
2	Apple	3
3	Banana	2
4	Banana	1
5	Banana	1
6	orange	1
7	Apple	3
8	Orange	2
9	Apple	2

id	boxes
1	{"Apple": 1, "Banana": 2, "orange": 1}
2	{"Apple": 1, "Banana": 1, "orange": 1}
3	{"Apple": 2, "Banana": 0, "orange": 0}