如何将雪花响应变成JSON格式？

Question

这是我正在开发的Spring Boot API的一部分。我正在查询雪花以使用这样的查询来获取一些数据：

select distinct OBJECT_CONSTRUCT(
'id', id,
'name', name,
'etc', etc
) as RESPONSE from ...

我获得了类似格式的字符串列表：

list:
0-> {'id':1, 'name':name, 'etc':etc}
1-> {'id':2, 'name':name, 'etc':etc}
2-> {'id':3, 'name':name, 'etc':etc}
...

我可以对API可以返回的单个JSONNODE响应获得此响应的最清洁方法？

Answer 1

原始语句，生产单个行 -

select distinct object_construct('response',OBJECT_CONSTRUCT(
'id', id,
'name', name,
'etc', etc
)) as RESPONSE from
(select 1 as id, 'name' as name, 'etc' as etc union all
select 2, 'name','etc' union all
select 3, 'name','etc');
+--------------------+
| RESPONSE           |
|--------------------|
| {                  |
|   "response": {    |
|     "etc": "etc",  |
|     "id": 1,       |
|     "name": "name" |
|   }                |
| }                  |
| {                  |
|   "response": {    |
|     "etc": "etc",  |
|     "id": 2,       |
|     "name": "name" |
|   }                |
| }                  |
| {                  |
|   "response": {    |
|     "etc": "etc",  |
|     "id": 3,       |
|     "name": "name" |
|   }                |
| }                  |
+--------------------+
3 Row(s) produced. 

接下来，我们将使用array_agg将结果粘合到单个实体中。

select distinct array_agg(OBJECT_construct(
'id', id,
'name', name,
'etc', etc
)) as RESPONSE from
(select 1 as id, 'name' as name, 'etc' as etc union all
select 2, 'name','etc' union all
select 3, 'name','etc');
+--------------------+
| RESPONSE           |
|--------------------|
| [                  |
|   {                |
|     "etc": "etc",  |
|     "id": 1,       |
|     "name": "name" |
|   },               |
|   {                |
|     "etc": "etc",  |
|     "id": 2,       |
|     "name": "name" |
|   },               |
|   {                |
|     "etc": "etc",  |
|     "id": 3,       |
|     "name": "name" |
|   }                |
| ]                  |
+--------------------+
1 Row(s) produced. 

如果需要，可以根据需要通过to_json或parse_json进一步转换结果。

以下将提供字符串输出 -

select distinct to_json(array_agg(OBJECT_construct(
'id', id,
'name', name,
'etc', etc
))) as RESPONSE from
(select 1 as id, 'name' as name, 'etc' as etc union all
select 2, 'name','etc' union all
select 3, 'name','etc');
+------------------------------------------------------------------------------------------------------------+
| RESPONSE                                                                                                   |
|------------------------------------------------------------------------------------------------------------|
| [{"etc":"etc","id":1,"name":"name"},{"etc":"etc","id":2,"name":"name"},{"etc":"etc","id":3,"name":"name"}] |
+------------------------------------------------------------------------------------------------------------+
1 Row(s) produced.

以下将提供JSON输出 -

select distinct parse_json(to_json(array_agg(OBJECT_construct(
'id', id,
'name', name,
'etc', etc
)))) as RESPONSE from
(select 1 as id, 'name' as name, 'etc' as etc union all
select 2, 'name','etc' union all
select 3, 'name','etc');

+--------------------+
| RESPONSE           |
|--------------------|
| [                  |
|   {                |
|     "etc": "etc",  |
|     "id": 1,       |
|     "name": "name" |
|   },               |
|   {                |
|     "etc": "etc",  |
|     "id": 2,       |
|     "name": "name" |
|   },               |
|   {                |
|     "etc": "etc",  |
|     "id": 3,       |
|     "name": "name" |
|   }                |
| ]                  |
+--------------------+
1 Row(s) produced.