MySQL: JSON: ->> operator

[dushaoshuai]

-> 和 ->>

-> 提取 JSON 文档中的 string 值。

->> 提取 JSON 文档中的 string 值并且移除双引号。

两者相比较，->> 应该是大部分时候能够满足我们需要的那个。

mysql> CREATE TABLE `json_unquote` ( `j` JSON );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `json_unquote` VALUE ( '{"id": 5, "name": "shao"}' );
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM `json_unquote`;
+---------------------------+
| j                         |
+---------------------------+
| {"id": 5, "name": "shao"} |
+---------------------------+
1 row in set (0.04 sec)

mysql> SELECT `j`->'$.name',
    `j`->'$.name' LIKE 's%',
    `j`->>'$.name',
    `j`->>'$.name' LIKE 's%'
    FROM `json_unquote`;
+---------------+-------------------------+----------------+--------------------------+
| `j`->'$.name' | `j`->'$.name' LIKE 's%' | `j`->>'$.name' | `j`->>'$.name' LIKE 's%' |
+---------------+-------------------------+----------------+--------------------------+
| "shao"        |                       0 | shao           |                        1 |
+---------------+-------------------------+----------------+--------------------------+
1 row in set (0.05 sec)

参见

column->>path