MySQL 5.7のJSON機能を試してみる(MySQL Server Blogより)

免責事項

この記事はMorgan Tocker氏によるMySQL Server Blogの投稿「Taking the new MySQL 5.7 JSON features for a test drive」(2016/3/2)をユーザが翻訳したものであり、Oracle公式の文書ではありません。

MySQL 5.7からネイティブのJSON型と、サーバーサイドでデータを操作や検索を自然に行えるようにする一連のSQL関数が使えるようになりました。本日、SF OpenDataにあるサンプルデータを使って、これらの機能の簡単な利用例をご紹介したいと思います。

サンプルデータをインポートする

良いサンプルデータを使うことは、結果が正確であることを自身で確認するのに役立つため、とても重要なことです。良いデータは、データの分布にも優れており、これはインデックスを追加する際にも重要です。

私はSF OpenDataから「Geographic Locations and Boundaries」のデータの中で一番よく使われるものを選びました。これにはおよそ20万件の都市区画データが含まれます。初めのステップはダウンロードしてMySQLにインポートすることです。

# 最初にデータをダウンロード
# 途中段階を保存するために、フォーマットされてない状態のJSON形式で取得
cd /tmp
curl -O https://raw.githubusercontent.com/zemirco/sf-city-lots-json/master/citylots.json
grep "^{ \"type" citylots.json > features.json

# MySQLにインポート
CREATE TABLE features (
 id INT NOT NULL AUTO_INCREMENT,
 feature JSON NOT NULL,
 PRIMARY KEY (id)
);

LOAD DATA INFILE '/tmp/features.json' INTO TABLE features (feature);

ここにそれぞれの地物(feature、土地の一区画)がどのようなものであるかの例を示します。

{
   "type":"Feature",
   "geometry":{
      "type":"Polygon",
      "coordinates":[
         [
            [-122.42200352825247,37.80848009696725,0],
            [-122.42207601332528,37.808835019815085,0],
            [-122.42110217434865,37.808803534992904,0],
            [-122.42106256906727,37.80860105681814,0],
            [-122.42200352825247,37.80848009696725,0]
         ]
      ]
   },
   "properties":{
      "TO_ST":"0",
      "BLKLOT":"0001001",
      "STREET":"UNKNOWN",
      "FROM_ST":"0",
      "LOT_NUM":"001",
      "ST_TYPE":null,
      "ODD_EVEN":"E",
      "BLOCK_NUM":"0001",
      "MAPBLKLOT":"0001001"
   }
}

このケースでは全20万件のデータが共通のフォーマットですが、これは必須ではないことを指摘しておきます。JSONはスキーマレスです :)

クエリーの例

クエリー 1: サンフランシスコのメインストリートの1つであるマーケットストリートの土地区画を検索する

SELECT * FROM features 
WHERE feature->"$.properties.STREET" = 'MARKET'
LIMIT 1\G
************************* 1. row *************************
     id: 12250
feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}}
1 row in set (0.02 sec)

JSON_EXTRACT演算子の短縮演算子である -> を使うことでJSON列に対して自然にクエリを発行しています。"$.properties.STREET"の書き方はいわゆるJSONパスで、javascriptに馴染みがある方々にとっては、JQueryで利用するCSSセレクターと似たもの、と表現するのが良いと思います。

JSONパスの文法についての詳細は、マニュアルを確認いただくか、Roland Bouman氏のブログ投稿をご確認下さい。

クエリー 2: 通りの指定がない土地区画を検索する

SELECT * FROM features 
WHERE feature->"$.properties.STREET" IS NULL
LIMIT 1\G
Empty set (0.39 sec)

JSONはスキーマレスなので、期待したデータ構造になっていないものが検索できます。この例では、すべてのデータが$.properties.STREETを持っており、クエリーの結果が0件となっていることがわかります。

JSON型とTEXT型を比較する

この例では意図的に全20万件のJSONデータにアクセスするクエリを実行しています。これはマイクロベンチマークで、多くの場合インデックスを保持するプロダクション環境とは異なるものです。

# as JSON type
SELECT DISTINCT
 feature->"$.type" as json_extract
FROM features;
+--------------+
| json_extract |
+--------------+
| "Feature"    |
+--------------+
1 row in set (1.25 sec)

# as TEXT type
ALTER TABLE features CHANGE feature feature LONGTEXT NOT NULL;
SELECT DISTINCT
 feature->"$.type" as json_extract
FROM features;
+--------------+
| json_extract |
+--------------+
| "Feature"    |
+--------------+ 
1 row in set (12.85 sec)

何が起きたかをもう少し詳細にご説明しましょう。

• 単純化するために、両方のデータセットがメモリ上に保持されるようにしました。
• json_extract()演算子の短縮演算子である -> を含むJSON関数は、ネイティブのJSONデータ型だけでなく、TEXT/BLOB/VARCHARデータ型でも利用できます。これはとても有用で、なぜならMySQL 5.7以前のバージョンで既にJSONデータを格納しているユーザーが、うまくアップグレードできるようになるからです。
• ネイティブのJSON型はTEXT型より10倍以上高速であることがわかります(1.25秒と12.85秒)。これはネイティブのJSON型はデータのパースやバリデーションが不要であるため、JSONドキュメントを効率的に操作できるためである、と説明されます。

まとめ

JSONデータをインポートしていくつかのサンプルクエリを実行する有用な例として、本記事がみなさんの役に立つことを願います。次の投稿では、次のステップとして仮想列を利用してJSONデータにどのようにインデックスを作成するかをご紹介したいと思います。