Skip to content
Advertisement

How to keep table name when inner joining related tables

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

sensors

sensor_id model_no location_id
int varchar int

locations

location_id name location radius
int varchar point int

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?

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement