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。差は全くない。

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

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

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

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

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

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

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

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

##　3.JSON_OBJECTAGG
JSON_OBJECTAGGもJSON_ARRAYAGGと同様

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

```sql: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

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

##JSON_OBJECTAGG

```sql: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](https://mariadb.com/kb/en/json-data-type/)