How to Pattern Match on PostgreSQL text[] Arrays

Pattern matching cannot be performed directly on text array columns.

To pattern match on a text array, use the UNNEST function to unnest the text array, then perform pattern matching on the unnested result.

Let's try it out.

First, create a table with a text array column and insert some sample rows.

When inserting a text array, surround it with '{}'.

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

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

Displaying the created table shows that the data is properly nested.

SELECT id, nest_data FROM table1;

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

Unnesting the nest_data column with the UNNEST function looks like this:

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

Then, by applying pattern matching on the unnested column while selecting only the original nested column, you can extract the desired rows.

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}