Encode JSON data with two tables using join?

Question

I have a table with stadiums containing the city code as foreign key. I want to get all information about stadium and city tables, but I want city information in a different level, currently, this info is at the same level.

I know there are many similar questions, but I am not able to get the desired result after trying those solutions.

My code:

$consulta = "SELECT es.*, c.nombre as 'ciudad', c.pais FROM estadio es INNER JOIN ciudad c ON es.cod_ciudad = c.cod_ciudad WHERE es.nombre LIKE '$nombre' ORDER BY es.nombre";
$return_data = [];
$query = pdo->query($consulta);
while ($obj = $query->fetch(PDO::FETCH_ASSOC)) {
    $return_data[] = $obj;
}
print(json_encode($return_data));

Current result:

[
   {
      "cod_est":28,
      "nombre":"Alfonso Murube",
      "cod_ciudad":13,
      "ciudad":"Ceuta",
      "pais":"España"
   },
   {
      "cod_est":19,
      "nombre":"Antonio Pazos Puyana - El Monago",
      "cod_ciudad":2,
      "ciudad":"Rota",
      "pais":"España"
   }
]

Expected result:

[
   {
      "cod_est":28,
      "nombre":"Alfonso Murube",
      "cod_ciudad":13,
      "ciudad":[
         {
            "ciudad":"Ceuta",
            "pais":"Espana"
         }
      ]
   },
   {
      "cod_est":19,
      "nombre":"Antonio Pazos Puyana - El Monago",
      "cod_ciudad":2,
      "ciudad":[
         {
            "ciudad":"Rota",
            "pais":"Espana"
         }
      ]
   }
]

Answer 1

You'll need to modify your PHP code to restructure the flat result set into the nested JSON format you want. Here's how you can achieve this:

1. Retrieve the data with a simple JOIN as you were doing before
2. For each row, extract the city and country into a separate associative array
3. Remove these fields from the main object to avoid duplication
4. Add the city data as a nested array under the 'ciudad' key
5. Output the restructured data as JSON

I've also added the JSON_PRETTY_PRINT option to make the output more readable, though you can remove this if you prefer compact JSON.

<?php
$consulta = "SELECT es.*, c.nombre as 'ciudad', c.pais FROM estadio es INNER JOIN ciudad c ON es.cod_ciudad = c.cod_ciudad WHERE es.nombre LIKE '$nombre' ORDER BY es.nombre";
$return_data = [];
$query = $pdo->query($consulta);

// Process results and create nested structure
while ($obj = $query->fetch(PDO::FETCH_ASSOC)) {
    // Extract city data
    $cityData = [
        'ciudad' => $obj['ciudad'],
        'pais' => $obj['pais']
    ];
    
    // Remove flat city data from the main object
    unset($obj['ciudad']);
    unset($obj['pais']);
    
    // Add city data as a nested array
    $obj['ciudad'] = [$cityData];
    
    $return_data[] = $obj;
}

// Output JSON with proper encoding
echo json_encode($return_data, JSON_PRETTY_PRINT);