i have a table with hotel data, each row has an hotel id. in 2 more tables (hotel images + hotel facilities) there are multiple lines corresponding to each single row in the main table (based on the hotel id value).

I am trying to join multiple tables into a json result.

the wanted outcome is:

"{
        "hotels" : [
           {
             "ID": 1,
             "name": "hotelA",
             "star_rating": 5,
             "Address": "a",
             "isRefundable": true,
             "images":[
              {"url":"x", "title":"a", "description":"a"},
              {"url":"x", "title":"b", "description":"b"},
              {"url":"x", "title":"c", "description":"c"},
              {"url":"x", "title":"d", "description":"d"},
             ],
             "facilities":[
              {"facilityID": 1, "facilityName":"free wifi"},
              {"facilityID": 2, "facilityName":"free breakfast"},
              {"facilityID": 3, "facilityName":"TV"},
             ]
           },
           {...},{...},{...}
       ]}"

currently I've implemented this via code, is there a way to do this using an sql procedure? im using (microsoft sql)

im adding my schema underneath

Main Table (Hotel Table):
| ID | name | star_rating | Address | isRefundable |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | hotelA | 5 | a | true |
| 2 | hotelB | 5 | b | true |
| 3 | hotelC | 3 | c | true |
| 4 | hotelD | 4 | d | true |
| 5 | hotelE | 5 | e | false |

Images Table:

Facilities Table:

I have tried the following but it did not get me very far, because it did not handle multiple lines matching a single line between tables, so h got just one image/facility per hotel - and not all of the related rows.

BEGIN
SET NOCOUNT ON;

SELECT hotel.*, facility.FacilityId, facility.FacilityName, img.url, img.title, img.description,
FROM [main_hotel_table] hotel
JOIN db.facilityTable facility ON hotel.HotelId = facility.HotelId
JOIN db.ImageTable img ON hotel.HotelId = img.HotelId

FOR JSON Path, ROOT('hotels')

END

is there a way to do this using an sql procedure?