I use PostgreSQL and I have these tables, address and company with relation OneToMany on address to company. I want to retrieve a list of addresses which each of them contains a list of companies. I implemented the join between these two tables and I retrieved the data below:
When execute this query:
select ad.id,ad.geolocation, ca.id, ca.name_en, ca.name_el from
address as ad right join company_address as ca on (ad.id = ca.address_id);
I retrieved this table and I want to map it in JSON format as below:
1;"0101000020E6100000C006335CD3043840504BBDB89EC14140"; 6; "Petros";
1;"0101000020E6100000C006335CD3043840504BBDB89EC14140"; 12; "Shop3";
1;"0101000020E6100000C006335CD3043840504BBDB89EC14140"; 13; "Shop3";
7;"0101000020E6100000A03418D984063840D8DD978E94C14140"; 7; "Mike";
7;"0101000020E6100000A03418D984063840D8DD978E94C14140"; 14; "Shop5";
7;"0101000020E6100000A03418D984063840D8DD978E94C14140"; 15; "Shiop8";
9;"0101000020E6100000804127840E163840CC28965B5AC64140"; 10; "Shop2";
9;"0101000020E6100000804127840E163840CC28965B5AC64140"; 11; "Shop3";
Do you know how can convert the results in the format below using an elegant way? Json Data:
{
"code": "200",
"data": [
{
"id": 1,
"geolocation": "0101000020E6100000C006335CD3043840504BBDB89EC14140",
"shops": [
{
"id": 6,
"nameEn": "Petros"
},
{
"id": 12,
"nameEn": "Shop3"
},
{
"id": 13,
"nameEn": "Shop3"
}
]
},
{
"id": 7,
"geolocation": "0101000020E6100000A03418D984063840D8DD978E94C14140",
"shops": [
{
"id": 7,
"nameEn": "Mike"
},
{
"id": 14,
"nameEn": "Shop5"
},
{
"id": 15,
"nameEn": "Shiop8"
}
]
},
{
"id": 9,
"geolocation": "0101000020E6100000804127840E163840CC28965B5AC64140",
"shops": [
{
"id": 10,
"nameEn": "Shop2"
},
{
"id": 11,
"nameEn": "Shop3"
}
]
}
]
}