How to get array index when doing unnest in PGSQL-CodePudding

I need to fetch array index when doing unnest. I tried row_number() Over() but this is calculating row number, not the array index. I also tried WITH ORDINALITY but it worked only with from clause.

select id, unnest(fname), status 
from testunnest;

Rows in Table testunnest

112w,{john,sam},yes  
2wew,{josh,Nick,Jeny},no

The above query is returning result as

112w    john    yes  
112w    sam     yes  
2wew    josh    No  
2wew    Nick    No  
2wew    Jeny    No

Expected is

112w    john    yes 1  
112w    sam     yes 2  
2wew    josh    No  1  
2wew    Nick    No  2  
2wew    Jeny    No  3

CodePudding user response：

use the unnest() function in the from clause, then you can add the with ordinality option which returns the array index

select t.id, u.name, t.status, u.idx
from testunnest t
  cross join unnest(t.fname) with ordinality as u(name, idx);

In general it's recommended to use set-returning functions in the FROM clause anyway.