是否可以将文本扩展为列？

Question

具有一个带有长文本类型名称参数的字段的作业表，其格式键为: value，anotherKey: anotherValue中的值为​​。可以通过查询将这些键展开为列。

例如，在params字段中具有以下值

{
    "field964" : "Value",
    "field966" : "Value",
    "field967" : "Value",
    "field968" : "Value",
    "field969" : "Value",
    "field970" : "Value",
    "field971" : "Value",
    "field972" : "Value",
    "field973" : "Value",
    "field974" : "Value",
    "field975" : "Value",
    "field976" : "Value",
    "field977" : "Value",
    "field978" : "Value",
    "field979" : "Value",
    "field980" : "Value",
    "field981" : "Value",
    "field982" : "Value"
}

它能否将字段转换为一个结果，其中每个字段对应文本的一个键，值是相应键的值？

提前感谢

Answer 1

嘿..。帮你解决了这个任务！

这是SQLFiddle：http://rextester.com/XNFKU52341

获取您的测试数据：

CREATE TABLE t20_table (value JSON);
INSERT INTO t20_table VALUES ('{
    "field964" : \"Value1\",
    "field966" : \"Value2\",
    "field967" : \"Value3\",
    "field968" : \"Value4\",
    "field969" : \"Value5\",
    "field970" : \"Value6\",
    "field971" : \"Value7\",
    "field972" : \"Value8\",
    "field973" : \"Value9\",
    "field974" : \"Value10\",
    "field975" : \"Value11\",
    "field976" : \"Value12\",
    "field977" : \"Value13\",
    "field978" : \"Value14\",
    "field979" : \"Value15\",
    "field980" : \"Value16\",
    "field981" : \"Value17\",
    "field982" : \"Value18\"
}');

实际解决办法：

# generate range between 0 and N - number of keys
CREATE TABLE incr (id INTEGER PRIMARY KEY AUTO_INCREMENT);

CREATE PROCEDURE xrange(value INTEGER)
BEGIN
  DECLARE v1 INT DEFAULT 0;
  WHILE v1 < value DO
    INSERT incr VALUES (NULL);
    SET v1 = v1 + 1;
  END WHILE;
END;

SET @keys = (SELECT JSON_KEYS(value) FROM t20_table);
SET @length = (SELECT JSON_LENGTH(value->"$.*") FROM t20_table);

# generating number range from 0 to @length
CALL xrange(@length);

# generate SELECT statement with JSON_EXTRACT
SELECT GROUP_CONCAT('JSON_EXTRACT(value->"$.*", ', '"$[', id - 1,']") AS ', JSON_EXTRACT(@keys, CONCAT("$[", id - 1, "]")), '') 
INTO @query
FROM incr;

SELECT @query;

SET @query = CONCAT('SELECT ', @query, ' FROM t20_table;');
PREPARE q FROM @query;

EXECUTE q;

输出：

​

​