I want the lexemes of a tsvector array placed in a column, one per row. I can first unnest the array and then the resulting tsvectors with:
SELECT (unnest(unnest(my_array))).lexeme FROM my_table WHERE id = 1;
lexeme ---------- foo bar baz ...
I noticed, however, that if I try to do the same in the FROM clause:
SELECT lexeme FROM unnest( (SELECT unnest(my_array) FROM my_table WHERE id = 1) );
ERROR: more than one row returned by a subquery used as an expression
So, I end up doing this:
SELECT lexeme FROM ( SELECT (unnest(col1)).lexeme FROM ( SELECT unnest(my_array) FROM my_table WHERE id = 1 ) AS t(col1) ) AS t2;
And I get the original result. But this is verbose. So, I’d like to know:
Why does unnest() require a single input row in the FROM clause, but doesn’t in the SELECT clause?
Is there a more concise, less convoluted way than my code, to get the column of lexemes in the FROM clause?
Is there a difference in performance between unnesting in the SELECT clause, versus doing it in the FROM clause?