Returning result in Json and table type sql oralce

December 1, 2022

I have a table in which with repeating id.
The goal is (to make a select) to get all the results of the repeating id to pour against each id in Json format as id it’s not in Json

id,yyyy,name 
1,2010,a  
1,2011,b
2,2010,a
3,2010,c
3,2011,a
4,2011,v

desired result

  id, column(json) 
  1     {"2010":"a","2011","b"}
  2     {"2010":"a","2010":"c"}
  3     {"2010":"c","2011":"a"}
  4     {"2011":"v"}

>Solution :

with data(id, y, name) as (
    select 1,2010,'a' from dual union all  
    select 1,2011,'b' from dual union all
    select 2,2010,'a' from dual union all
    select 3,2010,'c' from dual union all
    select 3,2011,'a' from dual union all
    select 4,2011,'v' from dual -- union all
)
select id, json_objectagg(
    key to_char(y) value name) as obj
from data 
group by id
;

1   {"2010":"a","2011":"b"}
2   {"2010":"a"}
3   {"2010":"c","2011":"a"}
4   {"2011":"v"}