MYSQL8.0-JSON函数简单示例-JSON_EXTRACT|JSON_VALUE|JSON_TABLE
JSON类型在日常应用开发中，用得很少，个人通常用于存储常常变化的配置参数。
它适用于什么业务场景，不好说。就好像许多年前读到的一篇文章，说有个国外公司利用ORACLE的CLOB/BLOB管理一些信息，好像还很成功的样子。
所以，客观地说，JSON的价值取决于工程师的能力和业务场景。
MYSQL8.0提供了许多的函数，详细的参阅官网的文档：
https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
一、定义JSON字段
MYSQL提供了JSON类型字段，它至少某个程度上和字符类型的表现是一致的。
它和字符类型的主要区别在于：
- MYSQL会验证值的合法性。如果定义为varchar,text，那么MYSQL就不会去验证。
- 优化存储格式，以二进制的形式存储。读取的时候无需先转为json对象。所以读取更快
总结起来，还是直接定义为JSON类型更好。
至于具体业务场景中，是定义为JSON还是VARCHAR/TEXT之类的，需要看具体需要。
如果我们频繁存取JSON，那么通常还是建议使用JSON类型，而不是字符类型来存储JSON数据。
二、查找/获取值
这是最常用的函数类。
在很多情况下，我们并不使用MYSQL自身提供的JSON函数。
例如我需要把系统配置信息序列化到数据库，那么一般情况下，会直接通过面向对象的序列化功能，把对象序列化为JSON，在直接存入数据库。
但是常常会希望在SQL中获取JSON的某个值，而不希望把数据载入内存，再写代码解析。
MYSQL8关于查询的函数有许多;
JSON_CONTAINS(target, candidate[, path]) -- 类似exists 或者 having
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...) --同上
JSON_EXTRACT(json_doc, path[, path] ...) -- 读取某个键值
JSON_VALUE( -- 类似JSON_EXTRACT,但是比后者强，因为JSON_VALUE还可以把结果转为特定类型json_doc, path)
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) -- 通过键值获取键路径
... 略
本文只介绍JSON_EXTRACT和JSON_VALUE.
在开始介绍前，先介绍如何访问JSON对象的特定部分的键值，或者说如何书写键路径。
例如有JSON对象文本如下：
{
"owner": "辛弃疾",
"book": [
{
"name": "武德充沛的必要性",
"price": 100
},
{
"name": "哲学",
"price": 90.5
}
],
"content":{
"totalPage":456,
"totalWords":400000
}
}
这个路径必须以“$.”开头，后面就很好理解--和JAVA包路径一样，不同层级之间通过点号分隔。如果有数组，那么用"[]"表示，下标从0开始。
下面分别写两中路径语法例子：
- 不带数组-$.k1.k2.k3....kn,其中n>=1
- 带数组-$.k1.k2[i].k3....kn ,其中n>=1,i>=0
三、返回结果集
上官方的表达式：
JSON_TABLE(
expr,
path COLUMNS (column_list)
) [AS] alias
column_list:
column[, column][, ...]
column:
name FOR ORDINALITY
| name type PATH string path [on_empty] [on_error]
| name type EXISTS PATH string path
| NESTED [PATH] path COLUMNS (column_list)
on_empty:
{NULL | DEFAULT json_string | ERROR} ON EMPTY
on_error:
{NULL | DEFAULT json_string | ERROR} ON ERROR
中括号，逗号以及竖线的含义请参阅mysql官方文档。表达式不难理解，只要看了例子。
但是如果涉及到嵌套等，就会有那么一点复杂了。
on empty,on error字句允许在没有或者发生错误的时候返回替代结果。
当至少在MYSQL8.0.X版本（截至20220623在8.0.29版本）中，这个功能还不是很完美：必须先关联才可以使用。
注：不关联也可以，不过对于我而言意义不大。
在我的一般业务场景中，我希望这样：
SELECT * FROM json_table(json_extract(t_json.book,'$.book'), "$[*]" COLUMNS( NAME VARCHAR(60) path "$.name", price DECIMAL(10,2) path "$.price" ) ) AS JJ;
但是这个不行的，会提示：
错误代码： 1109
Unknown table 't_json' in a table function argument
四、例子
数据库版本：centos8 mysql8.0.27 企业版（社区版）
建表T_JSON：
CREATE TABLE `t_json` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `book` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
插入数据到BOOK字段，内容如下（id略,脚本略）:
{
"book": [
{
"name": "中国的未来",
"price": 100
},
{
"name": "真理-数学",
"price": 100
}
],
"owner": "luzhifei",
"content": {
"totalPage": 945,
"totalWords": 9745556
}
}
------------------------
{
"book": [
{
"name": "武德充沛的必要性",
"price": 100
},
{
"name": "哲学",
"price": 90.5
}
],
"owner": "辛弃疾",
"content": {
"totalPage": 456,
"totalWords": 400000
}
}
1.JSON_VALUE
select JSON_VALUE(book,"$.content.totalPage" RETURNING decimal(10,2)) from t_json;
select JSON_VALUE(book,"$.content.totalPage") from t_json;
注：在8.0.20(windows)下，JSON_VALUE并不存在。
由于JSON_VALUE仅仅等价于(官方):
SELECT CAST( JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) ) AS type );
所以，不使用JSON_VALUE,而使用替代脚本也可以。
2.JSON_EXTRACT
SELECT json_extract(book,'$.book') FROM t_json;
查询特定数组中某个key值：SELECT json_extract(book,'$.book[1].name') FROM t_json where id=3;
补充说明，可以使用->或者->>替代JSON_EXTRACT，具体例子见后文JSON_TABLE。
其中：
- -> 表示获取可能带有双引号的值
- ->> 获取不带双引号的值。通常我们使用这个。
3.JSON_TABLE
SELECT v.id,book->>"$.owner" AS author, book->"$.content.totalPage" AS totalPage, t.name,t.price FROM t_json v,
json_table(json_extract(v.book,'$.book'),
"$[*]" COLUMNS(
NAME VARCHAR(60) path "$.name",
price DECIMAL(10,2) path "$.price"
)
) AS t;
SELECT v.id,json_extract(book,"$.owner") AS author, json_extract(book,"$.content.totalPage") AS totalPage, t.name,t.price FROM t_json v,
json_table(json_extract(v.book,'$.book'),
"$[*]" COLUMNS(
NAME VARCHAR(60) path "$.name",
price DECIMAL(10,2) path "$.price"
)
) AS t;
五、小结
JSON字段适用场景比较狭窄，尤其在一般应用开发领域，所以没有怎么关注。
JSON作为MYSQL一种类型补充，有了会方便点，没有也无所谓。
对于大部分程序员而言，JSON类型最大的价值在某些情况下可以于少写了一些代码，前提是你得恰当地适用。
如果业务场景中数据巨量，而且查询更新等都很频繁，不建议你适用这个数据类型，炫技除外！
于我而言，它的主要意义在于黑盒化信息，封装特定的业务，如果使用恰当的话。
例如某个配置又想添加一些属性，那么不需要对表格做任何修改，仅仅只需要修改下后台实现代码（通常情况）。如此，某种程度上有助于实现某种程度的开闭原则。
如果有需要大量使用非结构化数据和JSON的场景，可以考虑使用其它非关系数据库。
