如何获得雪花响应json格式?

Question

这是我正在开发的一个spring引导API的一部分。我正在使用如下查询查询snowflake以获取一些数据:

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}
...

什么是最干净的方式，我可以得到这个响应到一个单一的JsonNode响应，api可以返回?

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.