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?
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.
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