Using PostgreSQL® JSON functions to navigate reviews of restaurants in Indiaの翻訳です。

2022年11月8日

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

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

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

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

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

PostgreSQLはこの分野で、jsonとjsonbという2つのオプションを提供しています。最初のオプションは、コンテンツがJSON形式であることを検証し、文字列として格納します。詳細はStack Overflowを参照してください。

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

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

基本的なことから始めましょう。JSONデータの解析機能を持つデータベースが必要です。PostgreSQLはそれに最適で、自分のものを使うか、CLIを使って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という素晴らしいデータセットがあり、世界中の多くの都市のレストラン情報を含んでいる。

ダウンロードに必要なのは、有効なKaggleログインだけです。ダウンロードしたら、アーカイブファイルを解凍し、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に感謝する）。

先ほどアップロードしたファイルは、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を試してみるというのはどうでしょう？

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