Select regular expression groups in PostgreSQL-CodePudding

I have to do a select based on a regular expression and return some values of the expression as columns.

I managed to do something similar to this

select
(regexp_matches(value, '...', 'g'))[1] as expressionValue1,
(regexp_matches(value, '...', 'g'))[2] as expressionValue2,
(regexp_matches(value, '...', 'g'))[5] as expressionValue3
from table

I am trying to rewrite the query to something like this

select 
    (resultsq)[1] as expressionValue1,
    (resultsq)[2] as expressionValue2,
    (resultsq)[3] as expressionValue3
from (
    select
    regexp_matches(value, '...', 'g') 
    from table
) resultsq

However, I get a syntax error. I think the result text[] is being transformed to text.

I want to do it this way because I understand that the regular expression is evaluated only one time per tuple and the code is clearer.

The regular expression that I have to apply is relatively long and complex and there are many fields to extract.

CodePudding user response：

    (resultsq)[1] as expressionValue1,
    (resultsq)[2] as expressionValue2,
    (resultsq)[3] as expressionValue3 ```

You are referencing the subquery alias resultsq rather than the field name, hence array indexing on it doesn't work. The subquery is returning a set of rows with a field named regexp_matches, so reference that in your statement and it will work.

select 
    (regexp_matches)[1] as expressionValue1,
    (regexp_matches)[2] as expressionValue2,
    (regexp_matches)[3] as expressionValue3

https://www.db-fiddle.com/f/g6zdgdo7Jv6x2boL4NKi9m/0