関数の改良（ⅰ）JSONを処理してpageidを直接返す

昨日作ったPL/v8のストアド関数の使用例を再掲 ↓ します。キーワードを配列で渡し、それぞれの語でWikipediaの記事タイトルを検索し（完全一致のみ）結果をJSONで返却。検索にはMediaWiki APIを使い、結果のJSONも同APIの出力のまま。記事があればJSONの中にpageidがありますが、検索語がキーでなく逆にpageidがキーなので扱いづらいです。特に複数のキーワードで検索した時は。

select plv8_MediaWiki_ex1('{東京,大阪,札幌}');

plv8_mediawiki_ex1
{"url":"https://en.wikipedia.org/w/api.php?action=query&format=json&titles=%E6%9D%B1%
E4%BA%AC%7C%E5%A4%A7%E9%98%AA%7C%E6%9C%AD%E5%B9%8C","response":{"batchcomplete":"","q
uery":{"pages":{"2126632":{"pageid":2126632,"ns":0,"title":"東京"},"3211539":{"pageid
":3211539,"ns":0,"title":"大阪"},"3211578":{"pageid":3211578,"ns":0,"title":"札幌"}}}
}}
(1 row)

そこでまず ↓ こんな風に改良&簡素化してみました。ストアド内部でJSONを処理し [ {検索語: pageid}, {検索語: pageid}, ...} ] というオブジェクトの配列にして返します。

create or replace function plv8_MediaWiki_ex2(wiki_titles text[])
returns json language plv8 immutable as $$
    var url = 'https://en.wikipedia.org/w/api.php?action=query&format=json&titles='
            + encodeURIComponent(wiki_titles.join('|')),
        sql = "select convert_from(plpy_urlopen('" + url + "'), 'utf8')",
        res = plv8.execute(sql),
        objTmp = JSON.parse(res[0]['convert_from']);

    if (!objTmp) return null;

    var obj = objTmp['query']['pages'],
        pids = Object.keys(obj),
        resAry = [];

    // search a pageid matching each title
    for (var i = 0; i < wiki_titles.length; i++) {
        var titleTarget = wiki_titles[i];
        for (var j = 0; j < pids.length; j++) {
            var pidCand = pids[j],
                objCand = obj[ pidCand ];
            if (objCand['title'] === titleTarget) {
                var resObj = {};
                resObj[titleTarget] = pidCand;
                resAry.push(resObj);
                break;
            }
        }
    };
    return resAry;
$$;

↓ 三つの検索語での使用例。検索語で完全一致する記事があれば当該pageidだけを返すシンプルな形です。渡した検索語の順番と、返ってくるJSON配列の順番は一致します。

select plv8_MediaWiki_ex2('{東京,大阪,札幌}');

+------------------------------------------------------+
|                  plv8_mediawiki_ex2                  |
+------------------------------------------------------+
| [{"東京":2126632},{"大阪":3211539},{"札幌":3211578}] |
+------------------------------------------------------+
(1 row)

記事が存在しない検索語は、pageid : -1が返ります。（さらにある場合は-2, -3,…と続く）

select plv8_MediaWiki_ex2('{東京,大阪,札幌幌}');

+---------------------------------------------------------+
|                   plv8_mediawiki_ex2                    |
+---------------------------------------------------------+
| [{"東京":"2126632"},{"大阪":"3211539"},{"札幌幌":"-1"}] |
+---------------------------------------------------------+
(1 row)

ネットワーク非接続の場合などWebAPIから結果を取得できない場合、実行エラーでなくNOTICEを出してNULLが返ります。これは1月4日の記事でウェブアクセス用の関数を作った際の工夫によるもの。同関数はPL/Pythonのストアドなので、NOTICEに付くCONTEXTにPL/Python function...と出ます。

select plv8_MediaWiki_ex2('{東京,大阪,札幌}');

