mysql5.7:SQL_JSON型データ集計検証

json型で1カラムにリスト情報を持たせてリスト内の項目をKEYにしてを検索できるなら、mysql5.6から5.7にverupしたいと思い調査。できなかったので、5.7へのverupは断念した。

環境構築

Vagrant(Ubuntu14.04)にて下記のコマンドでさっくり構築

sudo apt-get remove --purge mysql-server* mysql-common
wget https://dev.mysql.com/get/mysql-apt-config_0.8.1-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.1-1_all.deb
sudo apt-get update
sudo apt-get install mysql-server

検証

この記事のSQLを突っ込んで検証。

create table json_test  ( `col` JSON)
INSERT INTO `json_test` VALUES ('{"x":10, "y":20}'), ('{"x":20, "y":40}'), ('{"x":30, "y":60}'), ('{"x":40, "y":80}'), ('{"x":50, "y":100}');
INSERT INTO `json_test` VALUES ('{"x":10, "y":21}'), ('{"x":20, "y":41}'), ('{"x":30, "y":61}'), ('{"x":40, "y":81}'), ('{"x":50, "y":101}');
create table json_test2  ( `col` JSON)
INSERT INTO `json_test2` VALUES ('[10,20,30,40,50]'), ('[11,21,31,41,51]'), ('[12,22,32,42,52]'), ('{"x":40, "y":80}'), ('{"x":50, "y":100}');
create table json_test3  ( `col` JSON)
INSERT INTO `json_test3` VALUES ('{"x":1,"y":[10,20,30,40,50]}'), ('{"x":2,"y":[11,21,31,41,51]}'), ('{"x":3,"y":[12,22,32,42,52]}');

JSON_EXTRACTでjson型を使えるっぽい。

select  JSON_EXTRACT(`col`, '$.x') as x, JSON_EXTRACT(`col`, '$.y') as y from json_test order by x ,y
# x, y
'10', '20'
'10', '21'
'20', '40'
'20', '41'
'30', '60'
'30', '61'
'40', '80'
'40', '81'
'50', '100'
'50', '101'
select  JSON_EXTRACT(`col`, '$.x') as x , SUM(JSON_EXTRACT(`col`, '$.y')) from json_test  group by x
# x, SUM(JSON_EXTRACT(`col`, '$.y'))
'10', '41'
'20', '81'
'30', '121'
'40', '161'
'50', '201'
select  JSON_EXTRACT(`col`, '$.x') as x , MAX(JSON_EXTRACT(`col`, '$.y')) from json_test  group by x
# x, MAX(JSON_EXTRACT(`col`, '$.y'))
'10', '21'
'20', '41'
'30', '61'
'40', '81'
'50', '101'

リストをたて列に直すのは調べても大変そう。
array select mysql5.7とかで調べたらみんな苦労してそうだったのでもういいか、という結論になりました。

select  JSON_EXTRACT(`col`, '$.x') as x, JSON_EXTRACT(`col`, '$.y') as y from json_test3 order by x ,y
# x, y
'1', '[10, 20, 30, 40, 50]'
'2', '[11, 21, 31, 41, 51]'
'3', '[12, 22, 32, 42, 52]'

select  JSON_EXTRACT(`col`, '$.x') as x, JSON_EXTRACT(`col`, '$.y[1]') as y from json_test3 order by x ,y
# x, y
'1', '20'
'2', '21'
'3', '22'