MySQLでのJSONデータの扱い方を、いつか試してみなきゃなぁと思い始めて2年以上が経っているのですが、ようやく、触ってみたいと思うJSONデータに出会ったので、重い腰を上げて試してみました。当方、JSON初心者ですので、このエントリは「お勉強日記」です。
JSONデータ
先日はじめて、COCOAアプリからの接触確認通知が来ました。このデータはJSON形式のデータとして取り出せるということで、これが今回扱うターゲットデータとなります。
手順としては、
- COCOAアプリで「情報を保存」ボタンを押して、データを取り出し（私は手軽にメールで自分宛に送信する方法を採りました）
- 取り出したテキストファイルが JSON 形式となっています。一応、テキストエディタで内容確認
- MySQL (8.0.29)にテーブルを作成し、INSERT。
今回作成したテーブル：
create table j1 (id integer, j json);
データの登録方法：
INSERT INTO j1 VALUES (1, '(ここにJSONの内容をべたっと(複数行))');
今回のJSONデータ（冒頭部分）：
今回の日記の方針・範囲
今回の日記(1)では、MySQLのJSON関数のうち、シンプルに結果が返ってくるものを適当に試してみることにします。自分の見たいデータを取り出して加工して・・・というのは(2)のほうでやります。また、今回は (1)(2) ともに更新系は扱いません。取り出すだけ。
JSON_DEPTH()
JSONの入れ子の深さを返す関数。
今回のデータが、元のテキストファイルで6000行以上あり、ちょっと目視だとわからなかった（深度4程度かなと思っていた）けど、どこかに6の深さの部分がきっとあるのでしょう（適当）。
mysql> select json_depth(j) from j1; +---------------+ | json_depth(j) | +---------------+ | 6 | +---------------+
JSON_LENGTH()
JSONの「長さ」を返す関数だそうです。
mysql> select json_length(j) from j1; +----------------+ | json_length(j) | +----------------+ | 10 | +----------------+
6000行もあるのに10ってホントかよ、と当初思ったのですが、要素の繰り返し部分をカットしてみたら、確かにトップレベル（と言って正しいんですかね）の要素は 10個でした。「略」とした部分に大量のデータが含まれています。
{
"platform": "iOS",
"platform_version": "15.5",
"model": "iPhone13,2",
"device_type": "Physical",
"app_version": "2.0.1",
"build_number": "1653370657",
"en_version": "2",
"daily_summaries": [ ...(略)... ],
"exposure_windows": [ ...(略)... ],
"exposure_configuration": { ...(略)... }
}
JSON_KEYS()
上記 JSON_DEPTH() では数値が得られたのに対し、JSON_KEYS()は、その内容をJSONの配列にして返してくれる。順序は、どうもキー名の文字列の長さが短い順であるように見える（同じ長さの場合は collation に従う）。
mysql> select json_keys(j) from j1; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | json_keys(j) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ["model", "platform", "en_version", "app_version", "device_type", "build_number", "daily_summaries", "exposure_windows", "platform_version", "exposure_configuration"] | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
JSON_TYPE()
JSONデータのタイプを返します。ひとえにJSONデータと言っても、ちょっと複雑なOBJECTやシンプルな配列 ARRAY があるらしい。
mysql> select json_type(j) from j1; +--------------+ | json_type(j) | +--------------+ | OBJECT | +--------------+
引数には、JSONデータの部分を与えることもできて、その場合は OBJECTやARRAYのほか、抽出したデータの型を返してくれる。以下の場合は、文字列なので、STRING。
mysql> select json_type(JSON_EXTRACT(j, '$.app_version')) jtype from j1; +--------+ | jtype | +--------+ | STRING | +--------+
JSON_VALID()
JSONとしてどうよ？というのを判定して返してくれる。「1」ならばイイカンジ。まぁ、今回の実行例だと、テーブルに登録できている時点で VALID な JSON であることが分かっているので、この関数は引数として即値（JSON文字列）を与えて使うものなのかな。
mysql> select json_valid(j) from j1; +---------------+ | json_valid(j) | +---------------+ | 1 | +---------------+
JSON要素へのアクセス方法の基本
JSONデータの後ろに ->"" を付けて、""の中に要素を指定する文字列を記述する。
JSONデータのトップ（ルート）が $ で、以降、要素名を深さに応じてドットでつなげていく。配列の要素は [n] （nは正の整数）で指定。
mysql> select j->"$.build_number" from j1; +---------------------+ | j->"$.build_number" | +---------------------+ | "1653370657" | +---------------------+
mysql> select j->"$.daily_summaries[2].ConfirmedTestSummary" from j1; +-----------------------------------------------------------------------+ | j->"$.daily_summaries[2].ConfirmedTestSummary" | +-----------------------------------------------------------------------+ | {"ScoreSum": 60.0, "MaximumScore": 60.0, "WeightedDurationSum": 60.0} | +-----------------------------------------------------------------------+
また、->を使うと、文字列注で \ エスケープする文字がそのまま含まれているのに対して、代わりに ->> を使用するとそれらが省かれるとのこと（引用符も除かれる）。
（ただし SQL モード NO_BACKSLASH_ESCAPES が指定されている場合は動作が異なる）
配列は、以下のようにひとつの要素を指定することもできるし、
mysql> select j->"$.daily_summaries[3].ConfirmedTestSummary.ScoreSum" ss from j1; +-------+ | ss | +-------+ | 840.0 | +-------+
以下のように、「to」を使って、範囲を指定することもできる。最終要素は数値の代わりに[last]を指定する。
mysql> select j->"$.daily_summaries[3 to 5].ConfirmedTestSummary.ScoreSum" ss from j1; +---------------------+ | ss | +---------------------+ | [840.0, 60.0, 60.0] | +---------------------+
mysql> select j->"$.daily_summaries[last].ConfirmedTestSummary.ScoreSum" ss from j1; +------+ | ss | +------+ | 60.0 | +------+