NOTICE:  URLError or HTTPError. failed to reach a server
CONTEXT:  PL/Python function "plpy_urlopen"
SQL statement "select convert_from(plpy_urlopen('https://en.wikipedia.org/w/api.php?a
ction=query&format=json&titles=%E6%9D%B1%E4%BA%AC%7C%E5%A4%A7%E9%98%AA%7C%E6%9C%AD%E5
%B9%8C'), 'utf8')"
+--------------------+
| plv8_mediawiki_ex2 |
+--------------------+
|                    |
+--------------------+
(1 row)

改良したものの、テーブルの複数行に対しては使いにくい

実際には最初から検索語の配列があるというより、何らかのテーブルの列を検索語として考え、複数行に対し一括検索したい場面が多いのではないかと。そうなると前項で改良したストアドもまだまだ使いづらいです。例えばサンプルテーブルとして ↓ こんなデータで一括検索する場合。

create table cities as
select *
from unnest('{東京,大阪,札幌幌,仙台,広島,北九州,福岡}' :: text[]) as city;

select * from cities;
+--------+
|  city  |
+--------+
| 東京   |
| 大阪   |
| 札幌幌 | -- 検索結果なしのテスト用
| 仙台   |
| 広島   |
| 北九州 |
| 福岡   |
+--------+
(7 rows)

検索語の配列はarray_agg関数で簡単に作れます。それをストアドに渡せばMediaWiki APIの検索結果が返り、検索語の順番と同じでJSON配列に。↓ が実際のクエリ。ここまでは良いとして、問題は結果を複数行にバラす（元テーブルの検索語の列の右側に検索結果を付けるような）処理が手間なこと。

-- 見やすくするため、整形オフ＆列を縦に並べて表示
\pset format unaligned
\pset expanded on

select ary, plv8_MediaWiki_ex2(ary)
from (
    select array_agg(city) as ary
    from cities
) foo;

ary|{東京,大阪,札幌幌,仙台,広島,北九州,福岡}
plv8_mediawiki_ex2|[{"東京":"2126632"},{"大阪":"3211539"},{"札幌幌":"-1"},{"仙台":"32
11582"},{"広島":"3211568"},{"北九州":"16326328"},{"福岡":"9336564"}]

一応やってみると ↓ こんな感じ。検索語はarray_agg()で配列にし、最後はunnest()で行にバラす。検索結果はJSON配列で返ってくるのでjson_array_elements()で行にバラし、pageidの本来の型である整数にキャスト。とまぁ煩雑なクエリで、検索語が多くなるほど「配列に入れる」「行にバラす」の負荷も増えます。

-- 表示設定を元に戻した
\pset format aligned
\pset expanded off

with a (ary1) as (
    select array_agg(city) from cities
), b (ary2) as (
    select plv8_MediaWiki_ex2(ary1) from a
), c (city, json) as (
    select unnest(ary1), json_array_elements(ary2)
    from a, b
)
select city, (json->>city) :: int as pageid
from c;

+--------+----------+
|  city  |  pageid  |
+--------+----------+
| 東京   |  2126632 |
| 大阪   |  3211539 |
| 札幌幌 |       -1 |
| 仙台   |  3211582 |
| 広島   |  3211568 |
| 北九州 | 16326328 |
| 福岡   |  9336564 |
+--------+----------+
(7 rows)

せっかくPL/PythonとPL/v8の環境構築をしストアドを作ったのに、実際使う段でクエリが非効率になってしまっては台無し。かと言ってテーブル1行ずつに対してWebAPIを叩く原始的な方法ではウェブアクセスの負荷が検索語数に比例して増えるし、途中で接続が切れた等の場合のエラー処理も難題。何とか、MediaWikiのように複数の検索語を一度に送れるAPIを上手くPostgreSQLで使いたい。

そこで次に、ウィンドウ関数を自作して「ウェブアクセスは1回に集約し、結果はテーブルの各行に対して付ける」方法を考えます。PL/v8の、ウィンドウ関数を手軽に作れるメリットを最大限に享受。詳細は明日書きます。