StarRocks数据库在SQL语句中解析JSON字符串

StarRocks数据库在SQL语句中解析JSON字符串

-- 使用数据库
use sr_test;
-- 删除表
drop table ts_usr;
-- 创建表
CREATE TABLE ts_usr (
uid bigint NOT NULL COMMENT "用户id",
uname varchar(64) NULL COMMENT "用户名",
ujson varchar(1024) NULL COMMENT "用户json"
) ENGINE=OLAP
PRIMARY KEY(uid)
DISTRIBUTED BY HASH(uid)
PROPERTIES (
"compression" = "LZ4",
"enable_persistent_index" = "true",
"fast_schema_evolution" = "true",
"replicated_storage" = "true",
"replication_num" = "1"
);

ENGINE=OLAP：指定了表的存储引擎为OLAP，这是为在线分析处理（Online Analytical Processing）优化的存储引擎，适合处理大量数据的复杂查询。

PRIMARY KEY(uid)：指定uid列为主键，这意味着uid列的值必须是唯一的，并且可以用于索引和快速检索。

DISTRIBUTED BY HASH(uid)：指定了表的数据分布方式为通过uid列的哈希值来分布数据到不同的节点上，这是一种常见的分布式数据库的分片策略。

PROPERTIES：后面跟着的是表的属性设置，用于配置表的行为。

“compression” = “LZ4”：设置表的压缩算法为LZ4，这是一种高效的压缩算法，可以减少存储空间的使用。
“enable_persistent_index” = “true”：启用持久索引，这意味着索引数据将被存储在磁盘上，而不是每次查询时都重新构建，可以提高查询效率。
“fast_schema_evolution” = “true”：启用快速模式演化，这允许在不重建表的情况下快速添加或删除列。
“replicated_storage” = “true”：启用复制存储，这意味着数据将被复制到多个节点上，以提高数据的可靠性。
“replication_num” = “1”：设置复制因子为1，这意味着每个数据分片将被复制到1个其他节点上。这个设置可能与replicated_storage属性相矛盾，因为通常复制存储意味着有多个副本，这里可能需要根据实际需求调整。

-- 插入语句
INSERT INTO sr_test.ts_usr (uid, uname, ujson) VALUES
(1, 'Alice', '{"height": 170, "width": 55}'),
(2, 'Bob', '{"height": 180, "width": 70}'),
(3, 'Carol', '{"height": 165, "width": 60}'),
(4, 'David', '{"height": 175, "width": 65}'),
(5, 'Eve', '{"height": 160, "width": 50}'),
(6, 'Frank', '{"height": 185, "width": 75}'),
(7, 'Grace', '{"height": 170, "width": 60}'),
(8, 'Hank', '{"height": 190, "width": 80}'),
(9, 'Ivy', '{"height": 168, "width": 58}'),
(10, 'Jack', '{"height": 172, "width": 62}');

-- 查询
select * from sr_test.ts_usr;

-- 查询，解析json字段，拆解height、width
SELECT
uid,
uname,
ujson,
GET_JSON_OBJECT(ujson, '$.height') AS height,
GET_JSON_OBJECT(ujson, '$.width') AS width
FROM
sr_test.ts_usr;

GET_JSON_OBJECT(ujson, ‘$.heigh{t}^{\prime })ASheight,：这里使用了GE{T}_{J}SO{N}_{O}BJECT函数来从ujson字段中提取名为height的JSON属性的值{。}^{\prime }$.height’是JSON路径表达式，代表JSON对象的根，.用来访问对象的属性。所以’$.heigh{t}^{\prime }表示从JSON对象的根开始，获取名为height的属性的值。ASheight是将这个值重命名为列名height。关于GE{T}_{J}SO{N}_{O}BJECT{和}^{\prime }$.'的写法
GET_JSON_OBJECT是一个函数，用于从JSON格式的字符串中提取指定路径的值。这个函数接受两个参数：第一个参数是包含JSON的字段，第二个参数是JSON路径表达式，用来指定要提取的JSON属性的位置。

'$.'是JSON路径表达式的写法。在JSON路径中：

代表JSON对象的根。
.用来访问对象的属性或数组的元素。
如果属性名是height或width，那么’$.heigh{t}^{\prime }{和}^{\prime }$.width’就是指定这些属性的路径。
例如，如果ujson字段包含如下JSON字符串：

json{“height”: 180, “width”: 75}
那么GET_JSON_OBJECT(ujson, ‘$.heigh{t}^{\prime })将返回180，GE{T}_{J}SO{N}_{O}BJECT(ujson{,}^{\prime }$.width’)将返回75。

这种查询方式允许你直接在SQL查询中处理和使用存储在JSON字段中的数据，而不需要在应用层进行解析，从而提高了数据处理的效率和灵活性。