mysql将xml数据或者json数据转换为表格。

Question

我需要将一个xml的数据或者json数据的字符串转化为一个mysql中的表格形式。
json_extract函数只能处理单个json数据，无法处理json数组,ExtractValue函数取出来的数据是拼接在一起的，不知道怎么分开，有没有其他办法呢，请教各位大神

数据样例：
[{"fCategoryId":"796","fCondition":"0.8"},{"fCategoryId":"730","fCondition":"0.05"},{"fCategoryId":"731","fCondition":"0.05"}]

select CONVERT(json_extract('{"fCategoryId":"796","fCondition":"0.8"}','$.fCondition'),DECIMAL(5,2)) AS fcid;

若是输入json数组，无输出结果

<jrt>

<item>
    <fCategoryId>1006</fCategoryId>
    <fCondition>0.40</fCondition>
</item>
<item>
    <fCategoryId>1007</fCategoryId>
    <fCondition>0.30</fCondition>
</item>
<item>
    <fCategoryId>1008</fCategoryId>
    <fCondition>0.30</fCondition>
</item>

</jrt>

SET @xmlstring = '<jrt><item><fCategoryId>1006</fCategoryId><fCondition>0.40</fCondition></item><item><fCategoryId>1007</fCategoryId><fCondition>0.30</fCondition></item><item><fCategoryId>1008</fCategoryId><fCondition>0.30</fCondition></item></jrt>'
;

SELECT ExtractValue(@xmlstring, '/jrt/item/fCategoryId') as fCategoryId,ExtractValue(@xmlstring, '/jrt/item/fCondition') as fCondition;

取得的数据形式是
fCategoryId | fCondition
1006 1007 1008 | 0.40 0.30 0.30

Answer 1

stackoverflow给的答案：需要预先知道数组的最大长度
https://stackoverflow.com/que...

CREATE TABLE t1 (rec_num INT, jdoc JSON);
INSERT INTO t1 VALUES 
  (1, '{"fish": ["red", "blue"]}'), 
  (2, '{"fish": ["one", "two", "three"]}');

SELECT
  rec_num,
  idx,
  JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) AS fishes
FROM t1
  -- Inline table of sequential values to index into JSON array
JOIN ( 
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION
  -- ... continue as needed to max length of JSON array
  SELECT  3
  ) AS indexes
WHERE JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) IS NOT NULL
ORDER BY rec_num, idx;

后面有个类似的例子，只是把1,2,3,4的序号存在另外一张表里面，灵活度更高。

CREATE TABLE `t_list_row` (
`_row` int(10) unsigned NOT NULL,
PRIMARY KEY (`_row`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT t_list_row VALUES (0), (1), (2) .... (65535) big enough;

SET @j = '[1, 2, 3]';
SELECT 
JSON_EXTRACT(@j, CONCAT('$[', B._row, ']'))
FROM (SELECT @j AS B) AS A
INNER JOIN t_list_row AS B ON B._row < JSON_LENGTH(@j);