Is such a result possible with a query from an SQL database?

I want to fire a query to get such a result:

[{
  id: 1,
  brandName: "x"
  brandModels: [
    {id: 1, modelName: "abc", createdAt: "yyyy-mm-dd"},
    {id: 2, modelName: "def", createdAt: "yyyy-mm-dd"},
  ]
},
{
  id: 2,
  brandName: "y"
  brandModels: [
    {id: 4, modelName: "ghi", createdAt: "yyyy-mm-dd"},
    {id: 5, modelName: "jkl", createdAt: "yyyy-mm-dd"},
  ]
}]

Tables Schema

BrandsTable
{id, brandName, brand_id}

ModelsTable
{id, modelName, createdAt}

I guess it’s not possible like that? I don’t have any experience with text-based databases, but I can well imagine that this can be achieved with a MongoDB. Because ultimately I want to have a js object at the end of the day.

Answer

Here’s an example but I have not tested it:

SELECT JSON_ARRAYAGG(
  JSON_OBJECT(
    'id', b.id,
    'brandName', b.brandName,
    'brandModels', m.modelArray
  )
) AS joined_result
FROM BrandTable AS b
JOIN (
    SELECT brand_id, JSON_ARRAYAGG(
      JSON_OBJECT(
        'id', id, 
        'modelName', modelName,
        'createdAt', createdAt
      )
    ) AS modelArray
    FROM ModelsTable
    GROUP BY brand_id
) AS m USING (brand_id);

Note that I had to assume the ModelsTable also contains a column for brand_id, otherwise there is nothing to use in the join condition.

Read documentation for JSON_OBJECT() and JSON_ARRAYAGG() for more information about how these functions work.