如何将列表中对象的字段值聚合到Snowflake中的一行中？

Question

我有一个包含 id 的表和一个包含名称的类似 JSON 的对象列表。我需要将此对象列表转换为单个字符串，名称以逗号分隔。

原始表

1 [{'name':'John', age: 23}, {'name':'Mary', age: 50}]
2 [{'name':'Joseph'}]
3 []
4 [{'name':'Alexander', age:12}, {'name':'Martha', age:42}, {'name':'Eugene'}]

预期结果

1 John, Mary
2 Joseph
3 NULL (or empty string)
4 Alexander, Martha, Eugene

我已经尝试使用 LATERAL JOIN 和 FLATTEN 函数，但这似乎不合适，因为它会生成更多行。我应该使用它然后使用 listagg 进行分组吗？感觉有一种更简单的方法可以做到这一点。

这是一个包含两个表的 db-fiddle 。

Answer 1

使用的数据集如下 -

select * from tab_names_json;

+---------------------------+
| NAMES                     |
|---------------------------|
| {                         |
|   "tab2": [               |
|     {                     |
|       "name": "Joseph"    |
|     }                     |
|   ],                      |
|   "tab3": [],             |
|   "tab4": [               |
|     {                     |
|       "age": 12,          |
|       "name": "Alexander" |
|     },                    |
|     {                     |
|       "age": 42,          |
|       "name": "Martha"    |
|     },                    |
|     {                     |
|       "name": "Eugene"    |
|     }                     |
|   ],                      |
|   "tabl": [               |
|     {                     |
|       "age": 23,          |
|       "name": "John"      |
|     },                    |
|     {                     |
|       "age": 50,          |
|       "name": "Mary"      |
|     }                     |
|   ]                       |
| }                         |
+---------------------------+

注意：我加载的文件实际上具有从 tab1 到 tab4 排序的“tab*”；然而，
JSON 中不保留顺序。

查询来获取。

select distinct listagg(ifnull(tab1.name,'null'),',') 
within group(order by tab1.name) 
over (partition by tab1.seq) as agg 
from
(select f1.seq as seq,f1.value:name as name from TAB_NAMES_JSON t, 
lateral flatten(input=>t.names) as f, 
lateral flatten (input => f.value,mode=>'ARRAY',outer=>TRUE) f1) tab1;

+-------------------------+
| AGG                     |
|-------------------------|
| Joseph                  |
| null                    |
| Alexander,Eugene,Martha |
| John,Mary               |
+-------------------------+