val |
---|
{"Bob": {"A": 10, "B": 5}, "Alice": {"A": 10, "C": 5}} |
{"Bob": {"D": 10, "E": 5}, "Mike": {"A": 5, "F": 5}} |
上記のようなJSON型の値を持った行から下記のような結果を得たいが、MySQL 5.7には JSON_TABLE が実装されていない。
name | type | val |
---|---|---|
Bob | A | 10 |
Bob | B | 5 |
Bob | D | 10 |
Bob | E | 5 |
Alice | A | 10 |
Alice | C | 5 |
Mike | A | 5 |
Mike | F | 5 |
SELECT t.k AS name, JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.v), CONCAT('$[', idx_list_2.idx, ']'))) AS type, JSON_UNQUOTE(JSON_EXTRACT(t.v, CONCAT('$.', JSON_EXTRACT(JSON_KEYS(t.v), CONCAT('$[', idx_list_2.idx, ']'))))) AS val FROM ( SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(val), CONCAT('$[', idx_list.idx, ']'))) AS k, JSON_EXTRACT(val, CONCAT('$.', JSON_EXTRACT(JSON_KEYS(val), CONCAT('$[', idx_list.idx, ']')))) AS v FROM json CROSS JOIN ( SELECT @idx := @idx + 1 AS idx FROM (SELECT @idx := -1) AS init_idx CROSS JOIN information_schema.`COLUMNS` ) AS idx_list WHERE JSON_EXTRACT(JSON_KEYS(val), CONCAT('$[', idx_list.idx, ']')) IS NOT NULL ) AS t CROSS JOIN ( SELECT @idx2 := @idx2 + 1 AS idx FROM (SELECT @idx2 := -1) AS init_idx CROSS JOIN information_schema.`COLUMNS` ) AS idx_list_2 WHERE JSON_EXTRACT(JSON_KEYS(t.v), CONCAT('$[', idx_list_2.idx, ']')) IS NOT NULL;
メンテナンス性が終わっているなあ...。