我想要解套这个2D数组来分离1Darray.
当前表格:
| id | ujvj |
|---|---|
| 1 | {{5.3, 0.05}, {3.5, 0.6}, {1.2,0.01}} |
结果表:
| id | uj | vj |
|---|---|---|
| 1 | {5.3, 3.5, 1.2} | {0.05, 0.6, 0.01} |
我找不到一种方法来按元素位置取消多维数组的嵌套，然后将结果放回它们自己的一维数组中.
我想要解套这个2D数组来分离1Darray.
当前表格:
| id | ujvj |
|---|---|
| 1 | {{5.3, 0.05}, {3.5, 0.6}, {1.2,0.01}} |
结果表:
| id | uj | vj |
|---|---|---|
| 1 | {5.3, 3.5, 1.2} | {0.05, 0.6, 0.01} |
我找不到一种方法来按元素位置取消多维数组的嵌套，然后将结果放回它们自己的一维数组中.
对于给定的两个输出列:
SELECT d.id, a.*
FROM data d
CROSS JOIN LATERAL (
SELECT array_agg(d.ujvj[d1][1]) AS uj
, array_agg(d.ujvj[d1][2]) AS vj
FROM generate_subscripts(d.ujvj, 1) d1
) a;
要在源中保留空数组('{}')，请执行以下操作:
SELECT d.id
, COALESCE(a.uj, d.ujvj) AS uj
, COALESCE(a.vj, d.ujvj) AS vj
FROM data d
LEFT JOIN LATERAL (
SELECT array_agg(d.ujvj[d1][1]) AS uj
, array_agg(d.ujvj[d1][2]) AS vj
FROM generate_subscripts(d.ujvj, 1) d1
) a ON d.ujvj <> '{}';
相关: