I have an array ARRAY[10,20,30,40,50,60] and another array with of indexes ARRAY[1,2,3] I would like to create a sql query to get values with index array. For example:
SELECT * FROM ARRAY[10,20,30,40,50,60] WHERE <index> = ANY(ARRAY[1,2,3]);
Output of this query would be:
[20, 30, 40];
In Postgres, the array element index starts from 1, not 0.
You may do UNNEST of the main array with ORDINALITY and then use it in the where clause.
select array_agg(elem order by idx) from
unnest (ARRAY[10,20,30,40,50,60])
with ordinality as a(elem,idx)
where idx = ANY(ARRAY[1,2,3]);
this yields {10,20,30}
