PostgreSQL®のJSON関数を使ってインドのレストランのレビューをナビゲートする

<font color="Grey">*[Using PostgreSQL® JSON functions to navigate reviews of restaurants in India](https://aiven.io/blog/pg-json-indian-restaurants)の翻訳です。*</font>

2022年11月8日

# PostgreSQL® JSON関数を使ってインドのレストランのレビューをナビゲートする

リレーショナルデータベースで半構造化データセットを解析するのは怖いように思えます。PostgreSQL®のJSON関数を使うことで、SQLクエリがjsonやjsonbのデータをどのように扱うことができるようになるかについては、こちらをお読みください。


## PostgreSQL® JSON関数を使ったインドレストランのレビュー分析


リレーショナルデータベースの元来の考え方は、「構造→データ」であり、コンテンツを挿入する前に、データがどのようなものであるかを定義する必要がありました。この厳密なデータ構造の定義は、データ型、参照整合性、および専用の制約を使用した追加のビジネス条件を検証することで、データセットの秩序を保つのに役立ちました。


しかし、人生には予測できないことがあり、データはさまざまな形をとることがある。ある種の柔軟性を可能にするために、PostgreSQL®のような最新のデータベースは、データの形状に関する形式的なチェックのみが行われる半構造化列オプションJSONを追加し始めました。


PostgreSQLはこの分野で、[`json`と`jsonb`](https://www.postgresql.org/docs/current/datatype-json.html)という2つのオプションを提供しています。最初のオプションは、コンテンツがJSON形式であることを検証し、文字列として格納します。詳細は[Stack Overflow](https://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql)を参照してください。


このブログポストでは、レストラン情報を含むデータセットを使って、いくつかのjsonb関数（jsonバージョンは末尾の`b`がないだけで本当によく似ている）について詳しく説明している。というわけで、私のように常に新しい料理を発見したいと思っている人は、シェフ調査員の帽子（ゴードン・ラムゼイの帽子とシャーロック・ホームズの帽子をミックスしたような帽子）を取って、私と一緒にインドへの架空の旅でおいしいレストランを探してみよう！


## PostgreSQL® インスタンスのデプロイ


基本的なことから始めましょう。JSONデータの解析機能を持つデータベースが必要です。PostgreSQLはそれに最適で、自分のものを使うか、[CLI](https://developer.aiven.io/docs/tools/cli.html)を使ってAivenでインスタンスを作成することができます：



```
AVN SERVICE CREATE DEMO-PG ￤ -サービスタイプ pg
 --サービスタイプ pg
 --plan hobbyist
 --cloud google-europe-west3`Copy to clipboard
```

上記は、ドイツのフランクフルトにある `google-europe-west3` クラウドリージョンに `demo-pg` という Aiven for PostgreSQL (`--service-type pg`) サービスを最低限の `hobbyist` プランで作成します。avn service wait` コマンドでサービスを監視することができる。


## PostgreSQL でレストランのデータを取得する。


どんな研究もデータセットから始まる！Kaggleで利用可能な[Zomato restaurants dataset](https://www.kaggle.com/datasets/shrutimehta/zomato-restaurants-data)という素晴らしいデータセットがあり、世界中の多くの都市のレストラン情報を含んでいる。


ダウンロードに必要なのは、[有効なKaggleログイン](https://www.kaggle.com/account/login?phase=startRegisterTab&returnUrl=%2F)だけです。ダウンロードしたら、アーカイブファイルを解凍し、5つのファイル（`file1.json`～`file5.json`）を含むフォルダを取得する。これらのファイルをAiven CLIを使ってPostgreSQLサービスにロードすると、接続文字列が取得され、`psql`クライアントが使用されます：



```
avn service cli demo-pg`Copy to clipboard
```

jsonb 型の `reviews_data` というユニークなカラムを持つ `rest_reviews` テーブルを作成する。



```
create table rest_reviews (reviews_data JSONB);``クリップボードにコピーする
```

でファイルをロードする：



```
copy rest_reviews from program 'sed -e ''s/e'' file1.json；
\¶copy rest_reviews from program 'sed -e ''s/e ''s/pedache/g'' file2.json'；
\Êcopy rest_reviews from program 'sed -e ''s//\/g'' file3.json'；
\Êcopy rest_reviews from program 'sed -e ''s//\/g'' file4.json'；
\copy rest_reviews from program 'sed -e ''s/json/g'' file5.json';`Copy to clipboard
```

ここでは、`copy`コマンドの問題である``文字を適切にエスケープするために、`sed -e ''s/↩/゙゙/g'' fileX.json`を使っている（答えをくれた[StackOverflow](https://stackoverflow.com/questions/43855930/postgresql-json-to-columns-error-character-with-value-must-be-escaped)に感謝する）。


先ほどアップロードしたファイルは、Zomato API のレスポンスを配列で含むネストされた JSON ドキュメントである：



```
`[
 {
 "results_found"：17151,
 "restaurants"：[
 {"restaurant"：{"name": "Hauz Khas Social",...}}、
 レストラン": { "name": "Qubitos - The Terrace Cafe",..：{"name": "Qubitos - The Terrace Cafe",...}}、
 ...
 },
 {
 "results_found"：100,
 "restaurants"：[
 {"restaurant"：{"name": "Spezia Bistro",...}}、
 レストラン": { "name": "Manhattan Brewery & Bar Exchange",：{"name": "Manhattan Brewery & Bar Exchange",...}}、
 ...
 },
 ...
 {"message"：「API limit exceeded", "code"：440, "status"：""}
 {"message"：API limit exceeded", "code": 440, "status": ""：440, "status": ""：""}
[]`クリップボードにコピーする
```

## jsonb_array_elements` を使ってレストランのリストを取り出す。


レストランのリスト（`restaurants`フィールド）にアクセスするには、以下のようにする必要がある：


* API レスポンスのリストを含む外側の配列を解析する。
* これは `API limit exceeded` エラーも取り除く。


以下のSQLクエリで実行できる：



```
select restaurant -> 'restaurant' ->> 'name' restaurant_name
from rest_reviews
 cross join lateral jsonb_array\_elements(reviews_data) dt
 cross join lateral jsonb_array_elements(dt -> 'restaurants') restaurant
limit 10;`クリップボードにコピー
```

上記では


* jsonb_array_elements` 関数を使用して jsonb 配列を解析する。
* jsonb_array_elements(reviews_data) dt` は、API レスポンスの外部配列である `dt` を返す。
* jsonb_array_elements(reviews_data) dt` は `dt` を返す。つまり


	+ jsonb_array_elements(dt -> 'restaurants') restaurant` は `restaurant` を返し、これは `restaurants` フィールドに含まれる配列である。
	+ そして、 `restaurant -> 'restaurants'` はレストランの配列から `restaurant` の jsonb 値を取得します。
* また、 `->` と同様に、 `->>` は jsonb のサブアイテムを取得するが、今回はテキストとして取得する。つまり、`'restaurant' ->> 'name'` はrestaurantのjsonbからフィールド `name` をテキストとして取得する。


上記のクエリを実行すると、データが正しくパースされていることがわかります。



```
 レストラン名
----------------------------------
ハウズ・カス・ソーシャル
キュビトス・ザ・テラス・カフェ
ハドソン・カフェ
サマーハウス・カフェ
38バラック
スペツィア・ビストロ
マンハッタン・ブリュワリー＆バー・エクスチェンジ
ザ・ワイン・カンパニー
ファージ・カフェ
インディアン・グリル・ルーム
(10列)`クリップボードにコピーする
```

同じようなクエリを使って、レストランごとに行を持つテーブルを作成することもできる。  

注：このステップではもっと多くのカラムを解析することができます。



```
としてテーブルrestaurant_dataを作成する。
select
 (restaurant -> 'restaurant' ->> 'id')::int id、
 restaurant -> 'restaurant' jsonb_data
from rest_reviews
 cross join lateral jsonb_array_elements(reviews_data) dt
 cross join lateral jsonb_array\_elements(dt -> 'restaurants') restaurant;`クリップボードにコピー
```

これで `restaurant_data` というテーブルができ、整数フィールド `id` と jsonb フィールド `jsonb_data` を持つようになった。


## レストランのデータを深く掘り下げる


さあ、調査を始めましょう。まずはいくつかのフィールドを調べてみましょう。id`と`name`の他に、`location`というJSONのサブ項目があり、そこでレストランの`city`を見つけることができる。



```
選択
 jsonb_data ->> 'id' id、
 jsonb_data ->> 'name' name、
 jsonb_data ->> 'location' ->> 'city' city
from restaurant_data limit 5;`クリップボードにコピー
```

上記で、jsonbのサブ項目を抽出するための`->`の使い方と、同じものをテキストとして抽出するための`->`の使い方をもう一度確認してみよう。結果は以下のようになる：



```
 ID｜名前｜都市名
---------+----------------------------+-----------
308322｜ハウズ・カス・ソーシャル｜ニューデリー
18037817｜キュビトス・ザ・テラス・カフェ｜ニューデリー
312345｜ハドソンカフェ｜ニューデリー
307490｜サマーハウス・カフェ｜ニューデリー
18241537｜38バラック｜ニューデリー
(5行)``クリップボードにコピー
```

### インドの物価トップは？を使用してデータをフィルタリングする


お金の話をしよう！インドの現地通貨ルピー（Rs.



```
選択
 jsonb_data ->> 'id' id、
 jsonb_data ->> 'name' name、
 (jsonb_data ->> 'average_cost_for_two')::int average_cost_for_two
from restaurant_data
where jsonb_data @> '{"currency"："Rs."}'
order by 3 desc
limit 5;`クリップボードにコピー
```

再び `int` を使って価格が整数であることを示し、`@>` 演算子を使って `jsonb_data` JSON ドキュメントに `{"currency"：「Rs."}` が含まれていることを確認する。別の方法としては、`currency` サブ項目を抽出して、`jsonb_data ->> 'currency' = 'Rs.'`でフィルタリングすることもできる。結果は以下のようになる：



```
 id | name | average_cost_for_two
--------+------------------------------------------------------+----------------------
3400072｜Dawat-e-Nawab - Radisson Blu｜3600
2300187｜ウォーターサイド｜ザ・ランドマーク・ホテル｜3000
3400059｜ペシャウリ｜ITCムガール｜2500
102216｜チャオ・チャイニーズ・ビストロ｜ホリデイ・イン・ジャイプール・シティ・センター｜2500
3400060｜タージ・バーノ｜ITCムガール｜2500
(5行)`クリップボードにコピーする
```

### with_bucket`で評価をチェックする


さて、上記のクエリでコストについてはわかったが、品質についてはどうだろうか？user_rating`の項目を調べて、ヒストグラムを作ってみよう：



```
with agg_bucket as (
 select
 width_bucket((jsonb_data -> 'user_rating' ->> 'aggregate_rating')::numeric, 0, 5, 10) bucket、
 count(˶*) nr_restaurants
 from restaurant_data
 where jsonb_data @> '{"currency"："Rs."}'
 group by width_bucket((jsonb_data -> 'user_rating' ->> 'aggregate_rating')::numeric, 0, 5, 10)
 )
select bucket、
 numrange(bucket*0.5 -0.5, bucket*0.5) range、
 nr_restaurants
from agg_bucket
order by 1;`クリップボードにコピー
```

上記のクエリは `with_bucket` 関数を使って、`user_rating` の値を10個のバケツのうちの1つに割り当てている。以下の結果は、`rating >= 4`を安全にフィルタリングし、なおかつ良いレストランを選択できることを示している。



```
バケット｜範囲｜nr_restaurants
-------+-----------+----------------
 5 | [2.0,2.5)| 2
 6 | [2.5,3.0)| 1
 7 | [3.0,3.5)| 88
 8 | [3.5,4.0)| 266
 9 | [4.0,4.5)| 287
 10 | [4.5,5.0)| 76
(6行)``クリップボードへコピー
```

### 手頃なレストランは？


出費をなるべく抑えるべき？rating`が4以上で、`average_cost_for_two`が1000インドルピー以下のレストランを検索してみよう。ここでも、フィルタを適用する前に `aggregate_rating` と `average_cost_for_two` の両方を整数にキャストしている。



```
選択
 jsonb_data ->> 'id' id、
 jsonb_data ->> 'name' name、
 (jsonb_data ->> 'average_cost_for_two')::int average_cost_for_two、
 (jsonb_data ->> 'user_rating' ->> 'aggregate_rating')::numeric aggregate_rating
from restaurant_data
where jsonb_data ->> 'currency' = 'Rs.
and (jsonb_data ->> 'average_cost\_for_two')::int < 1000
かつ (jsonb_data ->> 'user_rating' ->> 'aggregate_rating')::numeric >= 4
order by 4 desc, 3 asc
limit 5;`クリップボードにコピー
```

これで、高すぎず、でも美味しいレストランがかなり選りすぐられていることがわかる！



```
 id | name | average_cost_for_two | aggregate_rating
--------+---------------------------+----------------------+------------------
3400346｜シェローズ・ハングアウト｜0｜4.9
2600109｜Sagar Gaire Fast Food｜250｜4.9
800468｜トゥンデイ・カバビの孫｜300｜4.9
3001321｜ケーキビー｜350｜4.9
96776｜コンシュ｜600｜4.8
(5行)``クリップボードにコピー
```

### jsonb_array_length` と `jsonb_array_elements` を使って、イベント配列を深く掘り下げてみる。


その結果、イベントをやっているレストランはあるか？過去に行われたイベントをチェックすることで、そのレストランが何を提供できるのか、より深く知ることができるかもしれない。項目 `zomato_events` にはイベントの配列が含まれているので、その配列に少なくとも2つのエントリがあるレストランをチェックすることで、何が利用可能かを知ることができる。



```
選択する
 jsonb_data ->> 'id' id、
 jsonb_data ->> 'name' name、
 (jsonb_data ->> 'average_cost_for_two')::int average_cost_for_two、
 (jsonb_data ->> 'user_rating' ->> 'aggregate_rating')::numeric aggregate_rating、
 jsonb_array_length(jsonb_data -> 'zomato_events') nr_events
from restaurant_data
where jsonb_data ->> 'currency' = 'Rs.
and (jsonb_data ->> 'average_cost\_for_two')::int < 1000
and (jsonb_data ->> 'user_rating' ->> 'aggregate_rating')::numeric >= 4
and jsonb_array_length(jsonb_data -> 'zomato_events') > 1
order by 5 desc, 4 desc, 3 asc
limit 5;`クリップボードにコピー
```

zomato_events` をフィルタリングするために、特定の jsonb 配列の項目数を返す `jsonb_array_length` 関数を使用している。興味深いことに、3行しか得られない。



```
 id | name | average_cost | aggregate_rating | nr_events
---------+----------------+----------------------+------------------+-----------
103019｜ミューチュアルズ｜650｜4.2｜3
113537｜パフィッツァ｜700｜4.3｜2
18413814｜アンガンホライズン｜900｜4.0｜2
(3行)``クリップボードにコピー
```

さて、3つのレストランのリストができたので、最終的な決断を下すために、そのレストランがどのようなイベントを作ったか見てみよう。



```
選択
 jsonb_data ->> 'id' id、
 jsonb_data ->> 'name' name、
 (jsonb_data ->> 'average_cost_for_two')::int average_cost_for_two、
 (jsonb_data -> 'user_rating' ->> 'aggregate_rating')::numeric aggregate_rating、
 events -> 'event' ->> 'title' event_title
from restaurant_data
cross join lateral jsonb_array_elements(jsonb_data -> 'zomato_events') events
where jsonb_data ->> 'currency' = 'Rs.
and (jsonb_data ->> 'average_cost\_for_two')::int < 1000
and (jsonb_data ->> 'user_rating' ->> 'aggregate_rating')::numeric >= 4
and jsonb_array_length(jsonb_data -> 'zomato_events') > 1
order by 1,2,5
limit 5;`クリップボードにコピー
```

うわー、`#FlauntYourPizza Contest`は面白そうだ（そしておそらく非常に危険だ）！



```
 id | name | average_cost | aggregate_rating | event_title
-------+----------+----------------------+------------------+-----------------------------------
103019｜ミューチュアル｜650｜4.2｜ABHI SOLANKI LIVE
103019｜ミューチュアルズ｜650｜4.2｜FREEBIRDS
103019｜ミューチュアルズ｜650｜4.2｜IPLスペシャルオフ
113537｜パフィッツァ｜700｜4.3｜ディムライト・ディナー（毎週木曜日）
113537｜パフィッツァ｜700｜4.3｜#フラント・ユア・ピッツァ・コンテスト
(5行)`クリップボードにコピー
```

レストランが見つかったようだ！さあ、お店に行って、メニューをチェックして、食べよう！


## 結論


「リレーショナルデータベースは堅すぎる」、この言葉を何度聞いたことでしょう。特にPostgreSQLにはJSONオブジェクトを操作するための深い関数群があります。ですから、今度JSONデータセットがあったら、[PostgreSQLを試してみる](https://aiven.io/postgresql)というのはどうでしょう？


他にも役に立つリソースがあります：


* [PostgreSQL JSON関数](https://www.postgresql.org/docs/current/functions-json.html)
* Json vs jsonb、[違いは何ですか](https://www.postgresql.org/docs/current/datatype-json.html)
* [Aiven for PostgreSQLを始める](https://aiven.io/postgresql)