How to find the index of of value in array in postgresql?

Question

I have this table and some sample data as well. I want to get the index of each value in array in separate column.

CREATE TABLE contacts (
   id serial PRIMARY KEY,
   name VARCHAR (100),
   phones TEXT []
);

Sample data.

INSERT INTO contacts (name, phones)
VALUES
  (
      'John Doe',
      '{"(408)-589-5846","(408)-589-5555"}'
   ),   
   (
      'Lily Bush',
      '{"(408)-589-5841"}'
   ),
   (
      'William Gate',
      '{"(408)-589-5842","(408)-589-58423"}'
   );

Now I run this query to unnest the data into rows which is something like this.

select name, unnest(phones) from contacts

It gives me the data correctly but I want the number of index for the phone numbers in another column which will help me identify which phone number is at which index. I came to know with array_position() function but it's not working as expected and throwing some error, maybe I'm not putting in right way. I am new to postgresql so any help would be appreciated.

user330315 · Accepted Answer · 2020-01-14 11:45:10Z

2

Use unnest() in the FROM clause and you get the the index using the option with ordinality

select c.name, 
       p.phone,
       p.idx
from contacts c
  cross join lateral unnest(phones) with ordinality as p(phone, idx)
order by c.id, p.idx;

Online example

The above would not return rows from the contacts table that have an empty phones array, if you need that you need to use a LEFT JOIN

select c.name, 
       p.phone,
       p.idx
from contacts c
  left join lateral unnest(phones) with ordinality as p(phone, idx) on true
order by c.id, p.idx;

answered Jan 14, 2020 at 11:45

user330315

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

Collectives™ on Stack Overflow

How to find the index of of value in array in postgresql?

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