Mysql JSON_ARRAYAGG from another table in select

Question

I have vacancies and departments and I want to get all departments with vacancies as json array. I try to use JSON_ARRAYAGG function but got mysql syntax error.

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

1) Pls share the exact syntax error, the near … part contains an important clue as to what may have caused the error. 2) Also, what's the exact version of your MySQL? This function was introduced in 5.7.22. — Shadow
– Shadow, Commented Sep 14, 2020 at 11:07
The function JSON_ARRAYAGG() should be used with 1 parameter only — Luuk
– Luuk, Commented Sep 14, 2020 at 11:10

Jim Jimson · Accepted Answer · 2020-09-14 12:07:09Z

1

If you provided some sample data and an expected outcome we could have a better go for you. Try something like this in the meantime:

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

answered Sep 14, 2020 at 12:07

Jim Jimson

2,5784 gold badges22 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql JSON_ARRAYAGG from another table in select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related