How to do nested JSON objects in MySQL-CodePudding

I've made two tables for demonstrational purposes;

CREATE TABLE 'owners' (
    'id' int PRIMARY KEY AUTO_INCREMENT, 
    'firstname' varchar(20) NOT NULL, 
    'lastname' varchar(20) NOT NULL
);

and

CREATE TABLE 'cars' (
    'id' int PRIMARY KEY AUTO_INCREMENT, 
    'owner_id' int NOT NULL, 
    'brand' varchar(20), 
    'model' varchar(20)
);

I want to achieve this JSON:

[
   {
      "brand":"volvo",
      "model":"xc90",
      "owner":{
         "id":"1",
         "firstname":"Jon",
         "lastname":"Doe"
      }
   },
   {
      "brand":"bmw",
      "model":"m5",
      "owner":{
         "id":"1",
         "firstname":"Jon",
         "lastname":"Doe"
      }
   },
]

What should my query look like?

I've tried:

JSON_ARRAYAGG()
JSON_OBJECT()
JSON_EXTRACT()
Look at the documentation
Looking for solution all over the internet
Read almost every related Stackoverflow question and answers

CodePudding user response：

Join the two tables to relate the cars with their owners.

Use JSON_OBJECT() to create the JSON objects, and combine them all into an array with JSON_ARRAYAGG().

For the nested object, simply use a nested call to JSON_OBJECT().

SELECT JSON_ARRAYAGG(
    JSON_OBJECT('brand', c.brand, 'model', c.model,
        'owner', JSON_OBJECT('id', o.id, 'firstname', o.firstname, 'lastname', o.lastname)
    )
)
FROM cars AS c 
JOIN owners AS o ON o.id = c.owner_id