概览
注：MySQL版本不同，所支持的方法也会有所差异。此文中部分方法需MySQL 8
创建JSON数据
搜索JSON数据内容
修改JSON数据
查看JSON数据属性
将JSON数据转换成关系型表
其它JSON方法
详述
JSON_ARRAY
创建一个JSON数组
JSON_ARRAY([val[, val] ...])
select JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+ | JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) | +---------------------------------------------+ | [1, "abc", null, true, "10:00:24.000000"] | +---------------------------------------------+
JSON_OBJECT
创建一个JSON对象
JSON_OBJECT([key, val[, key, val] ...])
select JSON_OBJECT('id', '1047', 'name', 'Author');
+---------------------------------------------+
| JSON_OBJECT('id', '1047', 'name', 'Author') |
+---------------------------------------------+
| {"id": "1047", "name": "Author"} |
+---------------------------------------------+
JSON_QUOTE
通过在一个字符串两端添加引号并对内部特殊字符进行转义得到一个JSON数据
JSON_QUOTE(string)
select JSON_QUOTE('null'), JSON_QUOTE('"null"');
+--------------------+----------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"null"') |
+--------------------+----------------------+
| "null" | "\"null\"" |
+--------------------+----------------------+
转义字符表
JSON_UNQUOTE
JSON_QUOTE的反向操作
JSON_UNQUOTE(json_val)
select JSON_UNQUOTE('"abc"');
+-----------------------+
| JSON_UNQUOTE('"abc"') |
+-----------------------+
| abc |
+-----------------------+
JSON_CONTAINS
判断JSON数据中指定路径（path）的值是否与指定的值相等
JSON_CONTAINS(target, candidate[, path])
select JSON_CONTAINS('{"a":1}', '1', '$.a');
+--------------------------------------+
| JSON_CONTAINS('{"a":1}', '1', '$.a') |
+--------------------------------------+
| 1 |
+--------------------------------------+
select JSON_CONTAINS('{"a":1}', '0', '$.a');
+--------------------------------------+
| JSON_CONTAINS('{"a":1}', '0', '$.a') |
+--------------------------------------+
| 0 |
+--------------------------------------+
select JSON_CONTAINS('{"x":{"f1":"f"}}', '{"f1":"f"}', '$.x');
+--------------------------------------------------------+
| JSON_CONTAINS('{"x":{"f1":"f"}}', '{"f1":"f"}', '$.x') |
+--------------------------------------------------------+
| 1 |
+--------------------------------------------------------+
JSON_CONTAINS_PATH
判断JSON数据是否存在指定的路径（path）
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
select JSON_CONTAINS_PATH('{"a":1}', 'one', '$.a', '$.b');
+----------------------------------------------------+
| JSON_CONTAINS_PATH('{"a":1}', 'one', '$.a', '$.b') |
+----------------------------------------------------+
| 1 |
+----------------------------------------------------+
select JSON_CONTAINS_PATH('{"a":1}', 'all', '$.a', '$.b');
+----------------------------------------------------+
| JSON_CONTAINS_PATH('{"a":1}', 'all', '$.a', '$.b') |
+----------------------------------------------------+
| 0 |
+----------------------------------------------------+
JSON_EXTRACT
获取JSON数据中指定路径（path）的内容
JSON_EXTRACT(json_doc, path[, path] ...)
select JSON_EXTRACT('[1,2]', '$[0]');
+-------------------------------+
| JSON_EXTRACT('[1,2]', '$[0]') |
+-------------------------------+
| 1 |
+-------------------------------+
->
简易版的JSON_EXTRACT
column->path
select f2, f2->'$.a' from t1;
+------------------+-----------+
| f2 | f2->'$.a' |
+------------------+-----------+
| {"a": 1, "b": 2} | 1 |
+------------------+-----------+
->>
column->>path
这三者等价：
- JSON_UNQUOTE(JSON_EXTRACT(column, path))
- JSON_UNQUOTE(column -> path)
- column -> path
JSON_KEYS
获取JSON数据中的key
JSON_KEYS(json_doc[, path])
select JSON_KEYS('{"a":1, "b":2}');
+-----------------------------+
| JSON_KEYS('{"a":1, "b":2}') |
+-----------------------------+
| ["a", "b"] |
+-----------------------------+
JSON_SEARCH
搜索JSON数据中指定JSON值的路径（path）
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
select JSON_SEARCH('["a", {"f":"a"}]', 'one', 'a');
+---------------------------------------------+
| JSON_SEARCH('["a", {"f":"a"}]', 'one', 'a') |
+---------------------------------------------+
| "$[0]" |
+---------------------------------------------+
select JSON_SEARCH('["a", {"f":"1ab2"}]', 'all', '%a%');
+---------------------------------------------+
| JSON_SEARCH('["a", {"f":"1ab2"}]', 'all', '%a%') |
+---------------------------------------------+
| ["$[0]", "$[1].f"] |
+---------------------------------------------+
JSON_ARRAY_APPEND
在JSON数组最后增加一项数据
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
select JSON_ARRAY_APPEND('[1,[2]]', '$[1]', 3);
+-----------------------------------------+
| JSON_ARRAY_APPEND('[1,[2]]', '$[1]', 3) |
+-----------------------------------------+
| [1, [2, 3]] |
+-----------------------------------------+
JSON_ARRAY_INSERT
向JSON数组插入一项数据
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
select JSON_ARRAY_INSERT('[1,[2]]', '$[1]', 3);
+-----------------------------------------+
| JSON_ARRAY_INSERT('[1,[2]]', '$[1]', 3) |
+-----------------------------------------+
| [1, 3, [2]] |
+-----------------------------------------+
JSON_INSERT
向JSON文档插入一项数据
JSON_INSERT(json_doc, path, val[, path, val] ...)
select JSON_INSERT('{}', '$.a', 3);
+-----------------------------+
| JSON_INSERT('{}', '$.a', 3) |
+-----------------------------+
| {"a": 3} |
+-----------------------------+
JSON_MERGE
合并JSON文档。MySQL 5.7.22 中，该方法已被废弃
JSON_MERGE(json_doc, json_doc[, json_doc] ...)
JSON_MERGE_PATCH
合并JSON文档。如果key已存在，将替换原值
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
JSON_MERGE_PRESERVE
合并JSON文档。如果key已存在，会合并原值和新值
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
set @x = '{"a": 1, "b": 2}',
@y = '{"a": 3, "c": 4}',
@z = '{"a": 5, "d": 6}';
select JSON_MERGE_PATCH(@x, @y, @z) AS Patch,
JSON_MERGE_PRESERVE(@x, @y, @x) AS Preserve;
+-------------------------------+------------------------------------------+
| Patch | Preserve |
+-------------------------------+------------------------------------------+
| {"a":5, "b":2, "c":4, "d": 6} | {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6} |
+-------------------------------+------------------------------------------+
JSON_REMOVE
移除JSON文档中的数据
JSON_REMOVE(json_doc, path[, path] ...)
select JSON_REMOVE('[1, 2, 3]', '$[1]');
+----------------------------------+
| JSON_REMOVE('[1, 2, 3]', '$[1]') |
+----------------------------------+
| [1, 3] |
+----------------------------------+
JSON_REPLACE
替换JSON文档中的数据
JSON_REPLACE(json_doc, path, val[, path, val] ...)
select JSON_REPLACE('[1, 2, 3]', '$[1]', 'two');
+------------------------------------------+
| JSON_REPLACE('[1, 2, 3]', '$[1]', 'two') |
+------------------------------------------+
| [1, "two", 3] |
+------------------------------------------+
JSON_SET
更新JSON文档中的数据；如果key不存在，则插入新数据
JSON_SET(json_doc, path, val[, path, val] ...)
select JSON_SET('{"a":1}', '$.a', 'one', '$.b', 2);
+---------------------------------------------+
| JSON_SET('{"a":1}', '$.a', 'one', '$.b', 2) |
+---------------------------------------------+
| {"a": "one", "b": 2} |
+---------------------------------------------+
JSON_DEPTH
获取JSON文档的最大深度
JSON_DEPTH(json_doc)
select f2, JSON_DEPTH(f2) from t1;
+----------+----------------+ | f2 | JSON_DEPTH(f2) | +----------+----------------+ | NULL | NULL | | [] | 1 | | [1] | 2 | | [1, []] | 2 | | [1, [2]] | 3 | +----------+----------------+
JSON_LENGTH
获取JSON文档的（一级）数据项数量
JSON_LENGTH(json_doc[, pathj])
select f2, JSON_LENGTH(f2) from t1;
+-------------+-----------------+ | f2 | JSON_LENGTH(f2) | +-------------+-----------------+ | NULL | NULL | | [] | 0 | | [1] | 1 | | [1, []] | 2 | | [1, [2]] | 2 | | [1, [2, 3]] | 2 | +-------------+-----------------+
JSON_TYPE
查看JSON值的类型
JSON_TYPE(json_val)
select f2, JSON_TYPE(f2) from t1;
+------------------------------+---------------+
| f2 | JSON_TYPE(f2) |
+------------------------------+---------------+
| NULL | NULL |
| "xyz" | STRING |
| 123 | INTEGER |
| 3.14 | DECIMAL |
| "2018-09-20 15:22:40.000000" | DATETIME |
| {"a": 1} | OBJECT |
| [1, "two"] | ARRAY |
+------------------------------+---------------+
JSON_VALID
检查一个JSON值是否有效
JSON_VALID(val)
select f1, JSON_VALID(f1) from t1;
+------+----------------+
| f1 | JSON_VALID(f1) |
+------+----------------+
| a | 0 |
| "a" | 1 |
| [] | 1 |
| {} | 1 |
| NULL | NULL |
+------+----------------+
JSON_TABLE
将JSON数据转换成一个关系型表
JSON_TABLE(expr, path, COLUMNS (column_list) [AS] alias)
SELECT *
FROM
JSON_TABLE(
'[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
"$[*]" COLUMNS(
xval VARCHAR(100) PATH "$.x",
yval VARCHAR(100) PATH "$.y"
)
) AS jt1;
+------+------+ | xval | yval | +------+------+ | 2 | 8 | | 3 | 7 | | 4 | 6 | +------+------+
JSON_PRETTY
将JSON文档输出成易于阅读的格式
JSON_PRETTY(json_val)
SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}');
+---------------------------------------------+
| JSON_PRETTY('{"a":"10","b":"15","x":"25"}') |
+---------------------------------------------+
| {
"a": "10",
"b": "15",
"x": "25"
} |
+---------------------------------------------+
JSON_STORAGE_FREE
获取JSON字段在当前存储块中的剩余可用空间
JSON_STORAGE_FREE(json_val)
JSON_STORAGE_SIZE
获取JSON字段所占用的存储空间（包括未真正使用的空间）
JSON_STORAGE_SIZE(json_val)
JSON路径（path）语法：《JSON Path Syntax》
更多JSON方法：JSON_ARRAYAGG、JSON_OBJECTAGG、ST_AsGeoJSON、ST_GeomFromGeoJSON
相关推荐
用于JSqlParser4.3版本解析mysql中JSON_OBJECT函数抛出ParseException异常问题，如下： // this is true SELECT JSON_OBJECT(key person value account,key personName value account_name) obj FROM tb_review_...
JSON 格式字段是 Mysql 5.7 新加的属性，不够它本质上以字符串性质保存在库中的，刚接触时我只了解 $.xx 查询字段的方法，因为大部分时间，有这个就够了，其他交给程序就行了，但是最近一些操作需要更复杂的查询操作...
Thinkphp6.0以上版本在查询MySql数据表中JSON类型数据下使用JSON_CONTAINS函数
自宝义函数json_searchvalue，从给定的JSON值中，根据给定的Key，检索出需要Value,
如下sql，JSON_OBJECT函数中存在CAST函数，随后报出ParseException问题！ SELECT JSON_ARRAYAGG(obj) FROM (SELECT trt.relevance_id,JSON_OBJECT('id',CAST(trt.id AS CHAR),'taskName',trt.task_name,'openStatus'...
mysql(5.6及以下)解析json #json解析函数 DELIMITER $$ DROP FUNCTION IF EXISTS `json_extract_c`$$ CREATE FUNCTION `json_extract_c`( details TEXT, required_field VARCHAR (255) ) RETURNS TEXT CHARSET ...
本文给大家分享的是在mysql5.7中操作json的函数的使用方法以及相关示例，非常的实用，有需要的小伙伴可以参考下
新版 Mysql 中加入了对 JSON Document 的支持，可以创建 JSON 类型的字段，并有一套函数支持对JSON的查询、修改等操作，下面就实际体验一下
MySQL5.7发布后，专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析。 我们先看看MySQL老版本的JSON存取。 示例表结构： CREATE TABLE json_test( id INT, person_desc TEXT )ENGINE INNODB; 我们来...
八、MYSQL存储过程内循环； 九、创建MYSQL事件（每天/每时/每分）； 十、MYSQL行转列； 十一、设置/取消唯一索引； 十二、MYSQL事件失效； 十三、解决MYSQL表被锁住的问题； 十四、MYSQL日期函数大全； 十五、终止...
JSON 支持：MySQL 8.0 引入了对 JSON 格式的原生支持，使得存储、查询和处理 JSON 数据变得更加方便。 更强大的地理空间数据支持：MySQL 8.0 在地理空间数据类型和函数方面有了很多改进，并引入了新的地图和地理空间...
mysql常用函数的使用，mysql-数据库基本操作
mysql_function_china_workday使用mysql是json函数，通过给每个工作日索引的方式，计算法定节假日时效workday.php循环出每年日历的JSON。wokday.sql自定义函数的sql注意未兼容下班后（例如：18：00）算下一个工作日...
mysqlJson官方文档翻译版，包含json数据类型，json函数，创建json，搜索json，修改json，返回json，实用程序功能
1、MySQL大事记 1994年：Michael Widenius...2013年：MySQL 5.7发布，加入了JSON数据类型、有效地调整排序缓存和更好的性能优化等。 2018年：MySQL 8.0发布，提供窗口函数、变量范围和原生支持多种数据类型等新特性。
JSON数据类型，以便更好的支持文档存储和NoSQL应用场景。 多源复制，以便更好的支持CDN等分布式应用。 GIS空间函数，以便更好的支持地理信息系统。 半同步复制的性能和稳定性改进。 性能优化和稳定性改进等。 支持更...