暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 虚谷数据库探究：JSON数据类型

虚谷数据库探究：JSON数据类型

原创 石河 2024-02-27
1179

JSON数据类型概述：

1.保证了 JSON 数据类型的强校验，JSON 数据列会自动校验存入此列的内容是否符合 JSON 格式，非正常格式则报错。

2.虚谷数据库JSON数据采用可变长的大对象存储，支持最大2GB文本。

3.JOSN数据支持存储值的基础类型为string、bool、number、null。

JSON类型虚谷库存取探究

存入说明：

json string 是由双引号包裹的字符串：

'"虚谷数据库"'

json bool 是小写true false：

'true' 'false'

json number：

'1' '-1' '10.2'

json null：

'null'

json数组是包含在 [ ] 字符之间以逗号分割的值列表：

'["xugu", true, false, 1, 1.1, null]'

json对象是包含在 { }字符之间的多组键值对，键值必须为string：

'{"key1": "虚谷", "key2": "database", "key3": true, "key4": 1, "key5": 1.1, "key6": null}'

写入案例（JSON数据类型，虚谷数据库2024年初的新版本才支持，如需测试请联系官方获取最新版本）：

create table xugu_json(id int primary key, json_t json);

insert into xugu_json values (1,'"虚谷数据库"');

insert into xugu_json values (2,'true');

insert into xugu_json values (3,'null');

insert into xugu_json values (4,'2024.1');

insert into xugu_json values (5,'["xugu", true, false, 1, 1.1, null]');

insert into xugu_json values (6,'{"key1": "虚谷", "key2": "database", "key3": true, "key4": 1, "key5": 1.1, "key6": null}');

insert into xugu_json values (7,'{
    "xugu": {
        "DBMS": [
            {
                "version": "虚谷数据库V12",
                "Architecture": "Distributed",
                "title": "国产自研，自主可控",
                "desc": "首批安可",
                "level":1.0
            },
            {
                "version": "虚谷数据库V12",
                "Architecture": "Centralized",
                "title": "国产自研，自主可控",
                "desc": "集中式、分布式一体化数据库",
                "level":1.0
            },
            {
                "version": "虚谷数据库V12",
                "Architecture": "Saved",
                "title": "国产自研，自主可控",
                "desc": "安装便捷、代码自有",
                "level":1.0
            },
            {
                "version": "虚谷数据库V12",
                "Architecture": "Enterprise",
                "title": "国产自研，自主可控",
                "desc": "20年深耕，PB级投产",
                "level":1.0
            }
        ],
        "other": {
            "remake": "欢迎致电咨询",
            "tel": "400-8886236"
        }
    },
    "expensive": 30
}');

SELECT * FROM XUGU_JSON ;

image.png

读取说明：

JSON路径表达式（JSONPath）

虚谷支持一个路径表达式来检索JSON数据中特定的元素。

由$字符打头，代表JSON文档本身。

'{"test": 1}'->'$' = {"test": 1}

image.png

(.)点字符用于寻找对象中的键值对。

'{"test": 1}'->'$.test' = 1 
-- 或 
'{"test": 1}'->'$."test"' = 1

image.png
[N]用于寻找数组中下标为N的元素。

'[1,3,5,7]'->'$[1]' = 3

image.png

[M to N]用于寻找数组中下标M到N的元素集合。

'[1,3,5,7]'->'$[1 to 3]' = [3,5,7]

image.png

last关键字做为数组最后一个元素或非数组元素的同义词。

'[1,3,5,7]'->'$[last]' = 7
-- 或 last - N 作为相对寻址
'[1,3,5,7]'->'$[last - 1 to last]' = [5,7]
-- 非数组元素 与直接使用 $ 相同
'"123"'->'$[last]' = "123"
'{"a": 1}'->'$[last]' = {"a": 1}

image.png

*通配符，代表全量元素。

'[1,2,3,4]'->'$[*]' = [1,2,3,4]
-- 或
'{"a": 1,"b": 2}'->'$.*' = {"a": 1,"b": 2}

image.png

**深度查找。

'[1,2,[3,3,3],4]'->'$**[1]' = [2,3]
-- 或
'{"a": 1,"b": {"a": 2}}'->'$**.a' = [1,2]

image.png

虚谷数据库，JSON函数使用详解

一、JSON_ARRAY 构建JSON数组

语法：

JSON_ARRAY(value1[, value2[, ...]])

作用：JSON_ARRAY() 函数可将参数构建成JSON数组

参数说明：

value1、value2、… …：支持存储值的基础类型为string、bool、number、null。

