MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE sensors (sensor_id INT, model_no VARCHAR(255), location_id INT);
INSERT INTO sensors VALUES 
(1, 'some string', 11),
(2, 'another string', 22);
SELECT sensor_id, 
       model_no, 
       location_id 
FROM sensors;


sensor_id
model_no
location_id


1
some string
11

2
another string
22


CREATE TABLE locations (location_id INT, name VARCHAR(255), location POINT, radius INT);
INSERT INTO locations VALUES 
(11, 'Berlin', POINT(3,4), 1000),
(22, 'Frankfurt', POINT(5,6), 100);
SELECT location_id, 
       name, 
       ST_AsText(location),
       ST_X(location),
       ST_Y(location),
       radius
FROM locations;


location_id
name
ST_AsText(location)
ST_X(location)
ST_Y(location)
radius


11
Berlin
POINT(3 4)
3
4
1000

22
Frankfurt
POINT(5 6)
5
6
100


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);


JSON_output


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

{"location": {"name": "Frankfurt", "radius": 100, "location": {"x": 5, "y": 6}}, "model_no": "another string", "sensor_id": 2}