（十六）MariaDB部分内置JSON函数简介

如今，越来越多的 IOT 设备被推广使用，收集到的数据，习惯性的，都会是 nosql 类型的，例如 JSON。

MariaDB 10.2.7 加入了 JSON 的数据类型，用于处理该格式的数据，但在 MariaDB 10.2.3 已加入多个 JSON 用途 functions，支持所有文字型别字段 ( char, varchar, text …)。

此处简单介绍下常用的 MariaDB 内建的 JSON 相关的函数。

示例直接复制 MariaDB 命令窗口测试执行语句及结果。

JSON_VALID

语法：

JSON_VALID(value)

说明：

显示给定值是否为有效的 JSON 文档(JSON document)。如果有效，则返回 1；如果无效，则返回 0；如果参数为空，则返回 NULL。

在 MariaDB 10.4.3 中，JSON_VALID 函数自动用作 JSON 数据类型别名的检查约束，以确保插入有效的 JSON 文档。即字段是 JSON 类型，则会自动检查值是否为 JSON 格式。

示例：

MariaDB [(none)]> set @json1='{"id": 1, "name": "David"}';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SELECT JSON_VALID(@json1);
+--------------------+
| JSON_VALID(@json1) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.000 sec)

或简单写成一句：

MariaDB [(none)]> SELECT JSON_VALID('{"id": 1, "name": " David "}');
+--------------------------------------------+
| JSON_VALID('{"id": 1, "name": " David "}') |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.000 sec)

json 数据类型自动检查：

-- 建表
CREATE TABLE test200222.jsont (
    uid INTEGER UNSIGNED auto_increment NOT NULL,
    uattr json NULL,
    PRIMARY KEY (uid)
)

-- 插入测试
INSERT INTO jsont VALUES(1,NULL); -- Query OK, 1 row affected (0.01 sec)
INSERT INTO jsont VALUES(2,'{"size": 42, "colour": "white"}'); -- Query OK, 1 row affected (0.01 sec)
INSERT INTO jsont VALUES(3,'{"colour": "white}'); -- SQL 错误 [4025] [23000]: (conn=82) CONSTRAINT `jsont.uattr` failed for `test200222`.`jsont`

JSON_CONTAINS_PATH

语法：

JSON_CONTAINS_PATH(json_doc, return_arg, path[, path] ...)

说明：

显示给定的 JSON 文档是否包含指定路径处的数据。如果是，则返回 1；如果不是，则返回 0；如果任何参数为空，则返回 NULL。

返回参数可以是一个或全部：

• one-如果 JSON 文档中至少存在一个路径，则返回 1。
• all-仅当 JSON 文档中存在所有路径时返回 1。

Path 路径表示

• $. -> 起始
• $.Key -> $.id —> { “id”: 5 }
• $.key.subkey -> $.data.subject -> { “data”: { “subject”:”h”} }

示例：

MariaDB [(none)]> SET @json = '{"A": 1, "B": [2], "C": [3, 4]}';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A', '$.D');
+------------------------------------------------+
| JSON_CONTAINS_PATH(@json, 'one', '$.A', '$.D') |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT JSON_CONTAINS_PATH(@json, 'all', '$.A', '$.D');
+------------------------------------------------+
| JSON_CONTAINS_PATH(@json, 'all', '$.A', '$.D') |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0.000 sec)

JSON_EXISTS

语法：

JSON_EXISTS(,)

说明

确定给定数据中是否存在指定的 JSON 值。如果找到，则返回 1；如果没有，则返回 0；如果任何输入为空，则返回 NULL。

示例：

MariaDB [(none)]> SELECT JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2");
+------------------------------------------------------------+
| JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") |
+------------------------------------------------------------+
|                                                          1 |
+------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]");
+---------------------------------------------------------------+
| JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]") |
+---------------------------------------------------------------+
|                                                             1 |
+---------------------------------------------------------------+
1 row in set (0.000 sec)

JSON_CONTAINS

语法：

JSON_CONTAINS(json_doc, val[, path])

说明：

返回指定的值是否在给定的 JSON 文档中找到，或者是否在文档中的指定路径（可选）处找到。

• 如果是，则返回 1；如果不是，则返回 0；如果任何参数为空，则返回 NULL。
• 如果文档或路径无效，或包含*或**通配符，则会发生错误。

示例：

