如何根据postgresql9.4中的第一个索引获取数组元素的第二个索引值？

I have table called arraytable -> create table arraytable(id int, somearray int[][])

我有一个名为arraytable的表 - > create table arraytable（id int，somearray int [] []）

INSERT INTO arraytable(id, somearray) values(1,array[[3,5],[4,12]]);
INSERT INTO arraytable(id, somearray) values(2,array[[7,15],[13,47],[15,27],[18,97]]);
INSERT INTO arraytable(id, somearray) values(3,array[[56,1],[67,78],[105,78]]);

I have no idea how to select second index values of array elements in all rows according to particular first index values of array elements;

我不知道如何根据数组元素的特定第一索引值在所有行中选择数组元素的第二索引值;

First, I want to select 6 array elements that have first index values smaller than 67 which would look like:

首先，我想选择6个第一个索引值小于67的数组元素，如下所示：

[4,12],[7,15],[13,47],[15,27],[18,97],[56,1]

And now I need to select second index values of these which would look like:

现在我需要选择这些的第二个索引值，如下所示：

12, 15, 47, 27, 97, 1.

1 个解决方案

#1

This is so ugly I'm sure there is a better way to do this but since nobody has answered this question, I'll post this answer bearing in mind that I don't think it's a good solution and a stable one. Don't use this in production! It's merely an exercise for me with my very limited knowledge about how multidimensional arrays work in Postgres.

这太丑了我确信有更好的方法可以做到这一点，但由于没有人回答这个问题，我会发布这个答案，记住我不认为这是一个很好的解决方案和一个稳定的解决方案。不要在生产中使用它！对于我对Postgres中多维数组如何工作的非常有限的了解，这对我来说只是一个练习。

It's just for the sake of answering:

这只是为了回答：

with x as (
  select foo.id, goo.nr, goo.first_ind, foo.somearray 
  from (
    select *, somearray[1:array_upper(somearray,1)][1] AS first_indexes 
    from arraytable
  ) foo, 
  unnest(foo.first_indexes) WITH ORDINALITY goo(first_ind,nr) 
  where goo.first_ind < 67
) 
select string_agg(z.second_ind::text, ', ' order by x.id, x.nr) 
from x 
join (
  select * 
  from (
    select id, first_ind, somearray[1:array_upper(somearray,1)][2:3] AS second_indexes 
    -- [2:3] should actually be [2] but for some reason it wouldn't work? so this is specific to data with 2 indexes
    from x
  ) y, 
  unnest(y.second_indexes) WITH ORDINALITY goo(second_ind,nr)
) z on 
  x.id=z.id 
  and x.nr=z.nr 
  and x.first_ind=z.first_ind;

Output:

输出：

     string_agg
--------------------
5, 12, 15, 47, 27, 97, 1
(1 row)

Also, {3,5} should be taken into consideration so 5 should be present in the output.

此外，应考虑{3,5}，因此输出中应存在5。

#1