mysql group\u concat添加斜杠

hlswsv35  于 2021-06-24  发布在  Mysql
关注(0)|答案(1)|浏览(470)

我试图以json格式返回投票结果。我的第一个sql是

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;

此sql返回如下内容:
pollu id标题值
163问题1{“文本”:“否”,“总计”:599}
163问题1{“文本”:“si”,“总计”:258}
在我的第二个sql中,我使用 GROUP BY pollQuestion_id 以及 GROUP_CONCAT(values) 使用此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;

结果如下

163 {"title": "Question 1", "values": "{\"text\": \"No\", \"total\": 599},{\"text\": \"Si\", \"total\": 258}"}
``` `group_contact()` 在json中的所有字符串中添加斜杠,为什么?我试着用手指去划 `REPLACE()` 或者 `JSON_UNQUOTES()` 但我无法消除这种行为。
jfgube3f

jfgube3f1#

请尝试一下下面的代码。如果不需要,请将其替换为“”。

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

相关问题