MySQL - Select or Extract JSON object data as Column and value

Question

I want to extract JSON object data (Key/value) as multiple column/value in MySQL.

Let say I have following data:

CREATE TABLE t3 (id INT, jdoc JSON);

INSERT INTO t3 VALUES 
  (1, '{"groups": {"CS":15, "Physics":20,"Chemistry":10}}'),
  (2, '{"groups": {"CS":6, "Physics":8,"Chemistry":5}}');

Is there anyway that above data can be extracted as following output. e.g. Key Name as column name and values as row.

id| CS | Physics | Chemistry
1 | 15 | 20      | 10
2 | 6  | 8       | 5

Please note, I can change the jdoc's JSON data format in order to get required output.

Dynamic output structure needs in stored procedure with dynamic SQL. And yes, this is possible. PS. If your JSON value always matches shown format then the task can be solved in single query. — Akina
– Akina, Commented Oct 22, 2021 at 8:10

Akina · Accepted Answer · 2021-10-22 09:43:42Z

6

SELECT test.id, 
       jsontable.cs, 
       jsontable.physics, 
       jsontable.chemistry
FROM test
CROSS JOIN JSON_TABLE(test.jdoc,
                      '$.groups' COLUMNS ( cs INT PATH '$.CS',
                                           physics INT PATH '$.Physics',
                                           chemistry INT PATH '$.Chemistry')) jsontable

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f3d1bab18c3ea50706ddacd3cffd2dac

For dynamic key list use stored procedure:

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

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8011845c76cc60137d7fea5d3806761a

edited Oct 22, 2021 at 9:43

answered Oct 22, 2021 at 8:14

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Zeeshanef Over a year ago

Thank you for the answer, but unfortunately Keys are not known, format will be same though. It can be any Key/Value pair and I need to select all key/value pair as column/row. Thanks

Akina Over a year ago

@Zeeshanef If so then this is not possible in single query. You need in dynamic SQL - get keys list from JSON (use JSON_KEYS), then build SQL text similar to my solution and execute it.

Zeeshanef Over a year ago

great solution which is working fine in dbfiddle. I didn't mention earlier that I am using MySQL 5.7 and I think JSON_TABLE function wouldn't work in 5.7 version apologies for not mentioning my version. Can I sill use your solution in 5.7 with alternatives?

Akina Over a year ago

@Zeeshanef In 5.7 you cannot use JSON_TABLE. Use synthetic numbers table (use JSON_LENGTH for to define upper value) for to extract separate keys names from JSON_KEYS() output (I recommend temptable on Memory engine) then use them while build the SQL code.

Zeeshanef Over a year ago

Akina, Can I get required output in MySQL 5.7, if have little simplified JSON data like following: jodc = {"CS":15, "Physics":20,"Chemistry":10} (without groups key). I don't have idea of MySQL's JSON related functions.

Collectives™ on Stack Overflow

MySQL - Select or Extract JSON object data as Column and value

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related