MySQL 8.0

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

CREATE TABLE test (id INT, jdoc JSON);

INSERT INTO test VALUES 
  (1, '{"groups": {"CS":15, "Physics":20,"History":10}}'),
  (2, '{"groups": {"CS":6, "Physics":8,"Chemistry":5}}');
  
SELECT id, JSON_PRETTY(jdoc) jdoc FROM test;

Records: 2  Duplicates: 0  Warnings: 0




id
jdoc


1
{
  "groups": {
    "CS": 15,
    "History": 10,
    "Physics": 20
  }
}

2
{
  "groups": {
    "CS": 6,
    "Physics": 8,
    "Chemistry": 5
  }
}


CREATE PROCEDURE proc ()
BEGIN
SELECT CONCAT('SELECT test.id,jsontable.* FROM test CROSS JOIN JSON_TABLE(test.jdoc,"$.groups" COLUMNS(',
              GROUP_CONCAT(DISTINCT jsontable.`key`, ' INT PATH "$.', jsontable.`key`, '"' SEPARATOR ','),
              ')) jsontable')
FROM test
CROSS JOIN JSON_TABLE(JSON_KEYS(test.jdoc, '$.groups'),
                      '$[*]' COLUMNS ( `key` VARCHAR(64) PATH '$' )) jsontable
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
END
CALL proc;


id
Chemistry
CS
History
Physics


1
null
15
10
20

2
5
6
null
8

id	jdoc
1	{ "groups": { "CS": 15, "History": 10, "Physics": 20 } }
2	{ "groups": { "CS": 6, "Physics": 8, "Chemistry": 5 } }