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?

CodePudding user response：

You must manually label each column; you cannot get the desired result and use *.

A small consolation prize: If you change

ON sensors.location_id = locations.location_id;

to

USING(location_id)

There will be only one location_id column.

CodePudding user response：

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