Json_Array with Group_Concat does not give a single array of string

Question

We have 2 tables. Question which has a question and Answers which has possible answer's for that question.

I want to collect all the questions such that all the answers for a given question appear in form of array.

select Answers.question_Id as questionId, 
       group_concat(json_array(Answers.answer)) as answers
      from Question 
      inner join Answers 
      on Question.id = Answers.question_Id 
      group by questionId

When I tried the above query I get the following output :

------------------------------------
questionId | answers
--------------------------
1          | ["1"],["2"],["3"],["4"]
------------------------------------

So I tried the following query :

select Answers.question_Id as questionId, 
          json_array(group_concat(Answers.answer)) as answers
          from Question 
          inner join Answers 
          on Question.id = Answers.question_Id 
          group by questionId

I get following output :

questionId | answers
--------------------------
1          | ["1,2,3,4"]
--------------------------

What I am looking for is ['1', '2', '3', '4']

Any suggestions what would be a good way. I also tried using only group_concat but it separates the output by comma and there are high chances that the answer will have comma in it. I was looking for a simple solution.

MySQL version : 5.7.19

Why do you need JSON output?

Tim Biegeleisen
– Tim Biegeleisen

2018-03-23 11:37:29 +00:00
Commented Mar 23, 2018 at 11:37 — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 23, 2018 at 11:37
actually I need it in form of array

j10
– j10

2018-03-23 12:37:40 +00:00
Commented Mar 23, 2018 at 12:37 — j10
– j10, Commented Mar 23, 2018 at 12:37

Tim Biegeleisen · Accepted Answer · 2018-03-23 12:18:13Z

3

If you are using MySQL 5.7.22 or later, you may use the JSON_ARRAYAGG function:

SELECT
    a.question_Id AS questionId, 
    JSON_ARRAYAGG(a.answer) AS answers
FROM Question q
INNER JOIN Answers a
    ON q.id = q.question_Id 
GROUP BY
    a.question_Id

edited Mar 23, 2018 at 12:18

answered Mar 23, 2018 at 11:45

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

j10 Over a year ago

We are using MYSQL 5.7.19

Tim Biegeleisen Over a year ago

Then you may be somewhat out of luck. There might be a UDF which can do this. Are you using MySQL with an app language, such as Java or C#? They can easily handle your requirement.

Collectives™ on Stack Overflow

Json_Array with Group_Concat does not give a single array of string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related