I have following three relational tables like Country , Stare, City. I want to get these in Json format from relational data table, when I am triggering below query city arrays are coming multiple times. I Want to get the combine JSON data as below format.
select JSON_OBJECT('country_id',b.country_id, 'country_name', a.country_name, 'stateList',JSON_ARRAYAGG( JSON_OBJECT('cityList',(select JSON_ARRAYAGG(x.city_name) FROM city x where x.city_id_mapping=b.state_id group by x.state_id_mapping),'stateid',b.state_id,'state_name',b.state_name)))
FROM State b,Country a , City c where a.country_id=b.state_id and b.state_id=c.city_id by a.country_Id order by a.country_Id;
Country:
County_id country_name currency
-----------------------------------------------------!
1 India Rs.
2 USA Doller
3 NL Euro
State :
State id Stare name language country_maping_id
-------------------------------------------------------------------!
1 Maharashtra Marathi 1
2 WB Bengali 1
3 TN Tamil 1
4 New York English 2
5 Washington English 2
6 Amsterdam Duch 3
City:
City id City Name state id
---------------------------------------------------------!
1 Mumbai 1
2 Thane 1
3 Nagpur 1
4 Kolkata 2
5 Asansol 2
6 Siliguri 3
7 Tacoma 5
8 Olmpia 5
Want to get the combine data in JSON format as follows
[{
countryId: "1", countryName: “India”,
subgrouplist:
[{
State_id: "1", State_name: " Maharashtra ", language : " Marathi ",
city: ["Mumbai ", " Thane ", " Nagpur"]
},
{
State_id: "2", State_name: "WB", language : "Bengali",
city: ["Kolkata", " Asansol", " Siliguri"]
},]
},
{
countryId: "2", countryName: 'USA’,
subgrouplist:
[{
State_id: "1", State_name: " New York ", language : “English",
city: []
},
{
State_id: "1", State_name: "Washington", language : "English",
city: ["Tacoma”,”Olmpia "]
},
]},
{countryId: "1", countryName: 'NL',
subgrouplist:
[{
State_id:, State_name: , language : ,
city: []
}
]},]
