需求: 統(tǒng)計(jì)用戶各個(gè)維度信息，放入json串中

參考mysql json文檔(注意: json 類型時(shí)mysql5.7以上才出來，以前可以用text類型代替):

里面包含各種json 函數(shù)的實(shí)例和用戶，非常詳細(xì): https://www.cnblogs.com/ooo0/p/9309277.html
常用的幾個(gè)json 函數(shù):

# 查詢json長(zhǎng)度
select JSON_LENGTH('[{"uid":108,"type":2,"aa":1},{"uid":1008,"type":2,"aa":1}]')
#獲取json中key的值
select JSON_EXTRACT('[{"uid":108,"type":2},{"uid":1008,"type":2}]',"$[*].uid")

使用mysql中聚合函數(shù)GROUP_CONCAT時(shí)，需要注意:

#默認(rèn) GROUP_CONCAT 大小是1024，如果超出范圍，會(huì)造成數(shù)據(jù)丟失，代碼中加上如下，可以解決問題
SET SESSION  group_concat_max_len=1024000;

下面直接上生產(chǎn)代碼

# CONCAT用于拼接json， GROUP_CONCAT 用于分組排序
SELECT p.device_id
    , CONCAT("[", GROUP_CONCAT(h.accunt_info ORDER BY JSON_EXTRACT(h.accunt_info, "$.dateline") SEPARATOR ','), "]") AS account_array
FROM (
    SELECT device_id
    FROM db_channel.xxxx
    WHERE device_id IS NOT NULL
        AND device_id != ""
    GROUP BY device_id
) p
    JOIN (
        SELECT reg.deviceid
            , CONCAT('{"uid":', reg.uid, ',"dateline":"', ac.dateline, '","type":', reg.type, '}') AS accunt_info
        FROM db_log.xxxx
            JOIN (
                SELECT uid, dateline
                FROM db_user.xxx
                WHERE account_type = 1
                    AND status != 2
            ) ac
            ON reg.uid = ac.uid
        WHERE deviceid IS NOT NULL
    ) h
    ON p.device_id = h.deviceid
GROUP BY p.device_id