sql - 将多维数组转换为记录

Question

给定:{{1,"a"},{2,"b"},{3,"c"}}
期望:

 foo | bar
-----+------
  1  |  a
  2  |  b
  3  |  c

您可以通过以下查询获得预期的结果；但是，最好有一些随数组大小而缩放的东西。

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;

Answer 1

这个有效:

select key as foo, value as bar
from json_each_text(
  json_object('{{1,"a"},{2,"b"},{3,"c"}}')
);

结果:

 foo | bar
-----+------
  1  |  a
  2  |  b
  3  |  c

Docs