MySQL 嵌套JSON解析

╰+哭是因爲堅強的太久メ 2021-12-21 09:39 471阅读 0赞

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 ######

<table> 
 <thead> 
  <tr> 
   <th>Escape Sequence</th> 
   <th>Character Represented by Sequence</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>\"</td> 
   <td>A double quote (") character</td> 
  </tr> 
  <tr> 
   <td>\b</td> 
   <td>A backspace character</td> 
  </tr> 
  <tr> 
   <td>\f</td> 
   <td>A formfeed character</td> 
  </tr> 
  <tr> 
   <td>\n</td> 
   <td>A newline (linefeed) character</td> 
  </tr> 
  <tr> 
   <td>\r</td> 
   <td>A carriage return character</td> 
  </tr> 
  <tr> 
   <td>\t</td> 
   <td>A tab character</td> 
  </tr> 
  <tr> 
   <td>\\</td> 
   <td>A backslash () character</td> 
  </tr> 
  <tr> 
   <td>\uXXXX</td> 
   <td>UTF-8 bytes for Unicode value XXXX</td> 
  </tr> 
 </tbody> 
</table>

#### 注 ####

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

[参考资料1][1]  
[参考资料2][2]  
[参考资料3][3]

[1]: https://www.cnblogs.com/wggj/p/10535998.html
[2]: https://www.cnblogs.com/iwangzheng/p/8000207.html
[3]: https://blog.csdn.net/moshowgame/article/details/81016482