MySQL8拆分json格式数据

JSON_TABLE()函数说明

MySQL8.0.4，新增JSON_TABLE()函数。此函数可以将JSON 数据转换为指定列数据返回。
JSON_TABLE()函数的使用方法参考官方文档：
https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

例1：直接转换json格式数据

mysql> SELECT * FROM 
       JSON_TABLE(
	   '{"a": 23, "b": 27, "c": 1}', 
	    '$' 
		COLUMNS(
		    at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY
			,bt VARCHAR(10) PATH '$.b' DEFAULT '2' ON EMPTY
			,ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY 
		) 
		) AS tt;

+------+------+------+
| at   | bt   | ct   |
+------+------+------+
| 23   | 27   | 1    |
+------+------+------+
1 row in set (0.00 sec)

mysql> SELECT
    ->     *
    -> FROM
    ->     JSON_TABLE(
    ->         '[{"a": 23, "b": "27", "c": 1},{"a": 23, "c": 1},{"a": 23, "b": "", "c": 1}]',
    ->         '$[*]' COLUMNS(
    ->             at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY,
    ->             bt VARCHAR(10) PATH '$.b' DEFAULT '1' ON EMPTY,
    ->             ct VARCHAR(10) PATH '$.c' DEFAULT '1' ON EMPTY
    ->         )
    ->     ) AS tt;
+------+------+------+
| at   | bt   | ct   |
+------+------+------+
| 23   | 27   | 1    |
| 23   | 1    | 1    |
| 23   |      | 1    |
+------+------+------+

例2：将表中json格式列内容转换为指定列数据

-- 创建测试表：
CREATE TABLE t1 (c1 INT, c2 CHAR(1), c3 JSON);

INSERT INTO t1 () VALUES
	ROW(1, 'z', JSON_OBJECT('a', 23, 'b', 27, 'c', 1)),
	ROW(1, 'y', JSON_OBJECT('a', 44, 'b', 22, 'c', 11)),
	ROW(2, 'x', JSON_OBJECT('b', 1, 'c', 15)),
	ROW(3, 'w', JSON_OBJECT('a', 5, 'b', 6, 'c', 7)),
	ROW(5, 'v', JSON_OBJECT('a', 123, 'c', 1111))
;
-- 数据转换测试：
mysql> SELECT c1, c2, JSON_EXTRACT(c3, '$.*') ,tt.*
    -> FROM t1 AS m 
    -> JOIN 
    -> JSON_TABLE(
    ->   m.c3, 
    ->   '$' 
    ->   COLUMNS(
    ->     at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY, 
    ->     bt VARCHAR(10) PATH '$.b' DEFAULT '2' ON EMPTY, 
    ->     ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY
    ->   )
    -> ) AS tt;
+------+------+-------------------------+------+------+------+
| c1   | c2   | JSON_EXTRACT(c3, '$.*') | at   | bt   | ct   |
+------+------+-------------------------+------+------+------+
|    1 | z    | [23, 27, 1]             | 23   | 27   | 1    |
|    1 | y    | [44, 22, 11]            | 44   | 22   | 11   |
|    2 | x    | [1, 15]                 | 1    | 1    | 15   |
|    3 | w    | [5, 6, 7]               | 5    | 6    | 7    |
|    5 | v    | [123, 1111]             | 123  | 2    | 1111 |
+------+------+-------------------------+------+------+------+
5 rows in set (0.00 sec)