symfony7之使用JSON_ARRAYAGG和JSON_OBJECT做原生sql查询

public function getCertificateListWithEvent(Dto\Input\Certificate\CertificateListSearchDto $certificateListSearchDto)
{
    $params = [];
    $where  = [];

    if (!empty($certificateListSearchDto->getSearch())) {
        $where[] = '(e.title LIKE :search OR e.abbreviation LIKE :search)';
        $params['search'] = '%' . $certificateListSearchDto->getSearch() . '%';
    }

    if (!empty($certificateListSearchDto->getCertificateType())) {
        $where[] = 'ct.title = :certificateType';
        $params['certificateType'] = $certificateListSearchDto->getCertificateType();
    }

    $sql = "SELECT 
            e.id,
            e.title, 
            e.abbreviation,
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    'id', ct.id,
                    'title', ct.title,
                    'certificateImage', ct.certificate_template_path,
                    'accessSetting', ct.access_setting,
                    'systemKey', ct.system_key,
                    'userDefined', ct.user_defined
                ) // 这里本来还想对 JSON_OBJECT 使用 order by ct.sort_order desc 的，虽然我的mysql是 8.0.34 > 8.0.14 但是还是报版本问题
            ) AS certificateTypes
        FROM events e 
        INNER JOIN certificate_types ct ON e.id = ct.event_id";

    if (!empty($where)) {
        $sql .= ' WHERE ' . implode(' AND ', $where);
    }

    $sql .= ' GROUP BY e.id ORDER BY e.end_date DESC';

    $connection = $this->getEntityManager()->getConnection();
    $result     = $connection->executeQuery($sql, $params);

    return $result->fetchAllAssociative();
}

---

关于 JSON_ARRAYAGG 和 JSON_OBJECT 的用法和区别，这两个是 MySQL（8.0+）提供的 JSON 生成函数，配合起来用就能生成结构化 JSON 数据。

1️⃣ JSON_OBJECT

作用：生成一个 单个 JSON 对象（key-value 结构）

语法：

JSON_OBJECT(key1, value1, key2, value2, ...)

例子：

SELECT JSON_OBJECT(
    'id', 1,
    'title', 'Certificate A',
    'sort_order', 5
);

结果：

{"id": 1, "title": "Certificate A", "sort_order": 5}

特点：

• 参数必须成对出现（key, value）。
• key 必须是字符串，value 可以是任意表达式（数字、字符串、列名等）。
• 如果 key 重复，会取最后一个的值。
• 可以嵌套，比如 JSON_OBJECT('info', JSON_OBJECT('id', 1))。

2️⃣ JSON_ARRAYAGG

作用：聚合多行数据为一个 JSON 数组

语法：

JSON_ARRAYAGG(value [ORDER BY sort_expr ASC|DESC])

例子：

SELECT JSON_ARRAYAGG(title) AS titles
FROM certificate_types;

可能结果：

["Certificate A", "Certificate B", "Certificate C"]

特点：

• 类似 GROUP_CONCAT，但返回 JSON 数组，而不是字符串。
• ORDER BY（8.0.14+）可以控制数组内部顺序。
• value 可以是简单列值，也可以是 JSON 对象。

3️⃣ 组合使用

常用场景：先用 JSON_OBJECT 生成每一行的 JSON 对象，再用 JSON_ARRAYAGG 把这些对象聚合成一个数组。

理解：

• JSON_OBJECT：一行 → 一个 JSON 对象
• JSON_ARRAYAGG：多行对象 → 一个 JSON 数组

---

4️⃣ 版本限制

• JSON_OBJECT → MySQL 5.7.8+ 就支持
• JSON_ARRAYAGG → MySQL 8.0+ 才支持
• JSON_ARRAYAGG(... ORDER BY ...) → MySQL 8.0.14+ 才支持排序

5️⃣ 你的场景解释

你代码里：

JSON_ARRAYAGG(
    JSON_OBJECT(
        'id', ct.id,
        'title', ct.title,
        'certificateImage', ct.certificate_template_path,
        'accessSetting', ct.access_setting,
        'systemKey', ct.system_key,
        'userDefined', ct.user_defined
    )
) AS certificateTypes

等于：

1. 对每个证书类型（ct） 生成一个 JSON 对象（id、title 等字段）。
2. 聚合到一个 JSON 数组（所有属于某个 event 的证书类型放到一起）。
3. 结合 GROUP BY e.id，保证每个活动 (event) 只返回一行，并且那一行的 certificateTypes 是该活动的所有证书类型数组。

---

关于 JSON_ARRAYAGG 和 JSON_OBJECT, 我之前一直以为是两个窗口函数，但是根据窗口函数的定义，它们不是的，

1️⃣ 它们的类别

• JSON_OBJECT → 普通函数（scalar function）

  • 每一行执行一次，返回一个 JSON 对象。
  • 和 CONCAT()、LENGTH() 这种函数一样，不涉及分组聚合。

• JSON_ARRAYAGG → 聚合函数（aggregate function）

  • 类似 SUM()、GROUP_CONCAT()，是基于分组（GROUP BY）对多行进行聚合，返回一个 JSON 数组。
  • 不能单独在 SELECT 里随便用，除非有分组（或者整个表当成一个组）。

2️⃣ 窗口函数是什么

窗口函数（window function） 是在分组聚合之后，还能“保留行级细节”的函数，比如：

ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)

特点：

• 必须和 OVER (...) 一起用。
• 不会合并成一行，而是返回每行对应的计算结果。

3️⃣ 区别总结

函数类型	示例	是否需要 GROUP BY	返回行数
普通函数	JSON_OBJECT()	不需要	和输入行一样多
聚合函数	JSON_ARRAYAGG()	通常需要	每组一行
窗口函数	ROW_NUMBER() OVER()	不需要（用 PARTITION 代替）	和输入行一样多

4️⃣ 代码里的情况

JSON_ARRAYAGG(JSON_OBJECT(...)) AS certificateTypes

• JSON_OBJECT(...)：普通函数，生成每行的 JSON 对象。
• JSON_ARRAYAGG(...)：聚合函数，把当前 GROUP BY e.id 的多行对象合并成一个数组。

它俩配合起来用，就能实现“按 event 分组，每组输出一个 JSON 数组”。 这跟窗口函数是完全不同的机制。

---