HANA SQL QUERY TO FETCH NESTED JSON OBJECTS

[govardan_raj]

hi ,

I have a table machine_group with values as shown.

 

Now using a query as show below

select * from machine_group for json

the values fetched as below 

[
  {"COLA" :"A","COLB":"VAL1"},{"COLA" :"A","COLB":"VAL2"},{"COLA" :"A","COLB":"VAL3"},
  {"COLA" :"B","COLB":"VAL5"},{"COLA" :"B","COLB":"VAL6"},{"COLA" :"B","COLB":"VAL7"}
 ]

But am looking to fetch the output in nested json format as shown below.

 
[
{
"COLA":"A",
"COLB":[ {"COLB":"VAL1"}, {"COLB":"VAL2"}, {"COLB":"VAL3"}]
},
{
"COLA":"B",
"COLB":[ {"COLB":"VAL4"}, {"COLB":"VAL5"}, {"COLB":"VAL6"}]
}
]

Please help me. 

Regards

Govardan

[Vitaliy-R · Accepted solution]

It's not that straightforward, but can you try

WITH ColB_Aggregated AS (
    SELECT 
        COLA,
        '[' || STRING_AGG('{"COLB": "' || COLB || '"}', ', ') || ']' AS COLB_JSON
    FROM YourTable
    GROUP BY COLA
)
SELECT 
    '[' || STRING_AGG('{ "COLA": "' || COLA || '", "COLB": ' || COLB_JSON || ' }', ', ') || ']' AS FINAL_JSON
FROM ColB_Aggregated;

?

Regards,
--Vitaliy

[govardan_raj · Accepted solution]

Awesome , Thanks a lot its working