SQLをつかってJSONで格納されたデータを検索してみた|sitateru tech blog

シタテルの技術やエンジニアの取り組みを紹介するテックブログです。

2019年9月9日月曜日

SQLをつかってJSONで格納されたデータを検索してみた

こんにちは!

シタテルでエンジニアをしている建山です。

今回は、データベース(MySQL)にJSON形式で格納されているデータをSQLで検索する方法を紹介します。
シタテルでは、AWS上のデータベースに蓄積されたデータをredashというツールを使い、SQLでデータ抽出したあと、データ分析できるようになっています。

やりかた

今回はMySQLの


たとえば、格納データが以下の2レコードあったとします。

{"maxCount":1000,"targets":{"ladies":false,"mens":true,"kids":false,"baby":false}}

{"maxCount":50,"targets":{"ladies":true,"mens":false,"kids":false,"baby":false}}


ここで、maxCountが50のデータを検索したいときは、
JSON_EXTRACTを使用し、以下のように抽出できます。(テーブル名:table_a、JSON格納カラム名:detail)

select * from table_a where JSON_EXTRACT(detail, '$.maxCount') = 50


targetsのladiesがtrueの検索をしたいときは、以下のようになります。

select * from tablename where JSON_EXTRACT(detail, '$.targets.ladies') = true


# 感想
このように、```$.

のあとに、キーの名前を指定してあげれば、かんたんに、jsonが格納してあるカラムの中身を検索することができます。(表示の記述方法も同じ)

公式のドキュメントはこの辺あたりのようです。
https://dev.mysql.com/doc/refman/5.7/en/json.html

他にもいくつか関数があるようなので、いろいろ試してみたら、いいSQL+JSONライフが送れるかも知れません!

簡単ではありましたが、ご紹介でした!