返回值：返回设置的所有参数的数组。

说明：TURE转换为true、FALSE转换为false、NULL转换为null

案例：

SELECT JSON_ARRAY(2024, '虚谷', NULL, TRUE, FALSE, sysdate);

image.png

二、JSON_ARRAY_APPEND 添加元素至JSON文档的数组中

语法：

JSON_ARRAY_APPEND(json, path, value[, path2, value2] ...)

作用：JSON_ARRAY_APPEND() 函数可以添加元素至指定的JSON文档的数组中。

参数说明：

json：指定的JSON文档。

path：添加元素的路径表达式，路径表达式不能包含* 和 **标识符或数组范围。

value：需添加的元素值。

返回值：添加元素后的JSON文档。

案例：

SELECT JSON_ARRAY_APPEND('{"title": "虚谷", "desc": ["四川"]}', '$.desc', '成都');

image.png

三、JSON_ARRAY_INSERT 插入元素至JSON文档的数组中

语法：

JSON_ARRAY_INSERT(json, path, value[, path2, value2] ...)

作用：JSON_ARRAY_INSERT() 函数可插入一个元素至JSON文档的数组中。

参数说明：

json：指定的JSON文档。

path：添加元素的路径表达式，路径表达式不能包含* 和 **标识符或数组范围。

value：需添加的元素值。

返回值：添加元素后的JSON文档。

案例：

SELECT JSON_ARRAY_INSERT('{"desc": "首批安可","title": ["国产自研","自主可控"], "version": "虚谷数据库V12"}'
,'$.title[2]'
,'解压即用');

image.png

四、JSON_CONTAINS 判断JSON文档是否在路径处包含特定对象

语法：

JSON_CONTAINS(json1, json2)
JSON_CONTAINS(json1, json2, path)

作用：JSON_CONTAINS() 函数用于判断JSON文档是否在路径处包含特定对象。

参数说明：

json1：用于判断的JSON文档。

json2：被包含的JSON文档。

path：路径表达式。

返回值：包含，返回1；不包含，返回0。路径不存在或值为NULL时，返回NULL。

案例：

SELECT JSON_CONTAINS('{"name":"虚谷","other":[1,2]}','"虚谷"','$.name')
,JSON_CONTAINS('["a","b"]','"c"');

image.png

五、JSON_CONTAINS_PATH 判断JSON文档是否在路径处包含数据

语法：

JSON_CONTAINS_PATH(json, 'one'/'all', path[, path])

作用：JSON_CONTAINS_PATH () 函数用于判断JSON文档是否在路径处包含数据。

参数说明：

json：JSON文档。

one/all：one代表选取任意路径、all代表选取所有路径。

path[, path]：一个或多个路径表达式。

返回值：

选定’one’时，任意一个路径有值，则返回1，否则返回0；

选定’all’时，所有路径都有值，则返回1，否则返回0；

案例：

SELECT
JSON_CONTAINS_PATH('{"name":"虚谷","other":[1,2]}', 'one', '$.name', '$.other[3]'), 
JSON_CONTAINS_PATH('{"name":"虚谷","other":[1,2]}', 'all', '$.name', '$.other[3]');

image.png

六、JSON_DEPTH 获取JSON文档的最大深度

语法：

JSON_DEPTH(json)

作用：JSON_DEPTH() 函数返回JSON文档的最大深度。

参数说明：json：JSON文档。

返回值：

  • 空的数组、空的对象或者纯数值的深度是 1。
  • 仅包含深度为 1 的元素的数组的深度是 2。
  • 所有成员的值的深度为 1 的对象的深度是 2。
  • 其他 JSON 文档的深度都大于 2。

案例：

SELECT JSON_DEPTH(JSON_T),JSON_T  FROM XUGU_JSON xj ;

image.png

七、JSON_EXTRACT 从JSON文档返回数据

语法：

JSON_EXTRACT(json, path1, path2,...)

作用：JSON_EXTRACT() 函数可根据指定的路径表达式提取JSON文档中的值。

参数说明：

json：JSON文档。

path1, path2,…：一个或多个路径表达式。

返回值：

返回根据路径表达式匹配到的值，匹配不到则返回null

案例：

SELECT JSON_EXTRACT('{"name":"虚谷","other":[1,2]}', '$.name', '$.other[1]') ;

image.png

八、JSON_INSERT 将数据插入JSON文档

语法：

JSON_INSERT(json, path, value[, path2, value2] ...)

作用：JSON_INSERT () 函数可以将指定数据插入到JSON文档

