postgresql – Difference between unnest() in SELECT vs unnest() in FROM on Postgres

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;

which gives:


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)

I get:

ERROR: more than one row returned by a subquery used as an expression

So, I end up doing this:

SELECT lexeme
    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:

  1. Why does unnest() require a single input row in the FROM clause, but doesn’t in the SELECT clause?

  2. Is there a more concise, less convoluted way than my code, to get the column of lexemes in the FROM clause?

  3. Is there a difference in performance between unnesting in the SELECT clause, versus doing it in the FROM clause?