• 基础
  • 函数速查表
  • sql优化
  • 创建JSON值
  • 查询
  • 规范化
  • 合并
  • 路径表达式和搜索
  • 修改
  • 比较和排序
  • 转换
  • 聚合

基础

函数速查表

Name	Description	Introduced	Deprecated
->	Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
->>	Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_ARRAY()	Create JSON array
JSON_ARRAY_APPEND()	Append data to JSON document
JSON_ARRAY_INSERT()	Insert into JSON array
JSON_CONTAINS()	Whether JSON document contains specific object at path
JSON_CONTAINS_PATH()	Whether JSON document contains any data at path
JSON_DEPTH()	Maximum depth of JSON document
JSON_EXTRACT()	Return data from JSON document
JSON_INSERT()	Insert data into JSON document
JSON_KEYS()	Array of keys from JSON document
JSON_LENGTH()	Number of elements in JSON document
JSON_MERGE()	Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()		Yes
JSON_MERGE_PATCH()	Merge JSON documents, replacing values of duplicate keys
JSON_MERGE_PRESERVE()	Merge JSON documents, preserving duplicate keys
JSON_OBJECT()	Create JSON object
JSON_OVERLAPS()	Compares two JSON documents, returns TRUE (1) if these have any key-value pairs or array elements in common, otherwise FALSE (0)	8.0.17
JSON_PRETTY()	Print a JSON document in human-readable format
JSON_QUOTE()	Quote JSON document
JSON_REMOVE()	Remove data from JSON document
JSON_REPLACE()	Replace values in JSON document
JSON_SCHEMA_VALID()	Validate JSON document against JSON schema; returns TRUE/1 if document validates against schema, or FALSE/0 if it does not	8.0.17
JSON_SCHEMA_VALIDATION_REPORT()	Validate JSON document against JSON schema; returns report in JSON format on outcome on validation including success or failure and reasons for failure	8.0.17
JSON_SEARCH()	Path to value within JSON document
JSON_SET()	Insert data into JSON document
JSON_STORAGE_FREE()	Freed space within binary representation of JSON column value following partial update
JSON_STORAGE_SIZE()	Space used for storage of binary representation of a JSON document
JSON_TABLE()	Return data from a JSON expression as a relational table
JSON_TYPE()	Type of JSON value
JSON_UNQUOTE()	Unquote JSON value
JSON_VALID()	Whether JSON value is valid
JSON_VALUE()	Extract value from JSON document at location pointed to by path provided; return this value as VARCHAR(512) or specified type	8.0.21
MEMBER OF()	Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0)	8.0.17

sql优化

写出高性能sql说白了就是对优化器的口味

1. 在 MySQL 8.0中，优化器可以对 JSON 列执行部分原位更新，而不是删除旧文档并将新文档完整地写入该列。
   需要满足的条件:

• UPDATE 语句使用 JSON _ SET ()、 JSON _ REPLACE ()或 JSON _ REMOVE ()三个函数中的任何一个来更新列。
• 输入列和目标列必须是同一列。
• 所有更改都用新的数组或对象值替换现有的数组或对象值，并且不向父对象或数组添加任何新元素。
• 被替换的值必须至少与替换值一样大。换句话说，新值不能大于旧值。

创建JSON值

• 有效的JSON值，否则报错
  INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
• JSON _ TYPE ()函数期望一个 JSON 参数，并尝试将其解析为一个 JSON 值。如果值有效，则返回值的 JSON 类型，否则将产生错误。
  MySQL 使用 utf8mb4字符集和 utf8mb4 _ bin 排序规则处理 JSON 上下文中使用的字符串。其他字符集中的字符串根据需要转换为 utf8mb4。(对于 ascii 或 utf8字符集中的字符串，不需要转换，因为 ascii 和 utf8是 utf8mb4的子集。)
• JSON _ ARRAY ()接受一个(可能是空的)值列表，并返回一个包含这些值的 JSON 数组
• JSON _ OBJECT ()接受一个(可能是空的)键值对列表，并返回一个包含这些键值对的 JSON 对象
• JSON _ MERGE _ PRESERVE ()接受两个或多个 JSON 文档并返回组合结果

查询

column-path 操作符-> 和 内联路径操作符->>（不包括周围的引号或任何转义符）

mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+

mysql> SELECT sentence->>"$.mascot" FROM facts;
+-----------------------------------------+
| sentence->>"$.mascot"                   |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+

