MySQL5.6でもJSONしたい

はじめに

先日、たまたましまい込んでいたMySQL5.6のDBを使う機会があったので、備忘録のメモです。
5.6ではJSON型はサポートされていませんでしたが、アプリの都合でテキストでJSONを保存していたものがありました。
これに対して、アドホックにSQLで検索したのでせっかくなので残しておきます。

MySQLとJSON

MySQL5.7からJSON型がサポートされましたが、5.6ではJSON型はサポートされていません。
しかし、ただのテキストとしてJSONを保存することは可能です。
例えば以下のようなテーブルを考えます。

meta_dataテーブル

id	meta_json
1	{"lang": "ja", "timezone": 9, "type": "normal"}
2	{"lang": "en", "timezone": -5, "use": 2}

MySQL5.7の場合

このように、JSONをテキストとして保存している場合、MySQL5.7 以降であればJSON関数を使って検索することができます。
MySQL5.7以降であれば、以下のようなクエリでtextで保存されていてもJSON型にキャストすることでtimezoneを簡単に抽出することができます。

SELECT json_extract(CAST(meta_json AS JSON), '$.timezone') FROM meta_data;

MySQL5.6の場合

しかし、MySQL5.6ではJSON型がサポートされていないので、JSON関数を使うことができません。
そのため、JSONをテキストとして保存している場合、JSONの中身を抽出するには、正規表現を使う必要があります。

SELECT
  REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(meta_json, '"lang":', -1), '}', 1), '"', '') AS lang,
  SUBSTRING_INDEX(SUBSTRING_INDEX(meta_json, '"timezone":', -1), '}', 1) AS timezone
FROM  meta_data;

JSONのValueが数値であれば、そのまま数値として取り出すことができますが、文字列であれば、"で囲まれているので、"を取り除く処理が必要です。
また、JSONの末尾の項目の場合、,がないので、SUBSTRING_INDEXで取り出す際には注意が必要です。
末尾のものの場合は以下のようにする必要があります

SELECT
  REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(meta_json, '"type":', -1), ',', 1), '"', ''), '}','') AS type
FROM  meta_data
WHERE meta_json LIKE '%"type":%';

おわりに

以上、MySQL5.6でテキスト保存したJSONの中から手する方法についてのメモでした。

5.7ですら昨年には延長サポートも切れているので、今後ほとんど使う機会がないとは思いますが備忘録でした。

あくまでこの例は１階層しか無いJSONの場合の例でしたが、 いつかもし私のようにどうしても必要になった人の助けになれば幸いです。