Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

Convert row results into a json_object


Kim Girao

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

×
×
  • Create New...