PostgreSQL で json_array_elements を使って、JSON データ（オブジェクトの配列）を行として扱う方法

はじめに

これは PostgreSQL Advent Calendar 2016 の 12日目の記事です。

この記事では、私が PostgreSQL を使うことにしていてほんとよかったぁ、と思った件について書きます。

背景

今回のネタは JSON データ型に対するデータ分析です。

一般論として、JSON のようなゆるいデータ型を使うことはあまりデータベース設計上、良いこととされていません。
JSON データ型を使うと、テーブル設計が明確ではなくなってしまいます。
また、データ設計が正規化されなくなってしまいますので、冗長なデータの持ち方になってしまいます。

とはいえ、開発上、事前に適切なデータ設計をすることは難しいことがあります。
JSON データ型を使うことによって、なんでもそこで格納させることができますので、
ついつい開発のとき使ってしまいます。そして、あとで技術的負債となってしまったりします。

私が開発にかかわっているエネチェンジ でも JSON データ型を使っている箇所があります。

例えば、ユーザが選んだプラン情報を JSON データ型に格納しています。

初期の実装としては、最後に選んだプラン情報のみを格納していました。

しかし、一度あるプランについて詳細ページを閲覧したとしても、また別の詳細ページを見るというユーザ行動が当然ありえます。
さまざまな詳細ページを表示したとき、そのユーザ行動を追えるようにしたいというニーズが発生しました。

このとき、従来のコードを大きく変更せずに対応できるようにするために、JSON 型を採用しました。
JSON 型を採用することで、短期の開発スケジュールでちゃんと動くコードを書くことができ、よかったのですが、
その後、どのような詳細ページが特に見られているか分析したいというニーズが発生しました。
そういうデータ分析を行うには、JSON 型は不向きです。

しかしながら、エネチェンジでは PostgreSQL を採用していました。
なんとかできます。PostgreSQL だから。

前提となるデータ構造

ユーザの電気料金シミュレーションを行った実績は try_histories テーブルに格納されます。

電気料金シミュレーションを行ったあと、どのプランの詳細ページに遷移したかの情報が chosen_plans 列にあります。

chosen_plans 列の内容は例えば下記のようになっています。

1行目の chosen_plans の内容

 [
  {"provider_key":"showa-shell","plan_key":"home_sss","area_key":"tepco"},
  {"provider_key":"showa-shell","plan_key":"sss_denki","area_key":"tepco"},
  {"provider_key":"huistenbosch","plan_key":"mamatoku","area_key":"tepco"}
]

2行目の chosen_plans の列の情報

[
  {"provider_key":"huistenbosch","plan_key":"tokyo_b","area_key":"tepco"},
  {"provider_key":"mc-retail-energy","plan_key":"kihon","area_key":"tepco"}
]

この場合、どの電力会社のどのエリア、どのプランが最初に選ばれ、次にどの電力会社のどのエリア、どのプランが選ばれたのか、ということを分析したいというのが今回のニーズです。

この JSON データが別のテーブルになっていれば、簡単に集約関数などを使って分析できます。
しかしながら、すでに JSON データ型で設計してしまっています。
こういう場合でも、なんとかできる。
それが PostgreSQL のいいところです。

記述した SQL

下記のような SQL を作成しました。

WITH
 recent_histories AS (
   SELECT
     *
   FROM
     try_histories
   ORDER BY id DESC
   LIMIT 2
  ),
  chosen_plans_json AS (
    SELECT
      id,
      t.chosen_plan,
      t.idx
    FROM
      recent_histories AS rh,
      LATERAL
        json_array_elements(rh.chosen_plans)
        WITH ORDINALITY AS t(chosen_plan, idx)
  ),
  chosen_plans AS (
    SELECT
      id,
      idx,
      chosen_plan->>'provider_key' AS provider_key,
      chosen_plan->>'plan_key' AS plan_key,
      chosen_plan->>'area_key' AS area_key
    FROM
      chosen_plans_json
  )