规范化

文本插入和JSON_OBJECT函数都会依据“最后的重复键获胜”规则，例如{"a": 1, "a": 2}会得到{"a": 2}

合并

JSON_MERGE_PRESERVE与JSON_MERGE_PATCH区别
简单的说：json_merge_patch是覆盖，json_merge_preserve是组合（转化为数组或连接数组）

• 对于json对象
  如果参数列表中的json文档之间属性没有交集，则两个函数表现一致；否则，json_merge_preserve将相同键的值组成数组值，如果已经有数组值，则组合进这个数组或者将数组连接起来；重要的是如果第二个文档中某个属性为null并且第一个文档中有此属性，则结果中无此属性。

JSON _ MERGE _ PATCH ()移除第一个对象中的任何成员，并在第二个对象中使用匹配的键，前提是与第二个对象中的键关联的值不为 JSON null。

• 对于json数组
  json_merge_patch用第二个文档覆盖了第一个文档；json_merge_preserve仍旧是组合起来

路径表达式和搜索

路径表达式对于提取部分 JSON 文档或修改 JSON 文档的函数非常有用，路径语法使用一个前导 $字符来表示正在考虑的 JSON 文档。

SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
SELECT JSON_SET('"x"', '$[0]', 'a');

[ N ] 附加到选择数组的路径
[M to N] 指定数组值的子集或范围

路径可以包含*或通配符**

• . [ * ]计算 JSON 对象中所有成员的值。
• [ * ]计算 JSON 数组中所有元素的值。
• prefix**suffix 后缀计算以命名前缀开始并以命名后缀结束的所有路径。

路径包含空格
需要用引号，例如$."a fish"

last关键字
last表示最后一个元素的索引$[last-3 to last-1]
如果对非数组求值，则为元素本身

mysql> SELECT JSON_REPLACE('"Sakila"', '$[last]', 10);
+-----------------------------------------+
| JSON_REPLACE('"Sakila"', '$[last]', 10) |
+-----------------------------------------+
| 10                                      |
+-----------------------------------------+

修改

JSON _ SET ()替换现有路径的值，并为不存在的路径添加值
JSON _ INSERT ()添加新值，但不替换现有值，如果该位置已有值，则不操作原值，忽略新值
JSON _ REPLACE ()替换现有值并忽略新值
JSON _ REMOVE ()返回原始文档中删除减去一个 JSON 文档和一个或多个路径选择的值

比较和排序

可以使用 = 、 < 、 < = 、 > 、 > = 、 < > 、 ! = 和 < = > 运算符比较 JSON 值
<=>是null-safe相等比较，对于有null值的比较不会返回null
"不支持"between、in、greatest（最大）、least（最小），但是经过本人测试，是支持的。
官方文档原文为

A workaround for the comparison operators and functions just listed is to cast JSON values to a native MySQL numeric or string data type so they have a consistent non-JSON scalar type.
貌似意思是说，对于数值类型和字符串类型是支持上述比较操作的

如果类型不同，则比较结果仅由具有较高优先级的类型确定

类型名称是由 JSON _ TYPE ()函数返回的名称。在一行中一起显示的类型具有相同的优先级
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE（整数都被转换为 DOUBLE）
NULL

转换

转换规则

• 隐式转换为utf8mb4然后解析为json值；反过来json转为字符串
• NULL转为null
• 几何类型通过调用ST_AsGeoJSON()函数
• 其他类型，生成一个由单个标量值组成的 JSON 文档；反过来如果 JSON 文档由目标类型的单个标量值组成，该标量值可以强制转换为目标类型。否则，返回NULL并发出警告

应用举例

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)

聚合

对于 JSON 值的聚合，SQLNULL 值与其他数据类型一样被忽略。除了 MIN ()、 MAX ()和 GROUP _ CONCAT ()之外，非 NULL 值被转换为数值类型并进行聚合。对于数值标量的 JSON 值，转换为 number 应该会产生有意义的结果，尽管(取决于值)可能会发生截断和精度损失。转换为其他 JSON 值的数量可能不会产生有意义的结果。

注意点

• JSON对象中的 null、true、false必须小写
• 特殊字符需要\转义，文本形式插入需要\转义

  mysql> INSERT INTO facts VALUES
     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));
   mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');
  

【完结】