How to get snowflake response into json format?-CodePudding

This is part of a spring boot API I am developing. I am querying snowflake to get some data using a query like this:

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

I am getting a a List of String which is formatted like this:

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

What is the cleanest way that I can get this response to a single JsonNode response that the api can return?

CodePudding user response：

Original statement, producing individual rows -

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.

Next we will use ARRAY_AGG, to get result clubbed into single entity.

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.

If required, the result can be further converted as needed via TO_JSON or PARSE_JSON.

Below will give string output -

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.

Following will give JSON output -

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.