MySQLとmariaDBにおけるJSON関連の差異

mariaDBでJSON型のカラムを扱ってはまったのでメモ

環境

Mysql : 5.7.29
mariaDB : 10.5.2

データ準備

create table sample (id int,item json);
insert into sample values(1,'{"kanagawa":"yokohama"}');
insert into sample values(2,'{"yamanashi":"kofu"}');

itemはkey：県名、value：県庁所在地のJSON。

単純な抽出

まずは単純にselect。差は全くない。

MySQL

select * from sample;
+------+--------------------------+
| id   | item                     |
+------+--------------------------+
|    1 | {"kanagawa": "yokohama"} |
|    2 | {"yamanashi": "kofu"}    |
+------+--------------------------+

MariaDB

select * from sample;
+------+--------------------------+
| id   | item                     |
+------+--------------------------+
|    1 | {"kanagawa": "yokohama"} |
|    2 | {"yamanashi": "kofu"}    |
+------+--------------------------+

集約

　1.GROUP_CONCAT

区切り文字を指定しなければいずれも,（カンマ）で結合される。

MySQL

select GROUP_CONCAT(item) from sample;
+------------------------------------------------+
| GROUP_CONCAT(item)                             |
+------------------------------------------------+
| {"kanagawa": "yokohama"},{"yamanashi": "kofu"} |
+------------------------------------------------+

MariaDB

select GROUP_CONCAT(item) from sample;
+----------------------------------------------+
| GROUP_CONCAT(item)                           |
+----------------------------------------------+
| {"kanagawa":"yokohama"},{"yamanashi":"kofu"} |
+----------------------------------------------+

　2.JSON_ARRAYAGG

ここがハマったポイント。MySQLはJSON型のカラムをそのまま配列として返すが、MariaDBはエスケープシーケンスされた文字列の配列として返す。
原因は推測だがMariaDB上では「JSON型はLONGTEXT型のエイリアス」¹であることと思われる。

MySQL

select JSON_ARRAYAGG(item) from sample;
+---------------------------------------------------+
| JSON_ARRAYAGG(item)                               |
+---------------------------------------------------+
| [{"kanagawa": "yokohama"}, {"yamanashi": "kofu"}] |
+---------------------------------------------------+

MariaDB

select JSON_ARRAYAGG(item) from sample;
+------------------------------------------------------------+
| JSON_ARRAYAGG(item)                                        |
+------------------------------------------------------------+
| ["{\"kanagawa\":\"yokohama\"}","{\"yamanashi\":\"kofu\"}"] |
+------------------------------------------------------------+

　3.JSON_OBJECTAGG

JSON_OBJECTAGGもJSON_ARRAYAGGと同様

MySQL

select json_objectagg(id,item) from sample;
+-------------------------------------------------------------+
| json_objectagg(id,item)                                     |
+-------------------------------------------------------------+
| {"1": {"kanagawa": "yokohama"}, "2": {"yamanashi": "kofu"}} |
+-------------------------------------------------------------+

MariaDB

select json_objectagg(id,item) from sample;
+---------------------------------------------------------------------+
| json_objectagg(id,item)                                             |
+---------------------------------------------------------------------+
| {"1":"{\"kanagawa\":\"yokohama\"}", "2":"{\"yamanashi\":\"kofu\"}"} |
+---------------------------------------------------------------------+

MariaDBでMySQL同等の結果を取得するためには

一応、SQLを工夫すればMariaDBでもMySQL同等の結果が取得できる。但し、項目値によってはkeyが重複するなどするため、JSONとしての正しさは保証されない。

JSON_ARRAYAGG

MariaDB

select CONCAT('[',GROUP_CONCAT(item),']') from yukisaki_database.sample;
+------------------------------------------------+
| CONCAT('[',GROUP_CONCAT(item),']')             |
+------------------------------------------------+
| [{"kanagawa":"yokohama"},{"yamanashi":"kofu"}] |
+------------------------------------------------+

JSON_OBJECTAGG

MariaDB

select CONCAT('{',GROUP_CONCAT(CONCAT('"',id,'":',item)),'}') from sample;
+--------------------------------------------------------+
| CONCAT('{',GROUP_CONCAT(CONCAT('"',id,'":',item)),'}') |
+--------------------------------------------------------+
| {"1":{"kanagawa":"yokohama"},"2":{"yamanashi":"kofu"}} |
+--------------------------------------------------------+

---

1. JSON Data Type - MariaDB Knowledge Base ↩