[SOLVED] How to keep table name when inner joining related tables

Issue

I am new to SQL and wonder how to select nested tables.
I have two tables like this:

sensors

locations

They are linked with foreign key. Currently, I select using

SELECT sensors.*, locations.*
FROM sensors INNER JOIN locations
ON sensors.location_id = locations.location_id;

to get the data from both like this:

{
  "sensor_id": 1,
  "model_no": "some string",
  "location_id": 2,
  "name": "Berlin",
  "location": {
    "x": 3,
    "y": 3
  },
  "radius": 1000
}

I wonder if there is any way I can keep the location data grouped as its own object like this:

{
  "sensor_id": 1,
  "model_no": "some string",
  "location": {
    "name": "Berlin",
    "location": {
      "x": 3,
      "y": 3
    },
    "radius": 1000
  }
}

I am using MySQL 8 with mysql npm package to execute the queries. I know I can modify the response using javascript but wonder if it can be done directly in the query, and if so, is it better or worse for performance?

Solution

SELECT JSON_OBJECT( 
           'sensor_id', sensor_id, 
           'model_no', model_no,
           'location', JSON_OBJECT( 
                           'name', name,
                           'location', JSON_OBJECT(
                                           'x', CAST(ST_X(location) AS SIGNED),
                                           'y', CAST(ST_Y(location) AS SIGNED) 
                                           ),
                           'radius', radius 
                           )
                  ) as JSON_output
FROM sensors
JOIN locations USING (location_id);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b17dfa3069b4bb9345a9e99e8b893121

Answered By – Akina

Answer Checked By – Gilberto Lyons (BugsFixing Admin)