By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE reservations( id INT , time VARCHAR(100) , user_id INT)
CREATE TABLE users(id INT, NAME VARCHAR(100))
INSERT INTO reservations VALUES (1, "123456789", 321)
INSERT INTO users VALUES (321, 'John Doe')
INSERT INTO reservations VALUES (2, "00000", 7)
INSERT INTO users VALUES (7, 'Nick')
SELECT *
FROM reservations r
JOIN users u
ON r.user_id = u.id
| id | time | user_id | id | NAME |
|---|---|---|---|---|
| 1 | 123456789 | 321 | 321 | John Doe |
| 2 | 00000 | 7 | 7 | Nick |
SELECT JSON_OBJECT('id', r.id,
'time', r.time,
'user_id', r.user_id,
'user', JSON_OBJECT('id', u.id,
'name', u.name)
) AS result
FROM reservations r
JOIN users u
ON r.user_id = u.id
| result |
|---|
| {"id": 1, "time": "123456789", "user": {"id": 321, "name": "John Doe"}, "user_id": 321} |
| {"id": 2, "time": "00000", "user": {"id": 7, "name": "Nick"}, "user_id": 7} |
SELECT JSON_ARRAYAGG(JSON_OBJECT('id', r.id,
'time', r.time,
'user_id', r.user_id,
'user', JSON_OBJECT('id', u.id,
'name', u.name)
)) AS result
FROM reservations r
JOIN users u
ON r.user_id = u.id
| result |
|---|
| [{"id": 1, "time": "123456789", "user": {"id": 321, "name": "John Doe"}, "user_id": 321}, {"id": 2, "time": "00000", "user": {"id": 7, "name": "Nick"}, "user_id": 7}] |
/*{
id: 1,
time: "123456789",
user_id: 321,
user: {
id: 321,
name: John Doe
}
}*/