来自select中另一个表的Mysql JSON_ARRAYAGG

Question

我有空缺和部门，我想得到所有部门的空缺作为json数组。我尝试使用JSON_ARRAYAGG函数，但是得到了mysql语法错误。

select vd.id, vd.title, vd.sort,
(select json_arrayagg(v.id, v.title) from vacancies as v where vd.id = v.department_id) as vacancies
from vacancy_departments as vd

Answer 1

如果您提供一些样本数据和预期结果，我们可以为您提供更好的结果。在此期间尝试这样的东西：

SELECT vd.id, vd.title, vd.sort,
(
    SELECT JSON_ARRAYAGG(
        JSON_MERGE_PRESERVE(
            JSON_OBJECT("id", v.id),
            JSON_OBJECT("title", v.title)
            ))
    FROM vacancies as v
    WHERE vd.id = v.department_id
) as vacancies
FROM vacancy_departments as vd