MySQL 5.7 - Select JSON document as Table-CodePudding

I have a following JSON data format which has simple Key/Value pair data.

CREATE TABLE test_table (id INT, jdoc JSON);

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

I need to select above data in following format. i.e. Key name (Key Name is dynamic) as column name and value as data.

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

I got the following solution from senior SO member "Akina" using JSON_TABLE. But this function only supports in MySQL 8.0

https://stackoverflow.com/a/69673530/1791406

Is there any alternatives of JSON_TABLE function in MySQL 5.7. Or get any clue to get the required results.

CodePudding user response：

Yes, the old fashioned way, using json_extract and paths:

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=403c4a6fa81b514214d3f10ddb4cf50e

SELECT t.id, 
  t.jdoc->'$.CS' as CS, 
  t.jdoc->'$.Physics' as Physics, 
  t.jdoc->'$.Chemistry' as Chemistry
FROM test_table t

Where -> is a shorthand operator for json_extract. Depending on the format of the data you are getting out, you may need to use ->> which is the shorthand operator for json_unquote(json_extract()).

You can support unknown keys by building dynamic paths, but it becomes less readable:

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=bb9bf699ace41e6dd0e475e60b60605b

SELECT t.id, 
  json_extract(t.jdoc, concat('$.', json_extract(json_keys(t.jdoc), '$[0]'))) as k0,
  json_extract(t.jdoc, concat('$.', json_extract(json_keys(t.jdoc), '$[1]'))) as k1,
  json_extract(t.jdoc, concat('$.', json_extract(json_keys(t.jdoc), '$[2]'))) as k2
FROM test_table t;

You can see that the outputs are identical, except for the column names. If the column names are important, you can use a prepared statement to dynamically generate the query with field names, then prepare a statement from that and execute it. See this fiddle:

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=fb2ab27407789bdfad04e0a53d9a0257

SELECT json_keys(t.jdoc) INTO @keys FROM test_table t LIMIT 1;
SET @query = CONCAT("SELECT t.id, "
"t.jdoc->'$.", json_extract(@keys, '$[0]'), "' as ", json_extract(@keys, '$[0]'), ", ",
"t.jdoc->'$.", json_extract(@keys, '$[1]'), "' as ", json_extract(@keys, '$[1]'), ", ",
"t.jdoc->'$.", json_extract(@keys, '$[2]'), "' as ", json_extract(@keys, '$[2]'),
"FROM test_table t;");

PREPARE stmt FROM @query;
EXECUTE stmt;

Readability goes straight out the window, and maintaining will be a real chore. I believe variables are deprecated, so there's no telling how long it will work, and it's generally recommended to avoid running dynamically generated sql. But it will generate the desired result.

Bear in mind that executing dynamically generated sql opens you up to many possible harms. You are essentially taking text from somewhere else and then executing it on your system.