SELECT
  *
FROM
  chosen_plans
; 

この SQL で、得られた結果は下記のとおりです。

id | idx | provider_key | plan_key | area_key
--------+-----+-----------------------+------------------------+-------------
965255 | 1 | showa-shell | home_sss | tepco
965255 | 2 | showa-shell | sss_denki | tepco
965255 | 3 | huistenbosch | mamatoku | tepco
965251 | 1 | huistenbosch | tohoku_b | tepco
965251 | 2 | mc-retail-energy | kihon | tepco

この SQL で使われている技術を順に説明します。

共通テーブル式 ( Common Table Expressions )

共通テーブル式は非常に知名度が高いテクニックなので、多くの方がご存じかと思います。
PostgreSQL 8.4 と比較的昔から利用可能です。

共通テーブル式が登場する前は、FROM 以下にサブクエリをネストして記述したりしていました。
その場合、最初に使われるものほどネストの奥の方に書かれていることとなります。
非常に複雑となり、読みにくい SQL となっていました。

また、共通テーブル式の登場する前は CREATE TEMPORARY TABLE を使う手法もよく使われていました。
一時テーブルを使う方法は最初に使うテーブルを最初に記述できるので、読みやすさは向上するものの、
一度しか使わないテーブルに対してテーブル定義などを行うのは大げさで、面倒でした。

共通テーブル式に関する詳細は、公式リファレンスを参照ください。7.8. WITH問い合わせ（共通テーブル式）

非常に複雑なクエリを簡単な部品に分解して分かりやすく記述できます。

LATERAL ジョイン

上記の SQL では LATERAL という見慣れないキーワードが使われています。

LATERAL は PostgreSQL 9.3 でサポートされました。
LATERAL を使うことで、その前に書かれた副問合せ（この場合 try_histories ）の結果を
LATERAL の後ろの副問合せ（この場合 json_array_elements 関数）で参照できます。

LATERAL は SQL でできるforeach 文とよく説明されます。
LATERAL を使うことによって、その前の副問合せの各行の値を参照するような処理をより簡単に
高速に実行することができます。

詳しくは、笠原さんの 2015年の PostgreSQL Advent Calendar の3日目の記事 LATERALを使ってみようを参照ください。

なお、実は今回の LATERAL は省略できます。
今回のように LATERAL の後ろにあるのが関数であれば
LATERAL 的な処理が必要であることは自明なので省略できるのです。

json_array_elements関数

json_array_elements 関数を使うことで、もともと 1行に、 JSON の配列があったのを展開して、配列の要素数分の要素が並べることができます。

PostgreSQL の unnest 関数をご存じな方にとっては、それのJSON 版と説明すると分かりやすいかもしれません。
PostgreSQL 9.3 で導入されました。

WITH ORDINALITY

WITH ORDINALITY は SQL 標準にある機能です。
PostgreSQL 9.4 から利用可能になりました。

SELECT 文の FROM 節にある複数行を返す関数に指定して行番号を付加することができます。

JSON 型の ->> 演算子

JSON型の ->> 演算子を使うことによって JSON オブジェクトのあるキーに対応する値を文字列として取得することができます。

今回の例では、->> 演算子を必要な数、使うことによって JSON のキーを列としてアクセスできるようにしています。

おわりに

きちとしたスキーマ定義をせず、JSON 型で一旦定義したテーブル構造に対して、データ分析が必要になったような場合でも、
最新の PostgreSQL の機能を使うことで、分析しやすい形で出力することができました。

PostgreSQL の実力をもってすれば本来 JSON 型のようなゆるいデータ構造のデメリットとされるような点さえ乗り越えることができます。

参考文献

• stackoverlow: How to convert JSON Array of Arrays to columns and rows
• 9.15. JSON関数と演算子
• 2015年の PostgreSQL Advent Calendar の3日目の記事 LATERALを使ってみよう