● 記事 ●

RailsでPostgreSQLのjsonbを扱う

更新日2022/03/10 投稿日2022/03/03

#開発 #Rails

PostgreSQLのjsonb型をRailsで！

まず、jsonbとはなんぞや？
json-binaryのことらしい。
jsonを正規化してDBに保存するとか。
空白や重複キーを排除するとか(重複は後の方が適用されるとか)。

まぁ、詳しいことは知らん。
各自調査するように。

それで、railsでjsonbを扱おうって話。
railsはjsonbに対応してるので、migrationファイルにはそのままかける。
ginとかいうindexを貼ると良いとか。

じゃあ、本題！

★ jsonbのarrayをquery

Postgresのjsonb用のSQLが特殊なんだよね。

例えばこんなデータがあったとする。

table: hoges
column: id(integer), fuga(jsonb)

record
id: 1

fuga:

{
　　"data": "member",
　　"list": [
     {"name":"takeshi", "type":"A"},
     {"name":"nobita", "type":"B"},
    ]
}

こんなデータをRails(ActiveRecord)でいろいろ取得してみる。

1. 単純に取得

1.1. "data"の取得

Hoge.find(1).fuga["data"]

これで"member"が取得できる(はず)

1.2. "list"の取得

Hoge.find(1).fuga["list"]

これで[{"name":"takesh", "type": "A"}, {"name": "nobita", "type": "B"}]
が取得できる(はず)

1.3. fuga["list"][0]["name"]の取得

Hoge.find(1).fuga["list"][0]["name"]

これで"takeshi"が取得できる(はず)

2. 検索してみる

2.1. "data" = "member"の検索

Hoge.where("fuga ->> 'data' = ?", "member")

なんか変なのでてきた...

"fuga ->> 'data' = ?" とは...
まず、fugaの部分。これはjsonb型のcolumn: fugaを指している。
次に ->> 'data' これ。fugaの中のdataをtextとして取得している(何言ってんだ？)
->> という演算子は指定したkeyに対応するvalueをtextとして取得する。
そして = で"member"と比較している(?はプレースホルダ。第二引数"member"が入る)

Hoge.where("fuga ->> 'data' LIKE ?", "%emb%")

とかすればLIKE検索できる。

要は、fuga内のkey: "data"を取得して"member"と比較してる。

2.2. {"name": "takeshi", "type": "A"}の検索

Hoge.where("fuga -> 'list' @> ?", {name:"takeshi", type:"A"}.to_json)

また変なのでてきた。
@> は左のヤツに右のヤツが含まれているか?っていう演算子。
さらに今回は ->> ではなく -> 。これは"list"をjson形式で取得するよってこと。
基本的に >> がテキスト、 > がjsonとして取得するってこと。
で、list(json)に{"name":"takeshi","type":"A"}(json)が含まれるか?ってこと。

2.3. "takeshi"の検索
これ難しかった。どうやるのって感じだった。

失敗例(NG):

Hoge.where("fuga -> 'list' ->> 'name' = ?", "takeshi")

これだと、listが配列(Array)なのでNG。

微妙な例(NG):

Hoge.where("fuga -> 'list' -> 0 ->> 'name' = ?", "takeshi")

確かに取れるよ。でも違う。list[0]って指定したら意味ない。

で、調べたら
jsonb_array_elementsなる関数を見つけた。
こいつは配列の中身を展開してくれるみたいだ。
よし！

失敗例(NG):

Hoge.where("jsonb_array_elements(fuga -> 'list') ->> 'name' = ?", "takeshi")

だめだった...whereの中でjsonb_array_elementsは使えないよエラーがでた。

失敗例(NG):

Hoge.select("jsonb_array_elements(fuga -> 'list') as elem").where("elem ->> 'name' = ?", "takeshi")

elemなんてcolumn無いよ。馬鹿なの？ってエラーがでました。

じゃあ、そろそろ成功例を。

成功例(Success):

Hoge.from("hoges, jsonb_array_elements(fuga -> 'list') elem").where("elem ->> 'name' LIKE ?", "%keshi%")

これで成功しました。(ちゃっかりLIKE検索にしてみてる)

.fromに入れた。

.from("hoges, ...")

これ忘れないで。.fromを使うと本来のformを上書きしちゃうので、
ちゃんと、検索したいテーブルも指定すること。
あと、as は無くてもいいみたい。

やったぜ。

コメントがありません。