MySQL 一对多转 JSON 格式

我有两个 MySQL 表：

User (id, name)
Sale (id, user, item)

Where Sale(user)是一个外键User(id)，所以这是一种一对多的关系（一个用户可以进行多次销售）。

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

[
  {
    "id": 1,
    "name": "User 1",
    "sales": [
      {
        "id": 1,
        "item": "t-shirt"
      },
      {
        "id": 2,
        "item": "jeans"
      }
    ]
  },
  {
    "id": 2,
    "name": "User 2",
    "sales": [
      {
        "id": 3,
        "item": "sweatpants"
      },
      {
        "id": 4,
        "item": "gloves"
      }
    ]
  }
]

其中“销售”实体嵌套在其相应“用户”的实体内。

所以问题是，从数据库查询并将其转换为 JSON 的最佳方法是什么？我可以对所有用户运行查询，然后迭代每个用户并运行查询来获取他们的销售额，但这非常慢。或者，我可以在用户和销售之间进行外部联接，然后将代码解析为 JSON 格式，但这会从数据库发送多余的信息（包括每个销售的整套用户数据），并且需要循环遍历所有信息代码。有没有方便的方法来做到这一点？顺便说一句，我正在使用 Python 3.7。

这是一个可能满足您要求的 SQL 查询。它使用MySQL JSON_ARRAYAGG() 聚合函数 https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_json-arrayagg生成 JSON 对象数组（使用JSON_OBJECT() https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html#function_json-object).

在连接内执行中间级别的分组，以生成sales每个用户的 JSON 数组。然后，结果将聚合到一行中，其中一列包含生成的对象 JSON 数组。

SELECT
  JSON_ARRAYAGG(JSON_OBJECT('id', u.id, 'name', u.name, 'sales', s.sales))
FROM
    user u
    LEFT JOIN (
        SELECT 
            user, 
            JSON_ARRAYAGG(JSON_OBJECT('id', id, 'item', item)) sales 
        FROM sale 
        GROUP BY user
    ) s ON s.user = u.id

如果将返回值包装为JSON_PRETTY，输出如下：

[
  {
    "id": 1,
    "name": "User 1",
    "sales": [
      {
        "id": 1,
        "item": "t-shirt"
      },
      {
        "id": 2,
        "item": "jeans"
      }
    ]
  },
  {
    "id": 2,
    "name": "User 2",
    "sales": [
      {
        "id": 3,
        "item": "sweatpants"
      },
      {
        "id": 4,
        "item": "gloves"
      }
    ]
  }
]

Edit：这是一个针对 MySQL "特点 ：

SELECT
    CONCAT(
        '[', 
        GROUP_CONCAT( CONCAT( '{ "id":', u.id, ', "name":"', u.name, '", "sales":', s.sales, ' }' )  SEPARATOR ', ' ),
        ']'
    )
FROM 
    user u
    LEFT JOIN (
        SELECT 
            user, 
            CONCAT( 
               '[', 
                GROUP_CONCAT( CONCAT( '{ "id":', id, ', "item":"', item, '" }' ) SEPARATOR ', '),
                ']'
            ) sales 
    FROM sale
    GROUP BY user ) s ON s.user = u.id

DB Fiddle 上的演示 https://www.db-fiddle.com/f/eqSwXxhdvaKyW5VyrdpKii/0