Given: {{1,"a"},{2,"b"},{3,"c"}}
Desired:
鉴于：{{1，“a”}，{2，“b”}，{3，“c”}}所需：
foo | bar
-----+------
1 | a
2 | b
3 | c
You can get the intended result with the following query; however, it'd be better to have something that scales with the size of the array.
您可以使用以下查询获得预期结果;但是，最好有一些随阵列大小缩放的东西。
SELECT arr[subscript][1] as foo, arr[subscript][2] as bar
FROM ( select generate_subscripts(arr,1) as subscript, arr
from (select '{{1,"a"},{2,"b"},{3,"c"}}'::text[][] as arr) input
) sub;
2 个解决方案
#1
1
Not sure what exactly you mean saying "it'd be better to have something that scales with the size of the array". Of course you can not have extra columns added to resultset as the inner array size grows, because postgresql must know exact colunms of a query before its execution (so before it begins to read the string).
不确定你究竟是什么意思说“拥有随阵列大小缩放的东西会更好”。当然，随着内部数组大小的增加，你不能在结果集中添加额外的列，因为postgresql在执行之前必须知道查询的确切colunms（所以在它开始读取字符串之前）。
But I would like to propose converting the string into normal relational representation of matrix:
但我想建议将字符串转换为矩阵的正常关系表示：
select i, j, arr[i][j] a_i_j from (
select i, generate_subscripts(arr,2) as j, arr from (
select generate_subscripts(arr,1) as i, arr
from (select ('{{1,"a",11},{2,"b",22},{3,"c",33},{4,"d",44}}'::text[][]) arr) input
) sub_i
) sub_j
Which gives:
这使：
i | j | a_i_j
--+---+------
1 | 1 | 1
1 | 2 | a
1 | 3 | 11
2 | 1 | 2
2 | 2 | b
2 | 3 | 22
3 | 1 | 3
3 | 2 | c
3 | 3 | 33
4 | 1 | 4
4 | 2 | d
4 | 3 | 44
Such a result may be rather usable in further data processing, I think.
我认为，这样的结果可能在进一步的数据处理中可以使用。
Of course, such a query can handle only array with predefined number of dimensions, but all array sizes for all of its dimensions can be changed without rewriting the query, so this is a bit more flexible approach.
当然，这样的查询只能处理具有预定数量维度的数组，但是可以在不重写查询的情况下更改其所有维度的所有数组大小，因此这是一种更灵活的方法。
ADDITION: Yes, using with recursive one can build resembling query, capable of handling array with arbitrary dimensions. None the less, there is no way to overcome the limitation coming from relational data model - exact set of columns must be defined at query parse time, and no way to delay this until execution time. So, we are forced to store all indices in one column, using another array.
附加：是的，使用递归可以构建类似的查询，能够处理任意维度的数组。尽管如此，没有办法克服来自关系数据模型的限制 - 必须在查询分析时定义精确的列集，并且无法将此延迟到执行时间。因此，我们*使用另一个数组将所有索引存储在一列中。
Here is the query that extracts all elements from arbitrary multi-dimensional array along with their zero-based indices (stored in another one-dimensional array):
这是从任意多维数组中提取所有元素及其从零开始的索引（存储在另一个一维数组中）的查询：
with recursive extract_index(k,idx,elem,arr,n) as (
select (row_number() over())-1 k, idx, elem, arr, n from (
select array[]::bigint[] idx, unnest(arr) elem, arr, array_ndims(arr) n
from ( select '{{{1,"a"},{11,111}},{{2,"b"},{22,222}},{{3,"c"},{33,333}},{{4,"d"},{44,444}}}'::text[] arr ) input
) plain_indexed
union all
select k/array_length(arr,n)::bigint k, array_prepend(k%array_length(arr,2),idx) idx, elem, arr, n-1 n
from extract_index
where n!=1
)
select array_prepend(k,idx) idx, elem from extract_index where n=1
Which gives:
这使：
idx | elem
--------+-----
{0,0,0} | 1
{0,0,1} | a
{0,1,0} | 11
{0,1,1} | 111
{1,0,0} | 2
{1,0,1} | b
{1,1,0} | 22
{1,1,1} | 222
{2,0,0} | 3
{2,0,1} | c
{2,1,0} | 33
{2,1,1} | 333
{3,0,0} | 4
{3,0,1} | d
{3,1,0} | 44
{3,1,1} | 444
Formally, this seems to prove the concept, but I wonder what a real practical use one could make out of it :)
在形式上，这似乎证明了这个概念，但我想知道一个真正实用的用途:)
#2
1
This works:
这有效：
select key as foo, value as bar
from json_each_text(
json_object('{{1,"a"},{2,"b"},{3,"c"}}')
);
Result:
结果：
foo | bar
-----+------
1 | a
2 | b
3 | c
文件
#1
1
Not sure what exactly you mean saying "it'd be better to have something that scales with the size of the array". Of course you can not have extra columns added to resultset as the inner array size grows, because postgresql must know exact colunms of a query before its execution (so before it begins to read the string).
不确定你究竟是什么意思说“拥有随阵列大小缩放的东西会更好”。当然，随着内部数组大小的增加，你不能在结果集中添加额外的列，因为postgresql在执行之前必须知道查询的确切colunms（所以在它开始读取字符串之前）。
But I would like to propose converting the string into normal relational representation of matrix:
但我想建议将字符串转换为矩阵的正常关系表示：
select i, j, arr[i][j] a_i_j from (
select i, generate_subscripts(arr,2) as j, arr from (
select generate_subscripts(arr,1) as i, arr
from (select ('{{1,"a",11},{2,"b",22},{3,"c",33},{4,"d",44}}'::text[][]) arr) input
) sub_i
) sub_j
Which gives:
这使：
i | j | a_i_j
--+---+------
1 | 1 | 1
1 | 2 | a
1 | 3 | 11
2 | 1 | 2
2 | 2 | b
2 | 3 | 22
3 | 1 | 3
3 | 2 | c
3 | 3 | 33
4 | 1 | 4
4 | 2 | d
4 | 3 | 44
Such a result may be rather usable in further data processing, I think.
我认为，这样的结果可能在进一步的数据处理中可以使用。
Of course, such a query can handle only array with predefined number of dimensions, but all array sizes for all of its dimensions can be changed without rewriting the query, so this is a bit more flexible approach.
当然，这样的查询只能处理具有预定数量维度的数组，但是可以在不重写查询的情况下更改其所有维度的所有数组大小，因此这是一种更灵活的方法。
ADDITION: Yes, using with recursive one can build resembling query, capable of handling array with arbitrary dimensions. None the less, there is no way to overcome the limitation coming from relational data model - exact set of columns must be defined at query parse time, and no way to delay this until execution time. So, we are forced to store all indices in one column, using another array.
附加：是的，使用递归可以构建类似的查询，能够处理任意维度的数组。尽管如此，没有办法克服来自关系数据模型的限制 - 必须在查询分析时定义精确的列集，并且无法将此延迟到执行时间。因此，我们*使用另一个数组将所有索引存储在一列中。
Here is the query that extracts all elements from arbitrary multi-dimensional array along with their zero-based indices (stored in another one-dimensional array):
这是从任意多维数组中提取所有元素及其从零开始的索引（存储在另一个一维数组中）的查询：
with recursive extract_index(k,idx,elem,arr,n) as (
select (row_number() over())-1 k, idx, elem, arr, n from (
select array[]::bigint[] idx, unnest(arr) elem, arr, array_ndims(arr) n
from ( select '{{{1,"a"},{11,111}},{{2,"b"},{22,222}},{{3,"c"},{33,333}},{{4,"d"},{44,444}}}'::text[] arr ) input
) plain_indexed
union all
select k/array_length(arr,n)::bigint k, array_prepend(k%array_length(arr,2),idx) idx, elem, arr, n-1 n
from extract_index
where n!=1
)
select array_prepend(k,idx) idx, elem from extract_index where n=1
Which gives:
这使：
idx | elem
--------+-----
{0,0,0} | 1
{0,0,1} | a
{0,1,0} | 11
{0,1,1} | 111
{1,0,0} | 2
{1,0,1} | b
{1,1,0} | 22
{1,1,1} | 222
{2,0,0} | 3
{2,0,1} | c
{2,1,0} | 33
{2,1,1} | 333
{3,0,0} | 4
{3,0,1} | d
{3,1,0} | 44
{3,1,1} | 444
Formally, this seems to prove the concept, but I wonder what a real practical use one could make out of it :)
在形式上，这似乎证明了这个概念，但我想知道一个真正实用的用途:)
#2
1
This works:
这有效：
select key as foo, value as bar
from json_each_text(
json_object('{{1,"a"},{2,"b"},{3,"c"}}')
);
Result:
结果：
foo | bar
-----+------
1 | a
2 | b
3 | c
文件