时间戳

首页 > 解决方案 > GROUP_CONCAT 以多对多关系返回对象数组

mysql - GROUP_CONCAT 以多对多关系返回对象数组

问题描述

我正在为个人项目处理 mySQL 查询。现在的最终目标是让查询为每个项目返回以下 json：

{
    "id": 1,
    "designName": "Slender Man",
    "designNotes": "Rewrite the lapel component",
    "quantity": 3,
    "colors": [
        {
        "id": 4,
        "colorName": "black",
        "colorSwatch": "rgb (0,0,0)",
        "brandName": "caron simply soft",
        "yarnWeightId": {
              "weightNumber": 4,
              "weightName": "medium/worst weight"
          }
        },{
        "id": 5,
        "colorName": "Off White",
        "colorSwatch": "rgb (255,255,255)",
        "brandName": "caron simply soft",
        "yarnWeightId": {
              "weightNumber": 4,
              "weightName": "medium/worst weight"
          }
        }
    ]
}

我最近的尝试是：

SELECT 
    d.id,
    d.designName,
    d.designNotes,
    d.quantity,
    d.isDeleted,
    GROUP_CONCAT(
        JSON_OBJECT(
            c.colorName,
            c.colorSwatch,
            c.brandName,
            yw.weightNumber,
            yw.weightName
        )
    ) AS colorsUsed
FROM designs AS d
    INNER JOIN design_colors AS dc 
        ON dc.designId = d.id
    INNER JOIN colors AS c 
        ON dc.colorId = c.id
    INNER JOIN yarnweights AS yw 
        ON c.yarnWeightId = yw.id
GROUP BY d.id
;

这给了我一个内部服务器错误。使用更简单的查询对其进行测试很顺利，因此我将其范围缩小到查询本身就是问题所在。

下面的查询让我很接近：

SELECT 
    d.id,
    d.designName,
    d.designNotes,
    d.quantity,
    d.isDeleted,
    GROUP_CONCAT(
        DISTINCT c.id
        GROUP BY c.id
    ) AS colorsUsed
FROM designs AS d
    INNER JOIN design_colors AS dc 
        ON dc.designId = d.id
    INNER JOIN colors AS c 
        ON dc.colorId = c.id
    INNER JOIN yarnweights AS yw 
        ON c.yarnWeightId = yw.id
GROUP BY
    d.id

它返回这样的对象：

{
    "id": 3,
    "designName": "Slenderman",
    "designNotes": null,
    "quantity": 0,
    "isDeleted": 0,
    "colorsUsed": "4,5"
}

但是，虽然我得到了所有的 id，但colorsUsed我并没有把它们放在一个数组中，更不用说我需要的对象数组了。我觉得 GROUP_CONCAT 不是正确的解决方案，或者至少我没有正确使用它，但是当我试图找到解决方案时就会出现这种情况。只是在正确的方向上继续寻找一点也会很有帮助。

我的数据库结构如下：[db diagram][ https://i.stack.imgur.com/lxsvv.png]

标签： mysqlsqljsonmany-to-manyaggregate-functions

解决方案

GROUP_CONCAT()您可以使用 MySQL JSON 函数生成有效的 JSON 对象，而不是使用字符串连接函数，例如。JSON_OBJECT()可用于创建对象，JSON_ARRAYAGG()是生成 JSON 数组的聚合函数。

首先，让我们从一个返回所有需要的列的查询开始：

SELECT 
    d.id,
    d.designName,
    d.designNotes,
    d.quantity,
    c.id,
    c.colorName,
    c.colorSwatch,
    c.brandName
    y.weightNumber,
    y.weightName
FROM 
    designs AS d
    INNER JOIN design_colors AS dc ON dc.designId = d.id
    INNER JOIN colors AS c ON dc.colorId = c.id
    INNER JOIN yarnweights AS y ON c.yarnWeightId = y.id

现在我们可以打开聚合并使用 JSON 函数来生成预期的结果集：

SELECT JSON_OBJECT(
    'id', d.id,
    'designName', d.designName,
    'designNotes', d.designNotes,
    'quantity', d.quantity,
    'colors', JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', c.id,
            'colorName', c.colorName,
            'colorSwatch', c.colorSwatch,
            'brandName', c.brandName
            'yarnWeightId', JSON_OBJECT(
                'weightNumber', y.weightNumber,
                'weightName', y.weightName
            )
        )
    ) AS myjson
FROM 
    designs AS d
    INNER JOIN design_colors AS dc ON dc.designId = d.id
    INNER JOIN colors AS c ON dc.colorId = c.id
    INNER JOIN yarnweights AS y ON c.yarnWeightId = y.id
GROUP BY
    d.id,
    d.designName,
    d.designNotes,
    d.quantity

这将返回一个具有唯一列的结果集，其中每条记录都包含预期的 JSON 对象。

推荐阅读