0

My table structure in MySQL

CREATE TABLE `user_data` (
  `id` int(11) NOT NULL,
  `user` varchar(100) NOT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`data`))
)

Table data

id user data
1 1 {"box1": {"books": 12, "pen": 100},"box2": {"books": 13, "pen": 200},"box4": {"books": 17, "pen": 300},"box5": {"books": 16, "pen": 300}}
2 5 {"box1": {"books": 12, "pen": 100},"box2": {"books": 13, "pen": 200},"box3": {"books": 14, "pen": 300}}

My expected results:

box books pen
box1 24 200
box2 26 400
box3 14 300
box4 17 300
box5 16 300

What is the MySQL select query to get this result?

wchiquito
  • 16,177
  • 2
  • 34
  • 45
prabhakaran
  • 1
  • 1
  • Directly using an SQL this does not seem to be possible. If you are using MySQL that supports JSON functions, then you can alter your table and make `data` column of type `JSON` instead of a `longtext`. Then you can try some JSON functions available in MySQL - https://dev.mysql.com/doc/refman/8.0/en/json-functions.html. Still it seems a bit unlikely to do the exact thing that you want to do in the given JSON structure. I.e. group by on the keys of JSON. There is `JSON_KEYS` function which will give you an array of keys, but not sure how to group & then sum on the inner keys as well – Ishan May 02 '23 at 08:26
  • You provide 2 datatables. What is sample data and what is desired output? Also - provide sample data not as a table but as INSERT INTO. Also - provide precise MySQL version. And why you use LONGTEXT datatype instead of JSON? – Akina May 02 '23 at 08:54
  • If you are using MySQL >= 8.0.4 you can use [JSON_TABLE](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table), here [dbfiddle](https://dbfiddle.uk/0XEzEzNR) for ideas. – wchiquito May 02 '23 at 15:31

0 Answers0