How to get array index when doing unnest in PGSQL

Question

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

user330315 · Accepted Answer · 2022-01-12 19:39:08Z

9

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.

answered Jan 12, 2022 at 19:39

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get array index when doing unnest in PGSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related