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

Question

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:

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)
);

I get:

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:

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?

Answer 1

You're not really supposed to use set-returning operators in the select, even though PostgreSQL allows it.

It's much better to place them in the from and use a lateral join

SELECT lexeme
FROM my_table t,
  lateral unnest(array_column) AS vector,
  lateral unnest(vector)
WHERE t.product_id = 1;