How to get a JSON_OBJECT() of 3 columns whose some contains already a JSON format string.
Here is my example with info field that contains a json string, so all this object is escaped :
SELECT
T1.id,
CONCAT(
'{"elements": [',
GROUP_CONCAT(
JSON_OBJECT(
'type', T2.`type`,
'data', T2.`data`,
'info', T2.`info` <
)
),
']}'
) AS `elements`,
FROM `table` T1
INNER JOIN `table2` T2
ON T1.`id` = T2.`fk_t1_id`
GROUP BY T1.`id`
Maybe, use the new storage functions for JSON format will be better, but I didn't test it yet. What do you think ?
解决方案
Best solution I found is to use JSON_MERGE() in combination with JSON_OBJECT() and CONCAT()
SELECT
T1.id,
CONCAT(
'{"elements": [',
GROUP_CONCAT(
JSON_MERGE(
JSON_OBJECT(
'type', T2.`type`,
'data', T2.`data`
),
CONCAT('{"info": ', T2.`info`, '}')
)
),
']}'
) AS `elements`,
FROM `table` T1
INNER JOIN `table2` T2
ON T1.`id` = T2.`fk_t1_id`
GROUP BY T1.`id`
3769
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