参数说明：

json：JSON文档。

path：添加元素的路径表达式，路径表达式不能包含* 和 **标识符或数组范围。

value：插入的数据，搭配对应的路径表达式，可插入多个。

返回值：返回插入数据后的JSON文档，但是若路径位置已存在数值，则不插入返回原值。

案例：

SELECT JSON_INSERT ('{"name":"虚谷","other":[1,2]}', '$.DB','数据库','$.other[2]',3) ;

image.png

九、JSON_KEYS 获取JSON文档的键组成的数组

语法：

JSON_KEYS(json)
JSON_KEYS(json, path)

作用：JSON_KEYS() 函数用于获取JSON文档的键组成的数组。

参数说明：

json：JSON文档。

path：路径表达式。

返回值：返回JSON对象的KEY值数组，或者路径表达式指定的对象的KEY值数组。

案例：

SELECT JSON_KEYS ('{"name":"虚谷","other":[1,2]}'),
JSON_KEYS('[1, {"a": 1, "b": 2, "c": 3}]', '$[1]');

image.png

十、JSON_LENGTH获取接送文档中的元素个数

语法：

JSON_LENGTH(json)
JSON_LENGTH(json, path)

作用：JSON_LENGTH() 函数用于获取JSON文档中的元素个数。

参数说明：

json：JSON文档。

path：路径表达式。

返回值：返回JSON文档的元素个数或者指定路径的元素个数。

  • 数值的元素个数是 1。
  • 数组的元素个数是数组元素的数量。
  • 对象的元素个数是对象成员的数量。
  • 内嵌的数组或对象不参与计算元素个数。

案例：

SELECT JSON_LENGTH ('{"name":"虚谷","other":[1,2,3,4]}'),
JSON_LENGTH('[1, {"a": 1, "b": 2, "c": 3}]', '$[1]');

image.png

十一、JSON_MERGE 合并JSON文档，重复键值合成数组

语法：

JSON_MERGE(json1, json2, ...)

作用：JSON_MERGE() 函数用于合并JSON文档，重复键值合成数组。

参数说明：

json1：JSON文档1。

json2：JSON文档2。

返回值：返回合并后的JSON文档。

案例：

SELECT JSON_MERGE ('{"name":"虚谷","other":[1,2,3,4]}','{"name": "数据库", "other": 2, "c": 3}');

image.png

十二、JSON_MERGE_PATCH 合并JSON文档，替换重复键

语法：

JSON_MERGE_PATCH(json1, json2, ...)

作用：JSON_MERGE_PATCH() 函数用于合并JSON文档，替换重复键。

参数说明：

json1：JSON文档1。

json2：JSON文档2。

返回值：合并JSON文档，JSON文档2存在与原JSON文档重复键时，以JSON文档2的值为准。

案例：

SELECT JSON_MERGE_PATCH('{"name":"虚谷","other":[1,2,3,4]}','{"name": "数据库", "other": 2, "c": 3}');

image.png

十三、JSON_MERGE_PRESERVE 合并JSON文档，重复键值合成数组

语法：

JSON_MERGE(json1, json2, ...)

作用：JSON_MERGE_PRESERVE() 合并JSON文档，重复键值合成数组

参数说明：

json1：JSON文档1。

json2：JSON文档2。

返回值：返回合并后的JSON文档。

案例：

SELECT JSON_MERGE_PRESERVE('{"name":"虚谷","other":[1,2,3,4]}','{"name": "数据库", "other": 2, "c": 3}');

image.png

十四、JSON_OBJECT 构建JSON对象

语法：

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

作用：JSON_OBJECT() 函数用于将指定参数组合成JSON对象。

参数说明：

key：JSON对象的键。

value：JSON对象的值。

返回值：返回组合后的JSON对象

案例：

SELECT JSON_OBJECT('name','虚谷','DB','DataBase','desc',TRUE,'null',NULL,'value',1);

image.png

十五、JSON_OVERLAPS 比较两个JSON文档元素是否重合

语法：

JSON_OVERLAPS(json1, json2)

作用：JSON_OVERLAPS() 函数用于比较两个JSON文档。

参数说明：

json1：JSON文档1。

json2：JSON文档2。

返回值：如果有任何共同的键值对或数组元素，则返回1，否则返回0。

  • 比较两个数组时，如果两个数组至少有一个相同的元素返回 1，否则返回 0。

  • 比较两个对象时，如果两个对象至少有一个相同的键值对返回 1，否则返回 0。

  • 比较两个纯值时，如果两个值相同返回 1，否则返回 0。

  • 比较数值和数组时，如果值是这个数组中的直接元素返回 1，否则返回 0。

  • 比较数值和对象的结果为 0。

  • 比较数组和对象的结果为 0。

