1

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"
          }
        ]
      }
   ]
}
pik4
  • 1,123
  • 1
  • 18
  • 46
  • You may want to have POJO classes (with the specific fields and hierarchy you'd like) and convert the POJO class into JSON. There are few different libraries you can use to convert POJO to JSON. Please refer this post http://stackoverflow.com/questions/9593409/how-to-convert-pojo-to-json-and-vice-versa. – Tin Jun 17 '16 at 14:08
  • The complex of my case is that I have an OneToMany relation between tables and I should map them in the correct form of data. – pik4 Jun 18 '16 at 07:04

0 Answers0