MySQL 嵌套JSON解析

MySQL自5.7之后开始支持json类型，相应的解析函数主要是json_extract()。

查询MySQL版本

select version()

示例

示例json1

未经过转义的json串

{"l1":{"l1_1":["l1_1_1","l1_1_2"],"l1_2":{"l1_2_1":121,"l1_2_2":"122"}},"l2":{"l2_1":null,"l2_2":true,"l2_3":{}}}

示例json2

经过转义后的json串

{"t_key":"haha","t_value":"{\"id\":\"14\",\"timestamp\":1539768556,\"type\":1}","test":2}

通过json_extract()可以获取json里面value对于的值:

mysql> select json_extract(field1, '$.t_key') from table1;
+----------------------------------------+
| json_extract(field1, '$.t_key')        |
+----------------------------------------+
| "haha"                                 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract(field1, '$.t_value') from table1;
+-------------------------------------------------------+
| json_extract(field1, '$.t_value')              |
+-------------------------------------------------------+
| "{\"id\":\"14\",\"timestamp\":1539768556,\"type\":1}" |
+-------------------------------------------------------+
1 row in set (0.00 sec)

如果想去除两侧引号，可以先做类型转换再做trim:

mysql> select trim(both '"' from cast(json_extract(field1, '$.t_value') as char)) from table1;
+----------------------------------------------------------------------------+
| trim(both '"' from cast(json_extract(field1, '$.t_value') as char)) |
+----------------------------------------------------------------------------+
| {\"id\":\"14\",\"timestamp\":1539768556,\"type\":1}                        |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果要在MySQL中对解析后的json再进行解析，则需要加上json_unquote函数以去掉escape character：

mysql> select json_unquote(json_extract(field1, '$.t_value')) from table1; 
+--------------------------------------------------------+
| json_unquote(json_extract(field1, '$.t_value')) |
+--------------------------------------------------------+
| {"id":"14","timestamp":1539768556,"type":1}            |
+--------------------------------------------------------+
1 row in set (0.00 sec)

如果要对解析过后的json继续解析，则在上一步基础上嵌套json_extract()

mysql> select json_extract(json_unquote(json_extract(field1, '$.t_value')), '$.timestamp') from table1;
+-------------------------------------------------------------------------------------+
| json_extract(json_unquote(json_extract(field1, '$.t_value')), '$.timestamp') |
+-------------------------------------------------------------------------------------+
| 1539768556                                                                          |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

附

JSON_UNQUOTE() Special Character Escape Sequences

Escape Sequence	Character Represented by Sequence
\"	A double quote (") character
\b	A backspace character
\f	A formfeed character
\n	A newline (linefeed) character
\r	A carriage return character
\t	A tab character
\\	A backslash () character
\uXXXX	UTF-8 bytes for Unicode value XXXX

注

json_extract的等效操作符是->;
json_unquote(json_extract())的等效操作符是->>;

参考资料1
参考资料2
参考资料3