mysql 获取json数据类型 mysql 读取json

前言

从 MySQL 5.7.8 开始，MySQL 支持由RFC 7159定义的本机JSON数据类型，该类型可有效访问 JSON(JavaScript 对象 表 示法)文档中的数据。与将 JSON 格式的字符串存储在字符串列中相比，JSON数据类型具有以下优点：

• 自动验证存储在JSON列中的 JSON 文档。无效的文档会产生错误。
• 优化的存储格式。存储在JSON列中的 JSON 文档将转换为内部格式，从而可以快速读取文档元素。当服务器稍后必须读取以该二进制格式存储的 JSON 值时，则无需从文本表示形式解析该值。二进制格式的结构使服务器可以直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。¹

自动校验

在 MySQL 中，JSON 值被编写为字符串。 MySQL 会解析在需要 JSON 值的上下文中使用的任何字符串，如果该字符串作为 JSON 无效，则会产生错误。这些上下文包括将值插入具有JSON数据类型的列中，以及将参数传递给需要 JSON 值的函数，如下所示示例说明：

• 如果值是有效的 JSON 值，尝试将值插入JSON列会成功，但如果不是，则尝试失败：

mysql> create table j1 (jdoc JSON );
Query OK, 0 rows affected (0.27 sec)

mysql> insert into j1 values(' {"zhangsan":"man","lisi":"man"} ');
Query OK, 1 row affected (0.06 sec)

mysql> insert into j1 values('[dog ');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 1 in value for column 'j1.jdoc'.

TYPE函数

• JSON_TYPE()函数需要一个 JSON 参数，并尝试将其解析为 JSON 值。如果有效，则返回值的 JSON 类型，否则返回错误：

mysql> select JSON_TYPE('""');
+-----------------+
| JSON_TYPE('""') |
+-----------------+
| STRING          |
+-----------------+
1 row in set (0.00 sec)

mysql> select JSON_TYPE('{"apple":"food"}');
+-------------------------------+
| JSON_TYPE('{"apple":"food"}') |
+-------------------------------+
| OBJECT                        |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select JSON_TYPE('[1,2,3]');
+----------------------+
| JSON_TYPE('[1,2,3]') |
+----------------------+
| ARRAY                |
+----------------------+
1 row in set (0.01 sec)

mysql> select JSON_TYPE('[1,2,3');
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Missing a comma or ']' after an array element." at position 6.

mysql> select JSON_TYPE('{"apple":"food}');
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Missing a closing quotation mark in string." at position 15.

JSON函数

其他相关的函数：

Name	Description
->	评估路径后从 JSON 列返回值；等效于 JSON_EXTRACT()。
->>(引入 5.7.13)	评估路径并取消引用结果后，从 JSON 列返回值；等效于 JSON_UNQUOTE(JSON_EXTRACT())。
JSON_APPEND() (deprecated)	将数据附加到 JSON 文档
JSON_ARRAY()	创建 JSON 数组
JSON_ARRAY_APPEND()	将数据附加到 JSON 文档
JSON_ARRAY_INSERT()	插入 JSON 数组
JSON_CONTAINS()	JSON 文档是否在路径中包含特定对象
JSON_CONTAINS_PATH()	JSON 文档是否在路径中包含任何数据
JSON_DEPTH()	JSON 文档的最大深度
JSON_EXTRACT()	从 JSON 文档返回数据
JSON_INSERT()	将数据插入 JSON 文档
JSON_KEYS()	JSON 文档中的键数组
JSON_LENGTH()	JSON 文档中的元素数
JSON_MERGE()(不建议使用 5.7.22)	合并 JSON 文档，保留重复的键。 JSON_MERGE_PRESERVE()的已弃用同义词
JSON_MERGE_PATCH()(引入 5.7.22)	合并 JSON 文档，替换重复键的值
JSON_MERGE_PRESERVE()(引入 5.7.22)	合并 JSON 文档，保留重复的键
JSON_OBJECT()	创建 JSON 对象
JSON_PRETTY()(引入 5.7.22)	以易于阅读的格式打印 JSON 文档
JSON_QUOTE()	引用 JSON 文档
JSON_REMOVE()	从 JSON 文档中删除数据
JSON_REPLACE()	替换 JSON 文档中的值
JSON_SEARCH()	JSON 文档中值的路径
JSON_SET()	将数据插入 JSON 文档
JSON_STORAGE_SIZE()(引入 5.7.22)	表
JSON_TYPE()	JSON 值类型
JSON_UNQUOTE()	取消引用 JSON 值
JSON_VALID()	JSON 值是否有效

两个特殊符号

• 列路径运算符->( column->path)

在 MySQL 5.7.9 和更高版本中，与两个参数一起使用时，->运算符用作JSON_EXTRACT()函数的别名，两个参数是左侧的列标识符，右侧的 JSON 路径是根据 JSON 文档评估的(列值) 。您可以在 SQL 语句中的任何位置使用此类表达式代替列标识符。
以下两种操作是等效的。

mysql> select jdoc,JSON_EXTRACT(jdoc,"$.lisi") from j1;
+------------------------------------+-----------------------------+
| jdoc                               | JSON_EXTRACT(jdoc,"$.lisi") |
+------------------------------------+-----------------------------+
| {"lisi": "man", "zhangsan": "man"} | "man"                       |
+------------------------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> select jdoc,jdoc->"$.lisi" from j1;
+------------------------------------+----------------+
| jdoc                               | jdoc->"$.lisi" |
+------------------------------------+----------------+
| {"lisi": "man", "zhangsan": "man"} | "man"          |
+------------------------------------+----------------+
1 row in set (0.01 sec)

• 内联路径运算符->>

但不包括周围的引号或任何转义符，使用内联路径运算符->>，如下所示：

mysql> select jdoc,jdoc->>"$.lisi" from j1;
+------------------------------------+-----------------+
| jdoc                               | jdoc->>"$.lisi" |
+------------------------------------+-----------------+
| {"lisi": "man", "zhangsan": "man"} | man             |
+------------------------------------+-----------------+
1 row in set (0.01 sec)

路径语法使用前导$可以表示当前 JSON 文档，也可以表述JSON数组中元素$[0]表示第一个元素，$后可以跟通配符*、**

创建一条数据：

mysql> insert into j1 values('[1,2,{"array":[4,5],"str":"b"},[7,8]]');
Query OK, 1 row affected (0.00 sec)

查看使用：

mysql> select jdoc->"$[0]" from j1;
+--------------+
| jdoc->"$[0]" |
+--------------+
| 1            |
+--------------+
1 row in set (0.01 sec)

mysql> select jdoc->"$[*]" from j1;
+-----------------------------------------------+
| jdoc->"$[*]"                                  |
+-----------------------------------------------+
| [1, 2, {"str": "b", "array": [4, 5]}, [7, 8]] |
+-----------------------------------------------+
1 row in set (0.00 sec)

简单写点，大部分都是文档内容，方便温习。

---