MySQL GROUP_CONCAT add slashes

Question

I'm trying to return the results of a poll in JSON format. My first SQL is this

SELECT
fa.pollQuestion_id, 
q.title, 
JSON_OBJECT(
    'text', qo.title, 
    'total', COUNT(fao.pollQuestionOption_id)
) values
FROM pollFillAnswers fa 
INNER JOIN pollFills f ON fa.pollFill_id = f.pollFill_id
INNER JOIN pollQuestions q ON fa.pollQuestion_id = q.pollQuestion_id
INNER JOIN pollFillAnswerOptions fao ON fao.pollFillAnswer_id = 
fa.pollFillAnswer_id 
WHERE f.poll_id = 17 AND fa.type IN (3,4,5) 
GROUP BY fao.pollQuestionOption_id;

This SQL return something like that:

pollQuestion_id title values

163 Question 1 {"text": "No", "total": 599}

163 Question 1 {"text": "Si", "total": 258}

In my second SQL I use GROUP BY pollQuestion_id and GROUP_CONCAT(values) to aggregate the results with this SQL

SELECT JSON_OBJECT(
        'title', result.title, 
        'values', GROUP_CONCAT(values)
    ) answers 
    FROM (
        SELECT
            q.title,
            JSON_OBJECT(
                'text', qo.title, 
                'total', COUNT(fao.pollQuestionOption_id)
            ) values
        FROM pollFillAnswers fa 
        INNER JOIN pollFills f ON fa.pollFill_id = f.pollFill_id
        INNER JOIN pollQuestions q ON fa.pollQuestion_id = q.pollQuestion_id
        INNER JOIN pollFillAnswerOptions fao ON fao.pollFillAnswer_id = fa.pollFillAnswer_id 
        WHERE f.poll_id = 17 AND fa.type IN (3,4,5) 
        GROUP BY fao.pollQuestionOption_id
    ) AS result 
    GROUP BY result.pollQuestion_id;

The result is as follows

163 {"title": "Question 1", "values": "{\"text\": \"No\", \"total\": 599},{\"text\": \"Si\", \"total\": 258}"}

group_contact() add slashes in all of the strings within the JSON, why? I try to remove the slashes with REPLACE()or JSON_UNQUOTES()but I can`t eliminate this behavior.

score 0 · Answer 1 · answered Jun 26 '18 at 07:53

0

Please try the below code once. and if you don't need , replace this with ''.

GROUP_CONCAT(REPLACE(values, ',', '\\,'))

answered Jun 26 '18 at 07:53

Lavish Tyagi

223
5
10

I want to eliminate the slashes and I said in the post the `REPLACE` function doesn't work, – monchyrcg Jun 26 '18 at 08:13
please check once by using stripslashes() if not work for you then please share the schema so that i can try it on my end as normally the replace is working in all the scenario. – Lavish Tyagi Jun 26 '18 at 08:34
I must to do all in a stored procedure. I cant use use other language. – monchyrcg Jun 26 '18 at 09:41
please share your schema with test data so that i can do the same for you. – Lavish Tyagi Jun 26 '18 at 12:01

MySQL GROUP_CONCAT add slashes

1 Answers1