MariaDB [(none)]> SET @json = '{"A": 0, "B": {"C": 1}, "D": 2}';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SELECT JSON_CONTAINS(@json, '2', '$.A');
+----------------------------------+
| JSON_CONTAINS(@json, '2', '$.A') |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT JSON_CONTAINS(@json, '2', '$.D');
+----------------------------------+
| JSON_CONTAINS(@json, '2', '$.D') |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT JSON_CONTAINS(@json, '{"C": 1}', '$.A');
+-----------------------------------------+
| JSON_CONTAINS(@json, '{"C": 1}', '$.A') |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT JSON_CONTAINS(@json, '{"C": 1}', '$.B');
+-----------------------------------------+
| JSON_CONTAINS(@json, '{"C": 1}', '$.B') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.000 sec)

JSON_DEPTH

语法：

JSON_DEPTH(json_doc)

说明：

返回给定 JSON 文档的最大深度，如果参数为 NULL，则返回 NULL。如果参数是无效的 JSON 文档，则会发生错误。

标量值（scalar values）或空数组或对象的深度为 1。

非空但仅包含深度为 1 的元素或成员值的数组或对象的深度为 2。

在其它情况下，深度将大于 2。

示例：

MariaDB [(none)]> SELECT JSON_DEPTH('[]'), JSON_DEPTH('true'), JSON_DEPTH('{}');
+------------------+--------------------+------------------+
| JSON_DEPTH('[]') | JSON_DEPTH('true') | JSON_DEPTH('{}') |
+------------------+--------------------+------------------+
|                1 |                  1 |                1 |
+------------------+--------------------+------------------+
1 row in set (0.043 sec)

MariaDB [(none)]> SELECT JSON_DEPTH('[1, 2, 3]'), JSON_DEPTH('[[], {}, []]');
+-------------------------+----------------------------+
| JSON_DEPTH('[1, 2, 3]') | JSON_DEPTH('[[], {}, []]') |
+-------------------------+----------------------------+
|                       2 |                          2 |
+-------------------------+----------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT JSON_DEPTH('[1, 2, [3, 4, 5, 6], 7]');
+---------------------------------------+
| JSON_DEPTH('[1, 2, [3, 4, 5, 6], 7]') |
+---------------------------------------+
|                                     3 |
+---------------------------------------+
1 row in set (0.000 sec)

JSON_OBJECT

语法：

JSON_OBJECT([key, value[, key, value] ...])

说明：

返回包含给定键/值对的 JSON 对象。键/值列表可以为空。

如果参数的数目为奇数，或者任何键名为空，则将发生错误。

示例：

MariaDB [(none)]> SELECT JSON_OBJECT("id", 1, "name", "David");
+---------------------------------------+
| JSON_OBJECT("id", 1, "name", "David") |
+---------------------------------------+
| {"id": 1, "name": "David"}            |
+---------------------------------------+
1 row in set (0.000 sec)

JSON_KEYS

语法：

JSON_KEYS(json_doc[, path])

说明：

从 JSON 对象的顶层值（top-level value）以 JSON 数组的形式返回键，如果提供了可选的 path 参数，则从 path 返回顶层键（top-level keys）。

从顶层值中的嵌套子对象中排除关键帧。如果所选对象为空，则生成的数组将为空。

如果任何参数为空、给定路径未找到对象或 json_doc 参数不是对象，则返回 NULL。

如果 JSON 文档无效、路径无效或路径包含*或**通配符，则会发生错误。

示例：

MariaDB [(none)]> SELECT JSON_KEYS('{"A": 1, "B": {"C": 2}}');
+--------------------------------------+
| JSON_KEYS('{"A": 1, "B": {"C": 2}}') |
+--------------------------------------+
| ["A", "B"]                           |
+--------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT JSON_KEYS('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C');
+-----------------------------------------------------+
| JSON_KEYS('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C') |
+-----------------------------------------------------+
| ["D"]                                               |
+-----------------------------------------------------+
1 row in set (0.000 sec)

JSON_VALUE

语法：

JSON_VALUE(json_doc, path)

说明：

给定一个 JSON 文档，返回路径指定的标量（scalar）。如果没有给出有效的 JSON 文档，或者没有匹配项，则返回 NULL。

示例：

