Convert row results into a json_object

[Kim Girao]

I am attempting to transform my query results into a json_object to be published by rest API.

Results Sample:

 

 

 

 

 

 

 

 

id

first_name

date

purchase_id

item_name

 

 

 

 

55

Kim

Sep 9th

1

hard drive

 

 

55

Kim

Sep 9th

2

software

 

 

55

Kim

Sep 9th

3

monitor

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I want my response to be something along the lines of

{

id: 55,

first_name: Kim,

date: Sep 9th,

purchase_info [

{"purchase_id": 1,

"item_name": "hard drive"},

{"purchase_id": 2,

"item_name": "software"},

{"purchase_id": 1,

"item_name": "monitor"}]

}

 

I have attempted to convert the results as a JSON_OBJECT but recevied an error message of incorrect syntax. I am successfully able to convert the results as a JSON_ARRAY, however, that doesn't provide me the end-goal that I am trying to achieve.

[Kim Girao]

I have been able to get JSON_OBJECT to work. However, the results still return the duplicated information for id, name, and date. 

 

[Kim Girao]

I was able to create a JSON_OBJECT by running the following

 

 

SELECT DISTINCT

id,

first_name,

date,

LISTAGG(purchases,',') WITHIN GROUP (ORDER BY id, first_name, date) OVER (PARTITION BY id, fist_name, date) AS purchases

FROM (

SELECT DISTINCT

id,

first_name,

date,

JSON_OBJECT(

'purchase_id':purchase_id,

'item_name':item_name) AS purchases

FROM purchase_table

) T1

The only problem is this syntax causes the JSON object to become a clob, greatly reducing the query response.