これは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してみる。contentList[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 でなんか書きます」です。

Copyright© 2011-2016 Shunsuke Otani All Right Reserved .