mysql - MySQL 使用 nodeJs 嵌套 Json 对象

Question

我有两个 MySQL 表

category(id, name)
product(id, name, category_id)

product(category_id)是一个外键category(id)，关系是一对多对一的关系，所以一个类别可以有许多产品。

我试图从数据库中获取它并以 JSON 格式为多个类别返回它，所以它看起来像这样：

 [
  {
    "id": 1,
    "name": "Category 1",
    "products": [
      {
        "id": 1,
        "name": "Chicken"
      },
      {
        "id": 2,
        "name": "Beef"
      }
    ]
  },
  {
    "id": 2,
    "name": "Category 2",
    "products": [
      {
        "id": 3,
        "item": "Fries"
      },
      {
        "id": 4,
        "item": "Burgers"
      }
    ]
  }
]

产品嵌套在其相应类别的实体中的位置。

我是一个新手，不知道如何使用 MySQL 中的 nodeJs 来重现它。请协助

标签： mysqlsqlarraysjsongroup-by

Answer 1

在 MySQL 中，您可以使用json 生成器函数json_object()和json 聚合函数json_arrayagg()。

以下查询为每个类别生成一个记录，每个记录包含一个 json 对象：

select 
    json_object(
        'id', c.id,
        'name', c.name,
        'products', json_arrayagg(
            json_object(
                'id', p.id,
                'name', p.name
            )
        )
    ) cat
from category c
inner join product p on p.category_id = c.id
group by c.id, c.name

如果要将所有记录聚合到一个唯一的 json 数组中，则可以添加另一个聚合级别：

select json_arrayagg(cat)
from (
    select 
        json_object(
            'id', c.id,
            'name', c.name,
            'products', json_arrayagg(
                json_object(
                    'id', p.id,
                    'name', p.name
                )
            )
        ) cat
    from category c
    inner join product p on p.category_id = c.id
    group by c.id, c.name   
) t