ScalikeJDBC + circe で MySQL JSON 型を扱う

これはScala Advent Calendar(Qiita)の4日目の記事です。
昨日はOE_uiaさんの「Scala関西サミットでAndroid Akkaについて喋ってきた話」でした。

MySQL 5.7からJSON型が扱えるようになったらしく、これをScalaで読み取ってみよう、という試み。

今回使用するコード全体は以下の通り。

build.sbt

scalaVersion := "2.12.0"

libraryDependencies ++= Seq(
  "io.circe" %% "circe-core" % "0.6.1",
  "io.circe" %% "circe-generic" % "0.6.1",
  "io.circe" %% "circe-parser" % "0.6.1",
  "org.scalikejdbc" %% "scalikejdbc" % "2.5.0",
  "org.scalikejdbc" %% "scalikejdbc-syntax-support-macro" % "2.5.0",
  "mysql" % "mysql-connector-java" % "6.0.5"
)

initialCommands := """
import scalikejdbc._
implicit val session = AutoSession

Class.forName("com.mysql.cj.jdbc.Driver")
ConnectionPool.singleton("jdbc:mysql://192.168.99.100:3305/json_test?useSSL=true", "root", "password")

import io.circe._, io.circe.generic.auto._, io.circe.parser._, io.circe.syntax._

import com.zaneli.jsonrdb.JsonRDB
import com.zaneli.jsonrdb.JsonRDB._

val b = Book.syntax("b")
"""

com.zaneli.jsonrdb.JsonRDB.scala

package com.zaneli.jsonrdb

object JsonRDB {
  import io.circe.Json
  import io.circe.parser._
  import scalikejdbc._

  case class Book(id: Long, content: Json)

  object Book extends SQLSyntaxSupport[Book] {
    override val tableName = "books"
    override val columns = Seq("id", "content")

    def apply(rn: ResultName[Book])(rs: WrappedResultSet): Book = autoConstruct(rs, rn)
  }

  implicit val json: TypeBinder[Json] = TypeBinder.option[String].map(_.flatMap(parse(_).toOption).getOrElse(Json.Null))

  implicit class JsonSQLSyntax(val syntax: SQLSyntax.type) extends AnyVal {
    def jsonExtract(column: SQLSyntax, path: String): SQLSyntax = {
      sqls"json_extract($column, $path)"
    }
    def jsonMerge(column: SQLSyntax, json: Json): SQLSyntax = {
      sqls"json_merge($column, ${json.noSpaces})"
    }
  }
}

まずはMySQLの環境を用意する。Dockerでサクッとできて便利。

docker pull mysql:5.7

docker run -p 3305:3306 -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=json_test --rm mysql:5.7

テーブルを用意してレコードを作成する。

MySQL [json_test]> create table books (id int not null primary key auto_increment, content json null);
Query OK, 0 rows affected (0.01 sec)

MySQL [json_test]> insert into books (content) values (json_object('name','Scala beginner','author','zaneli2','date','2016-12-05')),
    -> (json_object('name','Scala professional','tag','programming','date','2016-12-04')),
    -> (json_object('name','foo','author','zaneli','date','2016-12-10')),
    -> (json_object('name','bar','author','zaneli','date','2016-12-01'));
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL [json_test]> select * from books;
+----+----------------------------------------------------------------------------+
| id | content                                                                    |
+----+----------------------------------------------------------------------------+
|  1 | {"date": "2016-12-05", "name": "Scala beginner", "author": "zaneli2"}      |
|  2 | {"tag": "programming", "date": "2016-12-04", "name": "Scala professional"} |
|  3 | {"date": "2016-12-10", "name": "foo", "author": "zaneli"}                  |
|  4 | {"date": "2016-12-01", "name": "bar", "author": "zaneli"}                  |
+----+----------------------------------------------------------------------------+
4 rows in set (0.00 sec)

selectしてみる。contentはList[io.circe.Json]で取れる。

scala> val books = withSQL { select.from(Book as b) }.map(Book(b.resultName)).list.apply()
books: List[com.zaneli.jsonrdb.JsonRDB.Book] =
List(Book(1,{
  "date" : "2016-12-05",
  "name" : "Scala beginner",
  "author" : "zaneli2"
}), Book(2,{
  "tag" : "programming",
  "date" : "2016-12-04",
  "name" : "Scala professional"
}), Book(3,{
  "date" : "2016-12-10",
  "name" : "foo",
  "author" : "zaneli"
}), Book(4,{
  "date" : "2016-12-01",
  "name" : "bar",
  "author" : "zaneli"
}))

scala> val contents = sql"select content from books".map(_.get[Json]("content")).list.apply()
contents: List[io.circe.Json] =
List({
  "date" : "2016-12-05",
  "name" : "Scala beginner",
  "author" : "zaneli2"
}, {
  "tag" : "programming",
  "date" : "2016-12-04",
  "name" : "Scala professional"
}, {
  "date" : "2016-12-10",
  "name" : "foo",
  "author" : "zaneli"
}, {
  "date" : "2016-12-01",
  "name" : "bar",
  "author" : "zaneli"
})

JSON型用の関数を使ってみよう。
json_extract関数を使ってcontentに"author"が含まれてるレコードのnameをdateでソートしてみる。

scala> val names = withSQL {
     |   select(sqls.jsonExtract(b.content, "$.name"))
     |     .from(Book as b)
     |     .where.gt(sqls.jsonExtract(b.content, "$.author"), 1)
     |     .orderBy(sqls.jsonExtract(b.content, "$.date"))
     | }.map(_.string(1)).list.apply()
names: List[String] = List("bar", "Scala beginner", "foo")

json_merge関数を使ってJSONをマージしてみる。

scala> case class Coauthor(author: String, country: String)
defined class Coauthor

scala> val c = Coauthor("znl", "jp")
c: Coauthor = Coauthor(znl,jp)

scala> withSQL {
     |   update(Book)
     |     .set(Book.column.content -> sqls.jsonMerge(Book.column.content, c.asJson))
     |     .where.eq(Book.column.id, 3)
     | }.update.apply()
res0: Int = 1

MySQL [json_test]> select * from books where id = 3;
+----+-------------------------------------------------------------------------------------+
| id | content                                                                             |
+----+-------------------------------------------------------------------------------------+
|  3 | {"date": "2016-12-10", "name": "foo", "author": ["zaneli", "znl"], "country": "jp"} |
+----+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ふむふむ。

(実用性があるかはともかく)他のJSONを扱うMySQL関数もこんな感じでJsonSQLSyntaxに追加して使ったりできそうですね。

なお、今回のネタはScalaもくもく会 @ セプテーニオリジナル vol.1で書きました。
セプテーニさんありがとうございました。

明日はKuchitamaさんの「ServerlessFramework + Scala でなんか書きます」です。