Convert multi-dimensional array to records

Question

Given: {{1,"a"},{2,"b"},{3,"c"}}
Desired:

 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;

There is no way to dynamicly change the number of columns returned by SQL query. — Ihor Romanchenko, Aug 07 '13 at 19:20
The only thing I meant to roll back was adding 'tag:' to a title, an edit you had reverted. Please don't put tags in titles. From your edit comment it seems you're looking for this search: http://stackoverflow.com/search?q=infavorites%3A183181+[postgres]. You can also use infavorites:mine as a shortcut. — Flexo, Aug 31 '13 at 18:35
@Flexo: you're amazing. Yes that is something I did not know about and probably could have used a couple years ago. Maybe I should hang out in Meta more often. You win :) — vol7ron, Aug 31 '13 at 18:39
If you replace every `{` with `[` and `}` with `]`, it becomes JSON which could be parser by a pl/pgsql json parser. Would that be a feasible solution for you? — asontu, Sep 01 '13 at 11:02
@funkwurm unfortunately not, unless it's part of the lastest vanilla Postgres that is still being maintained (8.4). I could also create a temporary table, or use `VALUES (..),(...)`, but neither would suffice. — vol7ron, Sep 01 '13 at 15:43

score 2 · Answer 1 · answered Oct 28 '15 at 23:27

2

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

Docs

answered Oct 28 '15 at 23:27

Travis

1,998
1
21
36

mas.morozov · Answer 2 · 2013-09-03T15:45:36.417

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).

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 :)

Mas, I'm not sure that's right. I'm thinking I could combine a `WITH RECURSIVE` syntax looking up the dims and upper bounds of the array .. and have something more dynamic. However, in testing, I've noticed that it's very slow to turn a string into an array, using this syntax (but that's beside the point - this is a proof of concept) — vol7ron, Sep 03 '13 at 03:21
Yes, such a solution exists, but it does not look somehow practical. SQL is just improper tool to do such non-relational things, so any SQL solution is doomed to be slow and unclear. — mas.morozov, Sep 03 '13 at 09:53

Convert multi-dimensional array to records

2 Answers2