Return multiple COLUMN_JSON results as JSON array

Question

I am storing data in standard tables in a MariaDB, but would like to return records from related tables as a JSON string.

What I intend to do is have a function where I can pass in exerciseId and the function returns a JSON string of all related exerciseMuscle records, meaning each exercise record returned by a stored proc can also include nested data from child tables.

I have been able to create JSON records using COLUMN_JSON and COLUMN_CREATE but can only get this to return as a set of individual records, rather than an array of JSON values as a need. The SQL I'm using is:

select
    e.id, 
    CONVERT(COLUMN_JSON(COLUMN_CREATE(
        'role', em.muscleRoleName, 
        'muscle', em.muscleName
    )) USING utf8) as musclesJson
from
    exercise e
    inner join exerciseMuscle em
            on e.id = em.exerciseId
where
    e.id = 96;

This returns:

| id | musclesJson 
| 96 | {"role":"main","muscle":"biceps"}
| 96 | {"role":"secondary","muscle":"shoulders"}

When what I want is:

| id | musclesJson 
| 96 | [{"role":"main","muscle":"biceps"},{"role":"secondary","muscle":"shoulders"}]

Is it possible to return multiple results in one row without having to iterate through the results and build it manually? If I add a group by to the SQL then the JSON only includes the first record.

Which MySQL version are you using?

Dez
– Dez

2016-10-09 12:00:36 +00:00
Commented Oct 9, 2016 at 12:00 — Dez
– Dez, Commented Oct 9, 2016 at 12:00
MariaDB 10.0.24, just changed tag

Niall
– Niall

2016-10-09 12:01:58 +00:00
Commented Oct 9, 2016 at 12:01 — Niall
– Niall, Commented Oct 9, 2016 at 12:01

Niall · Accepted Answer · 2016-10-09 12:36:58Z

3

Turns out it was GROUP_CONCAT that I needed, and specifying a comma as the delimiter. So changing my SQL to:

select
    e.id, 
    CONVERT(
        GROUP_CONCAT(
            COLUMN_JSON(
                COLUMN_CREATE(
                    'role', em.muscleRoleName, 
                    'muscle', em.muscleName
                )
            )
            SEPARATOR ','
        ) USING utf8) as muscles 
from
    exercise e
    inner join exerciseMuscle em
            on e.id = em.exerciseId
where
    e.id = 96;

Returns:

| id | musclesJson 
| 96 | {"role":"main","muscle":"biceps"},{"role":"secondary","muscle":"shoulders"}

answered Oct 9, 2016 at 12:36

Niall

1,6214 gold badges24 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

Return multiple COLUMN_JSON results as JSON array

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