将数组分解为单个元素，并通过唯一标识符创建新数组

Question

我有一个包含 6 个元素的数组字段(此处的示例有 4 个元素(，我想透视此数据并为每个元素创建一个新数组。

这可能是重复的，但我不确定合适的标题来提出类似的问题。

array<struct<Relative_Type:string,relative__name:relative_age:string,relative_id:string,relative_dept:string,title:string>>

---

原始表

+----+--------------------------------------------------------------------------------+
| ID | Array                                                                          |
+----+--------------------------------------------------------------------------------+
| 1  | [{"Relative Type":"Dad", "Relative Name":"Jerry", "Relative Age":"30"}]        |
+----+--------------------------------------------------------------------------------+
| 1  | [{"Relative Type":"Mom", "Relative Name":"Beth", "Relative Age":"33"}]         |
+----+--------------------------------------------------------------------------------+
| 1  | [{"Relative Type":"Sister", "Relative Name":"Summer", "Relative Age":"20"}]    |
+----+--------------------------------------------------------------------------------+
| 1  | [{"Relative Type":"Grandfather", "Relative Name":"Rick", "Relative Age":"77"}] |
+----+--------------------------------------------------------------------------------+
| 2  | [{"Relative Type":"Dad", "Relative Name":"Tom", "Relative Age":"43"}]          |
+----+--------------------------------------------------------------------------------+
| 2  | [{"Relative Type":"Mom", "Relative Name":"Teresa", "Relative Age":"59"}]       |
+----+--------------------------------------------------------------------------------+

---

所需表

+----+---------------------------------+-----------------------------+------------------+
| ID | Relative Type                   | Relative Name               | Relative Age     |
+----+---------------------------------+-----------------------------+------------------+
| 1  | (Dad, Mom, Sister, Grandfather) | (Jerry, Beth, Summer, Rick) | (30, 33, 20, 77) |
+----+---------------------------------+-----------------------------+------------------+
| 2  | (Dad, Mom)                      | (Tom, Teresa)               | (43, 59)         |
+----+---------------------------------+-----------------------------+------------------+

Answer 1

要实现这一点，您需要首先UNNEST，然后聚合到一个数组中。请参阅以下示例：

> SELECT * FROM x;
i |                                c
---+------------------------------------------------------------------
1 | [{relative_type=Sister, relative__name=Summer, relative_age=20}]
1 | [{relative_type=Mom, relative__name=Betty, relative_age=33}]
1 | [{relative_type=Dad, relative__name=Jerry, relative_age=30}]
2 | [{relative_type=Mom, relative__name=Jane, relative_age=33}]
2 | [{relative_type=Dad, relative__name=Tommy, relative_age=40}]
(5 rows)
> SELECT i, array_agg(relative_type), array_agg(relative__name), array_agg(relative_age) 
FROM x, UNNEST (c) GROUP BY i;
i |       _col1        |         _col2          |    _col3
---+--------------------+------------------------+--------------
2 | [Dad, Mom]         | [Tommy, Jane]          | [40, 33]
1 | [Mom, Sister, Dad] | [Betty, Summer, Jerry] | [33, 20, 30]
(2 rows)