# MariaDB の JSON_SET, JSON_INSERT, JSON_REPLACE とかで JSON を使う方法

# はじめに: mariadb で　JSON を使う

mariadb で json 型の column attr を持つ table j を作って、attr に例えば{"a": 10, "b": {"c": 20}} みたいな json を登録します

MariaDB [json_test]> create table j (id int auto_increment primary key, attr json, check(json_valid(attr))); 
Query OK, 0 rows affected (0.095 sec)
MariaDB [json_test]> insert into j values (NULL, '{"a": 10, "b": {"c": 20}}');
Query OK, 1 row affected (0.031 sec)

MariaDB [json_test]> select * from j;
+----+---------------------------+
| id | attr                      |
+----+---------------------------+
|  2 | {"a": 10, "b": {"c": 20}} |
+----+---------------------------+
1 row in set (0.001 sec)

これは attr の中身は json_extract を使って get することができます

MariaDB [json_test]> select json_extract(attr, "$.a") from j;
+---------------------------+
| json_extract(attr, "$.a") |
+---------------------------+
| 10                        |
+---------------------------+
1 row in set (0.002 sec)

MariaDB [json_test]> select json_extract(attr, "$.b") from j;
+---------------------------+
| json_extract(attr, "$.b") |
+---------------------------+
| {"c": 20}                 |
+---------------------------+
1 row in set (0.002 sec)

MariaDB [json_test]> select json_extract(attr, "$.b.c") from j;
+-----------------------------+
| json_extract(attr, "$.b.c") |
+-----------------------------+
| 20                          |
+-----------------------------+
1 row in set (0.002 sec)

# issue: json_set で json を使うと、引用符がバックスラッシュでクオートされてしまって json として操作できなくなる

attr の json の "b" を {"c": 20} から　{"d": {"e": 100}} とかに置き換えてみます

MariaDB [json_test]> update j set attr=json_set(attr, "$.b", '{"d": {"e": 100}}');
Query OK, 1 row affected (0.033 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [json_test]> select * from j;
+----+-----------------------------------------+
| id | attr                                    |
+----+-----------------------------------------+
|  2 | {"a": 10, "b": "{\"d\": {\"e\": 100}}"} |
+----+-----------------------------------------+
1 row in set (0.001 sec)

二重引用符にバックスラッシュがついてて嫌な感じです。まるで "b" の中身が json ではなく単なる文字列だと言われてるみたいです（そう言ってるのですが ^^;)
ためしに $.b.d をとりだそうとしても

MariaDB [json_test]> select json_extract(attr, "$.b.d") from j;
+-----------------------------+
| json_extract(attr, "$.b.d") |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set (0.001 sec)

もちろんとれません ^p^/

"$.b" を unquote して、それの "$.d" を取るという work around はあるのでしょうが、なんともうざいです

MariaDB [json_test]> select json_extract(json_unquote(json_extract(attr, "$.b")),"$.d") from j;
+-------------------------------------------------------------+
| json_extract(json_unquote(json_extract(attr, "$.b")),"$.d") |
+-------------------------------------------------------------+
| {"e": 100}                                                  |
+-------------------------------------------------------------+
1 row in set (0.002 sec)

# なにが問題なのか

JSON型というものが実際には存在せず、実態はたんなる LONGTEXT な SQL の JSON の扱いは、文脈によって文字列を json として処理したり文字列として処理したりと日和見をしてくださるのが、状況によって態度かえるオポーチュニストみたな、モードによって処理をかえる vi みたいなウザさがあります
今の問題は json_set がどうも第三引数を素の文字列と見て勝手にエスケープしてくださってるのが原因っぽいです、ソースみてないので憶測ですが

これが例えば SQLite だと、その名もずばり json() という関数 (opens new window)があって、これを使うことで文字列を json として利用することができます

update j set attr=json_set(attr, "$.b", json('{"d": {"e": 100}}'));

でも mariadb には json() がありません

# 正解？

json-functions (opens new window) をじっくり眺めて、文字列を受け取ってそのまま json として返してくれそうな関数をさがします。json_value って名前からしてそれっぽいので json_value(json_str, "$") とかやってみたのですが NULL でした ^^;

で、正確に言うと受け取った文字列に何もしないでjsonとして返しているわけではないのですが、json_compact を使ってみると結果としてうまくいきましたのでこちらでご報告させていただきます次第です

MariaDB [json_test]> update j set attr=json_set(attr, "$.b", json_compact('{"d": {"e": 100}}'));
Query OK, 1 row affected (0.027 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [json_test]> select * from j;
+----+-----------------------------------+
| id | attr                              |
+----+-----------------------------------+
|  2 | {"a": 10, "b": {"d": {"e": 100}}} |
+----+-----------------------------------+
1 row in set (0.001 sec)

ちゃんと "d" も "e" も取ることができます

MariaDB [json_test]> select json_extract(attr, "$.b.d") from j;
+-----------------------------+
| json_extract(attr, "$.b.d") |
+-----------------------------+
| {"e": 100}                  |
+-----------------------------+
1 row in set (0.001 sec)

MariaDB [json_test]> select json_extract(attr, "$.b.d.e") from j;
+-------------------------------+
| json_extract(attr, "$.b.d.e") |
+-------------------------------+
| 100                           |
+-------------------------------+
1 row in set (0.001 sec)

やったね！

# 感想

昨夜、詰まって諦めて寝てしまい、夢の中でまだ考えてて（私の場合、諦めて寝たときにしつこく夢の中で同じ問題がでてきて考えることがしょっちゅうで、夢で正解にたどり着くことが多いので、寝ないで考えるということはしないでさっさと寝ます）ふと気がついて、起きて試したらうまくいきました
昨夜ググったときはなにも見つからなかったので、おなじように困る人のためにこちらに残しておきますね、ってかその人も諦めて寝てしまえば思いつく内容なのかもしれませんが

というか、mariadb にも json() 欲しい希ガス。MySQL ばっか気にしてないで SQLite のことも見てほしいかもしれない

← mariadb