（十六）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/

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 160,504评论 4赞 365
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,898评论 1赞 300
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 110,218评论 0赞 248
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 44,322评论 0赞 214
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,693评论 3赞 290
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,812评论 1赞 223
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 32,010评论 2赞 315
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,747评论 0赞 204
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,476评论 1赞 246
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,700评论 2赞 251
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,190评论 1赞 262
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,541评论 3赞 258
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,206评论 3赞 240
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,129评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,903评论 0赞 199
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,894评论 2赞 283
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,748评论 2赞 274