regexp_matches, array per row

user_17079163 0
Viewed 25

In PostgreSQL, I have a table as follow.

|    id    |     mycolumn   |
| -------- | -------------- |
| 1        | /1/2/8/        |
| 2        | /1/40/22/11/   |
| 3        | /1/15/35/      |

I am trying to get all the numbers, for each row, in an array. So far I came up with the following:

    SELECT array_agg(matchx)
    FROM (
        SELECT unnest(regexp_matches(mycolumn,
                                     '[0-9]+',
                                     'g')) matchx
        FROM mytable
    ) x

What I want:

|  array_agg   |
| ------------ |
| {1,2,8}      |
| {1,40,22,11} |
| {1,15,35}    |

What I get:

|        array_agg              |
| ----------------------------- |
| {1,2,8,1,40,22,11,1,15,35}    |

How is it possible to get an array per row?

postgresql arrays string
1 Answers

No need for unnesting, just turn it into an array directly:

select id, string_to_array(trim(both '/' from mycolumn), '/')
from mytable;

The trim() is necessary, because otherwise you'd have empty elements in the array.

Your original approach would work if you group while aggregating back:

SELECT id, array_agg(matchx)
FROM (
    SELECT id, 
           unnest(regexp_matches(mycolumn,'[0-9]+','g')) matchx
    FROM mytable
) x
group by id;
user_3303150
Related