MySQL JSON_OBJECT()，其中某些字段已经包含JSON字符串 [英] MySQL JSON_OBJECT() with some fields already containing JSON String

问题描述

如何获取3列的JSON_OBJECT()，其中的其中一些已经包含JSON格式字符串.

How to get a JSON_OBJECT() of 3 columns whose some contains already a JSON format string.

这是我的示例，其中info字段包含一个json字符串，因此所有此对象都被转义了:

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`  <<-- JSON stored string in varchar(100)
            )
        ),
        ']}'
    ) AS `elements`,
FROM `table` T1
INNER JOIN `table2` T2
    ON T1.`id` = T2.`fk_t1_id`
GROUP BY T1.`id`

也许，将新的存储功能用于JSON格式会更好，但是我尚未对其进行测试.你觉得呢?

Maybe, use the new storage functions for JSON format will be better, but I didn't test it yet. What do you think ?

推荐答案

我发现的最佳解决方案是将 JSON_MERGE()与JSON_OBJECT()和CONCAT()结合使用

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`

这篇关于MySQL JSON_OBJECT()，其中某些字段已经包含JSON字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！