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, ‘
.
h
e
i
g
h
t
′
)
A
S
h
e
i
g
h
t
,
：这里使用了
G
E
T
J
S
O
N
O
B
J
E
C
T
函数来从
u
j
s
o
n
字段中提取名为
h
e
i
g
h
t
的
J
S
O
N
属性的值
。
′
.height') AS height,：这里使用了GET_JSON_OBJECT函数来从ujson字段中提取名为height的JSON属性的值。'
.height′)ASheight,：这里使用了GETJSONOBJECT函数来从ujson字段中提取名为height的JSON属性的值。′.height’是JSON路径表达式，代表JSON对象的根，.用来访问对象的属性。所以’
.
h
e
i
g
h
t
′
表示从
J
S
O
N
对象的根开始，获取名为
h
e
i
g
h
t
的属性的值。
A
S
h
e
i
g
h
t
是将这个值重命名为列名
h
e
i
g
h
t
。关于
G
E
T
J
S
O
N
O
B
J
E
C
T
和
′
.height'表示从JSON对象的根开始，获取名为height的属性的值。AS height是将这个值重命名为列名height。 关于GET_JSON_OBJECT和'
.height′表示从JSON对象的根开始，获取名为height的属性的值。ASheight是将这个值重命名为列名height。关于GETJSONOBJECT和′.'的写法
GET_JSON_OBJECT是一个函数，用于从JSON格式的字符串中提取指定路径的值。这个函数接受两个参数：第一个参数是包含JSON的字段，第二个参数是JSON路径表达式，用来指定要提取的JSON属性的位置。
'$.'是JSON路径表达式的写法。在JSON路径中：
代表JSON对象的根。
.用来访问对象的属性或数组的元素。
如果属性名是height或width，那么’
.
h
e
i
g
h
t
′
和
′
.height'和'
.height′和′.width’就是指定这些属性的路径。
例如，如果ujson字段包含如下JSON字符串：
json{“height”: 180, “width”: 75}
那么GET_JSON_OBJECT(ujson, ‘
.
h
e
i
g
h
t
′
)
将返回
180
，
G
E
T
J
S
O
N
O
B
J
E
C
T
(
u
j
s
o
n
,
′
.height')将返回180，GET_JSON_OBJECT(ujson, '
.height′)将返回180，GETJSONOBJECT(ujson,′.width’)将返回75。
这种查询方式允许你直接在SQL查询中处理和使用存储在JSON字段中的数据，而不需要在应用层进行解析，从而提高了数据处理的效率和灵活性。