PostgreSQL text型配列のパターンマッチングのやり方

テキスト型配列ではそのままパターンマッチングができない。

テキスト型配列でパターンマッチングをするには、UNNEST関数でテキスト型配列のネストを解除し、ネスト解除したものに対してパターンマッチングを行う。

実際にやってみる。

まずテキスト型配列をもつテーブルを作成し、適当な行を挿入する。

テキスト型配列を挿入する際は、'{}'で囲む。

CREATE TABLE table1 (
    "id" integer,
    nest_data text[]
);

INSERT INTO table1 VALUES (1, '{abc, 123, XYZ}');
INSERT INTO table1 VALUES (2, '{ttt, 543, BCD}');

作成されたテーブルを表示すると、ちゃんとネストされている。

SELECT id, nest_data FROM table1;

id  unnested_data
1   {abc,123,XYZ}
2   {ttt,543,BCD}

nest_data列をUNNEST関数でネスト解除するとこんな感じになる。

SELECT id, unnested_data FROM 
(SELECT id, UNNEST(nest_data) "unnested_data" FROM table1 ) as t;

id  nest_data       unnested_data
1   {abc,123,XYZ}   abc
1   {abc,123,XYZ}   123
1   {abc,123,XYZ}   XYZ
2   {ttt,543,BCD}   ttt
2   {ttt,543,BCD}   543
2   {ttt,543,BCD}   BCD

あとはネスト解除した列でパターンマッチングしつつ、ネストされた列のみを取り出せばうまいこと抽出できる。

SELECT id, nest_data FROM 
(SELECT id, nest_data, UNNEST(nest_data) "unnested_data" FROM table1) as t
WHERE unnested_data LIKE '1%';

id  nest_data
1   {abc,123,XYZ}