案例：

SELECT 
JSON_OVERLAPS('{"name": "虚谷"}', '{"name": "虚谷", "DB":"DataBase"}'),
JSON_OVERLAPS('[1, 2, 3]', '3'),
JSON_OVERLAPS('[1, 2, [3]]', '3');

image.png

十六、JSON_PRETTY 格式化文档

语法：

JSON_PRETTY(json)

作用：JSON_PRETTY() 函数用于格式化输出JSON文档。

参数说明：json：JSON文档。

返回值：返回格式化后的JSON文档。

案例：

SELECT JSON_PRETTY(json_t) FROM XUGU_JSON;

image.png

十七、JSON_QUOTE 将字符串转换为有效的JSON字符串

语法：

JSON_QUOTE(str)

作用：JSON_QUOTE() 函数可将字符串转换为有效的JSON字符串。

参数说明：

str：字符串参数

返回值：返回JSON格式的字符串

案例：

SELECT JSON_QUOTE('虚谷'),JSON_QUOTE('"数据库"'),JSON_QUOTE('\');

image.png

十八、JSON_REMOVE 从JSON文档中删除数据

语法：

JSON_REMOVE(json, path[, path] ...)

作用：JSON_REMOVE() 函数用于从JSON文档中删除指定的元素。

参数说明：

json：JSON文档。

path：路径表达式。

返回值：返回删除指定元素之后的JSON文档。

案例：

SELECT JSON_REMOVE('{"desc": "首批安可","title": ["国产自研","自主可控"], "version": "虚谷数据库V12"}','$.title[1]');

image.png

十九、JSON_REPLACE 替换数据

语法：

JSON_REPLACE(json, path, value[, path2, value2] ...)

作用：JSON_REPLACE() 函数根据指定的文档路径替换JSON文档中的元素

参数说明：

json：JSON文档。

path：路径表达式。

value：需替换的元素值。

返回值：

案例：

SELECT JSON_REPLACE('{"desc": "首批安可","title": ["国产自研","自主可控"], "version": "虚谷数据库V12"}','$.title[1]','分布式架构');

image.png

二十、JSON_SCHEMA_VALID 根据JSON模式验证JSON文档

语法：

JSON_SCHEMA_VALID(schema, json_doc)

作用：JSON_SCHEMA_VALID() 函数用于检验一个给定的JSON串是否满足约定的数据规范。

参数说明：

schema：JSON Schema，以一个JSON串来描述的JSON数据规范。

json_doc：需验证的JSON文档。

返回值：通过验证返回1，否则返回0。

案例：

--传入JSON Schema
INSERT INTO XUGU_JSON  values(8,'{
  "type": "object",
  "properties": {
    "数值": {
      "type": "number"
    },
    "字符串": {
      "type": "string"
    },
    "枚举": {
      "type": "string",
      "enum": ["虚谷", "分布式数据库", "国产自研"]
    }
  },
  "required": ["数值", "字符串"]
}');
--验证是否符合JSON Schema的规则
SELECT JSON_SCHEMA_VALID(JSON_T ,'{"数值":2}'),
JSON_SCHEMA_VALID(JSON_T ,'{"数值":1, "字符串":"虚谷"}')
FROM XUGU_JSON WHERE id=8;

image.png

二十一、JSON_SCHEMA_VALIDATION_REPORT 根据JSON模式验证JSON文档返回验证报告

语法：

JSON_SCHEMA_VALIDATION_REPORT(schema, json)

作用：JSON_SCHEMA_VALIDATION_REPORT() 函数用于检验一个给定的JSON串是否满足约定的数据规范，并返回验证报告。

参数说明：

schema：JSON Schema，以一个JSON串来描述的JSON数据规范。

json_doc：需验证的JSON文档。

返回值：返回验证报告。

案例：

SELECT JSON_SCHEMA_VALIDATION_REPORT(JSON_T ,'{"数值":2}'),
JSON_SCHEMA_VALIDATION_REPORT(JSON_T ,'{"数值":1, "字符串":"虚谷"}')
FROM XUGU_JSON WHERE id=8;

image.png

二十二、JSON_SEARCH 返回给定值在JSON文档中的路径

语法：

JSON_SEARCH(json, 'one'/'all', str)
JSON_SEARCH(json, 'one'/'all', str, escape_char)
JSON_SEARCH(json, 'one'/'all', str, escape_char, path)

作用：JSON_SEARCH() 函数用于返回给定值在JSON文档中的路径

参数说明：

json：JSON文档。

one/all：one代表选取第一个路径、all代表选取所有路径。

str：需搜索的元素，可使用正则。

escape_char：使用正则是需要转义时，填写转义字符，默认为 \ 。

path：路径表达式。

返回值：返回搜索的路径表达式。

案例：

SELECT 
JSON_SEARCH(JSON_T ,'all','分布式数据库'),
JSON_SEARCH(JSON_T ,'all','strin%','\','$.properties."枚举".type','$.properties."字符串".type'),
JSON_SEARCH(JSON_T ,'one','strin%','\','$.properties."枚举".type','$.properties."字符串".type')
FROM XUGU_JSON WHERE id=8;

image.png

二十三、JSON_SET 替换或插入新值。

语法：

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

作用：JSON_SET() 函数可将JSON文档中的元素替换或插入新值

参数说明：

json_doc：json文本，JSON类型或JSON String类型。

path：路径表达式，字符类型。

val：新值，类型取值为（NULL、布尔型、数值型、自定义类型（object、varray、table）、JSON类型以及其他能转换成字符型的类型）。

返回值：返回替换或插入后的JSON文档。

案例：

SELECT JSON_SET('[1, {"name": "虚谷"}]', '$[0]', 10, '$[1].name', '分布式数据库', '$[3]', 12);

image.png

二十四、JSON_TYPE 判断JSON文本类型。

语法：

JSON_TYPE( json_doc )

作用：JSON_TYPE() 函数在

参数说明：

json_doc：json文本，JSON类型或JSON String类型。

返回值：返JSON文本类型（OBJECT/ARRAY/INTEGER/DOUBLE/BOOLEAN/STRING/NULL）。

案例：

SELECT JSON_TYPE('[1, {"name": "虚谷"}]'),
JSON_TYPE('[1, 2]'),
JSON_TYPE('true');

image.png

二十五、JSON_UNQUOTE取消JSON值引用并返回

语法：

JSON_UNQUOTE( json_doc )

作用：JSON_UNQUOTE() 函数用于取消JSON值引用并返回

参数说明：

json_doc：json文本，JSON类型或JSON String类型。

返回值：返回处理后的JSON文档。

案例：

SELECT JSON_UNQUOTE('"虚谷"'),
JSON_UNQUOTE('1'),JSON_UNQUOTE('"\u0031\u0032\u0033"'),
JSON_UNQUOTE('true');

image.png

二十六、JSON_VALID 判断参数是否为合格的JSON文本

语法：

JSON_VALID( val )

作用：JSON_VALID() 函数在

参数说明：

val：用于检验格式的参数。

返回值：验证通过返回1，否则返回0。

案例：

SELECT JSON_VALID('虚谷'),
JSON_VALID('1'),JSON_VALID(1),
JSON_VALID('true');

image.png

二十七、JSON_ARRAYAGG将结果集聚合成JSON数组。

语法：

JSON_ARRAYAGG( col_or_expr )

作用：JSON_ARRAYAGG() 函数用于将结果集聚合成JSON数组。

参数说明：

col_or_expr：列或表达式。（与其他聚合函数一致，出现NULL值（非JSON NULL）会有“[E17091] 统计函数忽略了部分空值”警告。）

返回值：返回JSON文档。

案例：

create table xugu_t1(id int, val varchar);
insert into xugu_t1 values(1,'xugu')(1,'分布式数据库')(2,'true')(2,null)(2,'null');
SELECT json_arrayagg(val)  from xugu_t1 group BY id;

image.png

二十八、JSON_OBJECTAGG 将结果集聚合成JSON对象

语法：

JSON_OBJECTAGG( key, value )

作用：JSON_OBJECTAGG() 函数用于将结果集聚合成JSON对象。

参数说明：

key：可转换为字符的类型。
value：取值为NULL、布尔型、数值型、自定义类型（object、varray、table）、JSON类型以及其他能转换成字符型的类型。

返回值：返回JSON文档。

案例：

SELECT JSON_OBJECTAGG(id,val)  from xugu_t1 group BY id;

image.png

以上就是虚谷数据库中测试JSON数据类型的使用结果，JSON数据类型的存取、函数的调用和MYSQL一致，对习惯使用mysql等数据库的用户来说，无需特殊学习用法。

虚谷数据库xugudb信创数据库信创
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

虚谷数据库（共6篇）
虚谷数据库探究

评论