Question Re: HANA SQL QUERY TO FETCH NESTED JSON OBJECTS in Technology Q&A

HANA SQL QUERY TO FETCH NESTED JSON OBJECTS

govardan_raj — Wed, 05 Feb 2025 13:38:15 GMT

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

Re: HANA SQL QUERY TO FETCH NESTED JSON OBJECTS

Vitaliy-R — Thu, 06 Feb 2025 21:50:51 GMT

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

Re: HANA SQL QUERY TO FETCH NESTED JSON OBJECTS

govardan_raj — Mon, 10 Feb 2025 07:32:00 GMT

Awesome , Thanks a lot its working