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であれば、大概の言語は基本実装なりライブラリがあるので、このクエリを発行したモジュールの言語で対応することができる。

今回は、JSONにして、PHPでパースする。

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