在mysql中将JSON数组转换为行数据[转]

https://blog.csdn.net/fqjia2008/article/details/106927393/

SELECT *, JSON_UNQUOTE(JSON_EXTRACT(value , CONCAT('$[', idx, ']'))) AS query_id FROM icehrmdb_dev.CustomFieldValues 


JOIN (
    SELECT  0  AS idx UNION
    SELECT  1  AS idx UNION
    SELECT  2  AS idx UNION
    SELECT  3  AS idx UNION
    SELECT  4  AS idx UNION
    SELECT  5  AS idx UNION
    SELECT  6  AS idx UNION
    SELECT  7  AS idx UNION
    SELECT  8  AS idx UNION
    SELECT  9  AS idx UNION
    SELECT  10 AS idx UNION
    SELECT  11 AS idx UNION
    SELECT  12 AS idx UNION
    SELECT  13 AS idx UNION
    SELECT  14 AS idx UNION
    SELECT  15 AS idx UNION
    SELECT  16 AS idx UNION
    SELECT  17 AS idx UNION
    SELECT  18 AS idx UNION
    SELECT  19 AS idx UNION
    SELECT  20 AS idx UNION
    SELECT  21 AS idx UNION
    SELECT  22 AS idx UNION
    SELECT  23 AS idx UNION
    SELECT  24 AS idx UNION
    SELECT  25 AS idx UNION
    SELECT  26 AS idx UNION
    SELECT  27 AS idx UNION
    SELECT  28 AS idx UNION
    SELECT  29 AS idx UNION
    SELECT  30 AS idx UNION
    SELECT  31 AS idx UNION
    SELECT  32
    -- query_id_str（1024）最多存储33个query_id（31）
) AS indexes
-- 过滤空数据
WHERE JSON_EXTRACT(value, CONCAT('$[', idx, ']')) IS NOT NULL

and  name = 'sub_sector_1'