草料场

浏览: 172255 次
性别:
来自: 杭州

最近访客更多访客>>

ufostop

努力吧飞翔

longfei136

ufopw

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

【MySQL】处理 JSON 的内建方法（函数）

博客分类：

MySQL

概览

注：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"
}   |
+---------------------------------------------+