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

本文主要是介绍在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'

这篇关于在mysql中将JSON数组转换为行数据[转]的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！