PostgreSQL : 配列を配列として取り出したい
はじめての技術メモです。
今まで雑魚実装をしていたことに気づいたので、覚え書き。
本来はタイトル通り、技術関係のことを書こうかなと思ってたけど、書くほどのことをしなかったので、ようやく記事が書けた。
PostgreSQLから配列を取り出す
例えばこういうテーブルを対象とする。
product_id | tag |
---|---|
101 | 書籍 |
101 | エッセイ |
102 | DVD |
102 | アニメ |
102 | アイカツ |
こういったテーブルから、下記のような形式のデータを抽出したい。
product_id | array_tag |
---|---|
101 | {書籍,エッセイ} |
102 | {DVD,アニメ,アイカツ} |
よくある(?)、正規化されたテーブルをいい感じにするやつ(表現しにくい)。
これをサブクエリにして、商品データとJOINするとかだろう。
これ自体は、下記のようなクエリでサックリいく。
SELECT product_id, array_agg(tag) AS array_tag FROM tags GROUP BY product_id
array_agg() はバージョン8.4以降から使える。
でもこれだとまだちょっと足りない。
正確で堅い手法でパースしたい
上記のクエリを発行した結果では、PostgreSQL内では配列だったとしても、受け取る言語の実装によっては文字列になる。
文字列が返った場合、パースして配列にするのがめんどくさい。
メタ的な意味ではなくデータ自体に波括弧があったら?カンマがあったら?
単純に波括弧を除外してカンマでsplitするだけでは全然ダメ。
たとえRFCを熟知していようが、自分でこういうの書くのは絶対ダメ。顧客が思い通りにデータを入れると思うな。
もしこれがJSONやXMLであれば、大概の言語は基本実装なりライブラリがあるので、このクエリを発行したモジュールの言語で対応することができる。
array_agg の結果をJSONにする
PostgreSQL バージョン9.3 からは、json_aggという集約関数がある。
array_aggのjson版となっている。
PostgreSQL内では9.2からjson型が存在するので、json型だが、PHPに返ってくるのは当然文字列。
まあjsonだしね。
これをjson_decodeでデコードしてやれば、解決する。
クエリ
SELECT product_id, json_agg(tag) AS json_tag FROM tags GROUP BY product_id
PHP実装例
<?php // $result に上記クエリの結果が入っていると考えて foreach($result as $record){ $productId = $record['product_id']; $decodedRecord = json_decode($record['json_tag']); foreach($decodedRecord as $tag){ //なんか処理 } }
バージョン9.2での実装
上記はバージョン9.3 でのカンタンな実装だが、json実装当初の9.2でも、同様のことは実現できる。
筆者はjson_aggを知らなかったのでこっちの方法でやっていた。
PostgreSQL内で配列からjsonに変換することができるから、array_aggの結果をjsonに変換すればいいだけ。
SELECT product_id, array_to_json(array_agg(tag)) AS json_tag FROM tags GROUP BY product_id
経験上こういう整形は出来る限りのところまでDBにやらせたほうが速いからDBに生きがいを与える。
応用する
下記のページにバージョン9.4系でのjson関係の関数が一覧になっている。
PostgreSQLでjsonをこねくりまわして変態になろう!
JSON関数と演算子 : https://www.PostgreSQL.jp/document/9.4/html/functions-json.html