MariaDB [(none)]> select json_value('{"key1":123}', '$.key1');
+--------------------------------------+
| json_value('{"key1":123}', '$.key1') |
+--------------------------------------+
| 123                                  |
+--------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> select json_value('{"key1": [1,2,3], "key1":123}', '$.key1');
+-------------------------------------------------------+
| json_value('{"key1": [1,2,3], "key1":123}', '$.key1') |
+-------------------------------------------------------+
| 123                                                   |
+-------------------------------------------------------+
1 row in set (0.000 sec)

JSON_INSERT

语法：

JSON_INSERT(json_doc, path, val[, path, val] ...)

说明：

将数据插入到 JSON 文档中，如果任何参数为 NULL，则返回结果文档或 NULL。

如果 JSON 文档不是无效的，或者如果任何路径无效或包含*或**通配符，则会发生错误。

JSON_INSERT 只能插入数据，而 JSON_REPLACE 只能更新。JSON_SET 可以更新或插入数据。

示例：

MariaDB [(none)]> SET @json = '{ "A": 0, "B": [1, 2]}';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT JSON_INSERT(@json, '$.C', '[3, 4]');
+--------------------------------------+
| JSON_INSERT(@json, '$.C', '[3, 4]')  |
+--------------------------------------+
| {"A": 0, "B": [1, 2], "C": "[3, 4]"} |
+--------------------------------------+
1 row in set (0.000 sec)

JSON_REPLACE

语法：

JSON_REPLACE(json_doc, path, val[, path, val] ...)

说明：

替换 JSON 文档中的现有值，返回结果；如果任何参数为空，则为空。

如果 JSON 文档无效、路径无效或路径包含*或**通配符，则会发生错误。

路径和值是从左到右计算的，前面的计算结果将用作下一个的值

示例：

MariaDB [(none)]> SELECT JSON_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4);
+-----------------------------------------------------+
| JSON_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4) |
+-----------------------------------------------------+
| {"A": 1, "B": [2, 4]}                               |
+-----------------------------------------------------+
1 row in set (0.000 sec)

JSON_SET

语法：

JSON_SET(json_doc, path, val[, path, val] ...)

说明：

在 JSON 文档中更新或插入数据，返回结果；如果任何参数为 NULL 或可选路径找不到对象，则返回 NULL。

如果 JSON 文档无效、路径无效或路径包含*或通配符，则会发生错误。

示例：

MariaDB [(none)]> SET @json = '{"A": 0, "B": "hello", "C": {"msg": "check"} }';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT JSON_VALID(@json);
+-------------------+
| JSON_VALID(@json) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT JSON_SET(@json , '$.B' , '"WORLD"');
+---------------------------------------------------+
| JSON_SET(@json , '$.B' , '"WORLD"')               |
+---------------------------------------------------+
| {"A": 0, "B": "\"WORLD\"", "C": {"msg": "check"}} |
+---------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT JSON_SET(@json , '$.D' , '"INSERT"');
+------------------------------------------------------------------+
| JSON_SET(@json , '$.D' , '"INSERT"')                             |
+------------------------------------------------------------------+
| {"A": 0, "B": "hello", "C": {"msg": "check"}, "D": "\"INSERT\""} |
+------------------------------------------------------------------+
1 row in set (0.000 sec)

JSON_EXTRACT

语法：

JSON_EXTRACT(json_doc, path[, path] ...)

说明：

从 JSON 文档中提取数据。从与路径参数匹配的部分中选择提取的数据。返回所有匹配的值。要么作为单个匹配的值，要么在参数可以返回多个值的情况下，则结果将自动包装为匹配顺序的数组。

如果没有匹配的路径或任何参数为空，则返回 NULL。

如果任何 path 参数不是有效的 path，或者 json_doc 参数不是有效的 json 文档，则会发生错误。

示例：

MariaDB [(none)]> SET @json = '[1, 2, [3, 4]]';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT JSON_EXTRACT(@json, '$[1]');
+-----------------------------+
| JSON_EXTRACT(@json, '$[1]') |
+-----------------------------+
| 2                           |
+-----------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT JSON_EXTRACT(@json, '$[2]');
+-----------------------------+
| JSON_EXTRACT(@json, '$[2]') |
+-----------------------------+
| [3, 4]                      |
+-----------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT JSON_EXTRACT(@json, '$[2][1]');
+--------------------------------+
| JSON_EXTRACT(@json, '$[2][1]') |
+--------------------------------+
| 4                              |
+--------------------------------+
1 row in set (0.000 sec)

更多 MariaDB 内建 JSON 函数，可参看官网：https://mariadb.com/kb/en/json-functions/