GoのsqlxパッケージにてJSON_CONTAINSを使ってSELECTしようとしてハマった話

はじめに

こんにちは、サーバサイドエンジニアの中川(@tkdev0728)です。
Yappliに入社して半年が経とうとしていて、時の流れは早いなと感じている今日この頃です。

さて、今回はタイトル通りとある業務にてJSONで格納されている値から特定のキーを抽出したいと思い、JSON_CONTAINSを使おうとしたらハマりました。
今後同じようなことで悩む方がいないことを願ってご紹介しようと思います。

やりたかったこと

id	user
1	{"age": "30", "name": "tanaka"}
2	{"age": "33", "name": "satou"}
3	{"age": "25", "name": "suzuki"}|

サンプルのためにだいぶ簡略化しましたが、上記のようなテーブルが存在したとします。今回やりたかったことは名前がsatouのレコードのidを取得することでした。
これをMySQLのクエリで実現しようとすると、下記のようなクエリになるかと思います。

select id from json_users where JSON_CONTAINS(user, '"satou"', '$.name');

クエリで実現できたので、これをGoで実装しようと思いました。

詰まった点

クエリで実行できたので実際のコードに反映させようと思い、下記のようなコードを作成しました。
※コピペしても動かないのでご注意ください

package main

import (
    "fmt"
    "log"

    "github.com/jmoiron/sqlx"
)

type IDs struct {
    id int64 `db:"id"`
}

func main() {
    //DBに接続する
    db, err := sqlx.Open("mysql", "root/sample")

    if err != nil {
        defer db.Close()
        log.Fatal(err)
    }

    res := make([]IDs, 5)

    // SQL実行
    err = db.Select(&res, "SELECT `id` FROM `json_users` where JSON_CONTAINS(`user`, ?, '$.name')", "'\""+"satou"+"\"'")
    if err != nil {
        log.Fatal(err)
    }

    defer db.Close()
    fmt.Println(&res)

}

上記のコードを実行したところ期待通りに動作せず、下記のようなエラーが返ってきていました。
Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0.

理由

クエリで実行した時には動くのになぜGoのコードで実行しようとするとエラーになるのかわからず、上記のエラー文が出る原因を調べていました。
色々調べてみると、文字列を検索対象としている場合、'"hoge"'のように検索対象を「"」で囲んだら解決した。との記事を発見しました。

stackoverflow.com

これはクエリを作成する際に私も気付いており、「"'\""+"satou"+"\"'"」のような形で囲んでいるのになぜ...と思っていました。実行されているクエリの引数部を確認すると、「'"satou"'」のように意図した記述になっているように思えます。ただ、この引数はプレースホルダでバインドしているため、もしかして「'"satou"'」をバインドする際に文字列として扱うために「"'"satou"'"」のようにまた「"」で囲われてしまっているのでは？と仮説を立てました。
そこで今度はクエリを実行した際に同様のエラーが再現するか調べるために、下記のクエリを実行しました。

※コピペしても動かないのでご注意ください
select id from json_users where JSON_CONTAINS(user, "'\"satou\"'", '$.name');.

すると同じエラーが再現できたので、ほぼほぼ仮説は正しそうだと判断しました。

解決策

JSON_CONTAINSで文字列を検索する際には「"」で囲む必要がある。また、プレースホルダでバインドしている関係で「"」も文字列として渡す必要もある。 ここまでわかれば解決策は浮かびました。
今回はfmt.Sprintfを使って「"satou"」の形式で渡すことにしました。コードとしては下記です。

package main

import (
    "fmt"
    "log"

    "github.com/jmoiron/sqlx"
)

type IDs struct {
    id int64 `db:"id"`
}

func main() {
    //DBに接続する
    db, err := sqlx.Open("mysql", "root/sample")

    if err != nil {
        defer db.Close()
        log.Fatal(err)
    }

    res := make([]IDs, 5)

    // SQL実行
    err = db.Select(&res, "SELECT `id` FROM `json_users` where JSON_CONTAINS(`user`, ?, '$.name')", fmt.Sprintf(`"%s"`, "satou"))
    if err != nil {
        log.Fatal(err)
    }

    defer db.Close()
    fmt.Println(&res)

}

これでやりたかったことが実現できました。

学びと反省

この一連の経験からの学びとして、当たり前ではありますが「クエリで動いたからといって、そのまま動くとは限らない」ということを学びました。同時に「クエリで動いたから動くでしょう」と動く理由を深掘りせず安易にコードとして記載したことについて反省しました。
この記事を読んだ人が自分の学びと反省を活かして下さることを祈っています。

最後に

ヤプリでは一緒に働く仲間を募集しています！「興味をもった！」という方や、「もう少し具体的な話が聞いてみたい」と思った方はぜひカジュアル面談にお越しください。