电脑建设网站在互联网访问,汕头,最近中美关系最新消息,牛商网招聘目录 一、前言二、目标介绍三、使用 pgsql 实现3.1 拆分 content 字段3.2 拆分 level 字段3.3 拼接两个拆分结果 四、使用 ODPS SQL 实现4.1 拆分 content 字段4.2 拆分 level 字段4.3 合并拆分 五、使用 MySQL 实现六、总结 一、前言
商业场景中#xff0c;经常会出现新的业… 目录 一、前言二、目标介绍三、使用 pgsql 实现3.1 拆分 content 字段3.2 拆分 level 字段3.3 拼接两个拆分结果 四、使用 ODPS SQL 实现4.1 拆分 content 字段4.2 拆分 level 字段4.3 合并拆分 五、使用 MySQL 实现六、总结 一、前言
商业场景中经常会出现新的业务继而产生新的业务数据这也难免会导致一些数据被孤立所以便需要对数据进行同步整合。在清洗数据的过程中难免也会出现同一个 SQL 逻辑需要使用不同的平台各自支持的一套 SQL 语言来实现。 本文介绍的就是一个同样的 SQL 逻辑通过不同的平台进行操作。相关平台阿里云的 postgresql 和 阿里云的 MaxCompute SQL下面称 ODPS SQL。
版本说明 PostgreSQLPostgreSQL 11.3 64-bit MySQLMySQL 8.0.16 ODPS SQLodps-sql-function version: r570e07eb77a5063f8c5715b0fa0beeba阿里云似乎会默认更新到最新版 二、目标介绍
首先介绍下抽象出来的数据有一个表记录2列数据可以看做是一个答题记录content列记录用户的某个类的答题内容而level列记录用户对应类的等级信息结构如下 创建临时数据集的 SQL
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
select *
from t1;最终处理效果为 将content和level里的键值信息分别都取出来然后拼接成一个用户在某个类型的答题内容和等级信息表单方便做业务分析。
基本分析content和level的字符类型都是字符串但他们的数据结构比较特殊content是一个 Json 数组结构的字符串而level是一个键值对结构的字符串。 进行处理的过程中可以将他们转为 Json 字符类型进行处理。 由于不同的用户的行为不同Json 的元素的长度也是不一致的所以要将类型type展开需要分别处理两个字段最后再进行联结。 三、使用 pgsql 实现
pgsql 有比较强大的 json 函数可以通过相关的 json 函数辅助处理 json 结构的数据参考 阿里云的 postgresql 的 json 函数文档 3.1 拆分 content 字段
content是一个 Json 数组结构的字符串所以可以通过::json函数转化为 json 数据类型之后通过json_array_elements()函数进行元素拆分一行一个元素。 SQL 参考如下
-- 拆元素
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
select user_id,json_array_elements(content::json) as content_kv
from t1;拆分结果如下可以看到已将content数据中的每一个元素都拆分开一行保留一个元素这时用户 102 有两行记录。
接下来就是把上面的结果中以键为字段名以值为字段值将固定长度的键值对处理为两个新列type和content。直接通过键取值即可参考 SQL 如下
-- 拆元素并取值
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
select user_id,json_array_elements(content::json) as content_kv,json_array_elements(content::json)-type as type,json_array_elements(content::json)-content as content
from t1;结果如下
返回结果中user_id、type和content三个字段便是最终需要的字段。所以拆分原始content字段的任务到此完成。 3.2 拆分 level 字段
接下来拆分level字段。level是一个键值对结构字符串键值对是标准 json 结构所以可以通过转化为 json 数据类型之后再借助json_object_keys()提取键值对中的所有键一行一个顺带也将键对应的值提取出来。
先将键取出来SQL 如下
-- 取键值对的键
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
select user_id,level,json_object_keys(level::json) as type
from t1;结果如下
有了键再取值就很方便了通过-取值即可。参考 SQL 如下
-- 取键值对的键和值
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
select user_id,level,json_object_keys(level::json) as type,level::json - json_object_keys(level::json) as level
from t1;结果如下
返回结果中user_id、type和level三个字段便是最终需要的字段。所以拆分原始level字段的任务到此完成。 3.3 拼接两个拆分结果
拼接这步则相对比较简单分别将以上的两个拆分的结果作为两个子查询然后通过user_id和type进行连接即可。 参考 SQL 如下
-- 拼接
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
select t1_content.user_id,t1_content.type,t1_content.content,t1_level.level
from(select user_id,json_array_elements(content::json)-type as type,json_array_elements(content::json)-content as contentfrom t1
)t1_content
left join(select user_id,json_object_keys(level::json) as type,level::json - json_object_keys(level::json) as levelfrom t1
)t1_level on t1_level.user_idt1_content.user_id and t1_level.typet1_content.type
;最终结果如下
通过 pgsql 处理还是比较简单的基本上就是四个函数便可解决四个函数分别是::json、json_array_elements()、json_object_keys()、-和-。 但是使用 ODPS SQL 就没有那么便捷了
四、使用 ODPS SQL 实现
ODPS 是阿里基于Hive的核心思想构建的不同的是 Hive 的文件存储在 hdfs 上ODPS 则存在阿里的盘古里而且 ODPS 针对 Hive 做了一些优化所以 ODPS SQL 和 HQL 比较接近和 MySQL 也有一定的相似性。
由于 ODPS SQL 没有像 pgsql 那么便捷的 json 函数所以需要通过其他的方式进行拆分元素。通过查阅官方的 SQL 文档发现可以通过以下的方式进行替换仅展示主要函数
Postgres SQLODPS SQL::jsonjson_parse()json_array_elements()regexp_count()、lateral view explode()json_object_keys()str_to_map()、map_values()、lateral view explode()-[] 或 json_extract()-json_extract()
参考ODPS SQL 的 json 等复杂函数。
下面具体来介绍一下。
4.1 拆分 content 字段
由于 ODPS SQL 不能一步到位将 json 数据拆开由一行变成多行所以需要通过另外的方式进行行向扩展即通过lateral view视图将数据进行发散而发散多少行呢这个由 json 的元素的个数来决定所以需要先计算元素的个数。
ODPS SQL 提供了regexp_count()函数可以通过计算数组元素的个数来确定。那么如何计算数组的元素个数呢通过观察数据的结构特点我通过识别元素的分割符},{作为标识即regexp_count(content,}\\s*,\\s*{)具体含义如下
\s匹配任何空白符避免因为出现空格而匹配不上等价于** [\t\n\f\r ]**\\由于系统采用反斜线\作为转义符因此正则表达式的模式中出现的\都要进行二次转义。例如正则表达式要匹配字符串ab。其中是正则中的一个特殊字符因此要用转义的方式表达在正则引擎中的表达方式是a\\b。由于系统还要解释一层转义因此能够匹配该字符串的表达式是a\\\b。简单理解就是在 SQL 中使用特殊字母转义时需要多加一层转义即多加一个**\**。*匹配前面的子表达式0次或多次。
下面来看看处理效果参考 SQL
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,content,json_parse(content) as content_json,regexp_count(content,}\\s*,\\s*{) as cntfrom t1
)
select *
from t1_json t1;输出结果
接下来就是将返回的结果根据cnt字段进行发散这里有一个临界点问题就是当cnt0时表示只有一个元素当cnt1则是两个依次类推。数组的索引是从 0 开始的所以通过lateral view创建的视图在发散时需要注意起点和临界值的处理。发散之后通过发散的序号来进行索引取出每一个元素的值。下面直接看下 SQL 来讲解
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,content,json_parse(content) as content_json,regexp_count(content,}\\s*,\\s*{) as cntfrom t1
)
select t1.user_id,t1.content_json,t1.cnt,index_tbl.index_no,json_extract(t1.content_json, concat($[,index_tbl.index_no,])) as content
from t1_json t1
-- 一行变成四行发散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制发散的值 index_no 小于等于 t1.cnt 的行把大于 t1.cnt 的行去掉
where index_tbl.index_not1.cnt
;上面 SQL 通过lateral view发散将一行扩展到四行数据增大了4倍然后再通过限制index_tbl.index_not1.cnt取出符合期望的行可以看看下面这张图返回的结果是没有加上最后where条件的结果从图中可以看出101 用户发散的四条记录中只有一条是有用的其他三条返回的content都是空值这些空值的行可以过滤掉这就是where的作用。
至此仅完成了前面json_array_elements()的处理结果而我们的目标是要将处理结果中的键值对的type对应的值和content对应的值取出来然后使用对应的键来命名接下来就把值取出。 取值使用的函数也是一样的使用json_extract()来取所以直接在上面的 SQL 中的json_extract()加上键即可具体 SQL 如下
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,content,json_parse(content) as content_json,regexp_count(content,}\\s*,\\s*{) as cntfrom t1
)
select t1.user_id,t1.content_json,json_extract(t1.content_json, concat($[,index_tbl.index_no,].type)) as type,json_extract(t1.content_json, concat($[,index_tbl.index_no,].content)) as content
from t1_json t1
-- 一行变成四行发散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制发散的值 index_no 小于等于 t1.cnt 的行把大于 t1.cnt 的行去掉
where index_tbl.index_not1.cnt
;返回结果如下
从返回的结果可以看到type和content都是带引号的这是因为json_extract()函数返回的数据类型是 JSON 类型而不是字符串所以还需要进行一步数据类型的显性转换。 最终的 SQL 如下
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,content,json_parse(content) as content_json,regexp_count(content,}\\s*,\\s*{) as cntfrom t1
)
select t1.user_id,cast(json_extract(t1.content_json, concat($[,index_tbl.index_no,].type)) as string) as type,cast(json_extract(t1.content_json, concat($[,index_tbl.index_no,].content)) as string) as content
from t1_json t1
-- 一行变成四行发散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制发散的值 index_no 小于等于 t1.cnt 的行把大于 t1.cnt 的行去掉
where index_tbl.index_not1.cnt
;4.2 拆分 level 字段
拆分level字段的时候可以用类似拆分content字段的方法对level字段计算元素的个数然后使用lateral view视图进行发散再取非空的行。 由于level字段的元素个数和content字段的元素个数是一致的所以可以使用前面已经统计好的cnt字段因为这二者都是通过这种方法进行发散最终是可以进行合并处理的为了不混淆统一使用cnt字段。 SQL 如下
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,level,regexp_count(content,}\\s*,\\s*{) as cnt,json_parse(level) as level_jsonfrom t1
)
select *
from t1_json t1
-- 一行变成四行发散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制发散的值 index_no 小于等于 t1.cnt 的行把大于 t1.cnt 的行去掉
where index_tbl.index_not1.cnt接下来有一个难点就是怎么知道level的键是什么前面content拆分完键都是一致的但是这里的键是可变长的可能是 1 个或 2 个或 3 个或十几个等所以这里不能一个个逻列需要使用其他的方法“智能”取键。
通过阅读官方文档参考ODPS SQL 的 json 等复杂函数发现了可以通过转为map数据类型进行处理先通过str_to_map()转为map类型然后使用map_keys()取键前面是通过键取值但是在map类型中还提供了map_values()函数也就是说可以直接取值不通过键一步到位 下面来实现一下
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,level,regexp_count(content,}\\s*,\\s*{) as cnt,json_parse(level) as level_jsonfrom t1
)
select t1.user_id,t1.level,str_to_map(regexp_extract(t1.level,{(.*?)}),,,:) as level_map
from t1_json t1
-- 一行变成四行发散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制发散的值 index_no 小于等于 t1.cnt 的行把大于 t1.cnt 的行去掉
where index_tbl.index_not1.cnt注意以上对t1.level进行了一层处理regexp_extract(t1.level,{(.*?)})目的是将外层的花括号去掉使用str_to_map()不需要花括号有键值对的结构即可。str_to_map(col_nmae,,,:)再根据逗号进行切割元素通过冒号处理为键值对。其实处理前后的“长相”是一样 以上 SQL 的返回结果如下虽然看上去一样但是数据类型是不一样的level是 string 类型level_map是 map 类型绕了这圈子其实就是转换下数据类型那能不能直接转呢至少目前从官方文档看不到这样的功能。
转换为 map 类型之后再使用map_keys()和map_values()分别取出键和值的数组然后再根据索引取出对应的值。索引取值可以使用[]或json_extract()取值。 SQL 如下
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,level,regexp_count(content,}\\s*,\\s*{) as cnt,json_parse(level) as level_jsonfrom t1
)
select t1.user_id,t1.level,map_keys(str_to_map(regexp_extract(t1.level,{(.*?)}),,,:))[index_tbl.index_no] as type,map_values(str_to_map(regexp_extract(t1.level,{(.*?)}),,,:))[index_tbl.index_no] as level
from t1_json t1
-- 一行变成四行发散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制发散的值 index_no 小于等于 t1.cnt 的行把大于 t1.cnt 的行去掉
where index_tbl.index_not1.cnt运行结果如下 注意这里返回的结果也需要进行数据类型转换type转为 string 类型而level2有重名被自动标记序号转为 int 或 bigint 类型。
到这里会有另外一个小细节需要处理就是当将type转换为 string 类型之后可以发现type字段依旧带有双引号。这是因为字符串是a即带双引号的a字符长度是 3。 怎么办呢再做一层处理可以在处理后将双引号去掉也可以在一开始的时候就将双引号去掉。 下面展示一开始就去掉双引号的方法参考 使用replace()先将·level字段的双引号去掉最后再转换数据类型。
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,level,regexp_count(content,}\\s*,\\s*{) as cnt,json_parse(level) as level_jsonfrom t1
)
select t1.user_id,t1.level,cast(map_keys(str_to_map(regexp_extract(replace(t1.level,,),{(.*?)}),,,:))[index_tbl.index_no] as string) as type,cast(map_values(str_to_map(regexp_extract(replace(t1.level,,),{(.*?)}),,,:))[index_tbl.index_no] as int) as level
from t1_json t1
-- 一行变成四行发散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制发散的值 index_no 小于等于 t1.cnt 的行把大于 t1.cnt 的行去掉
where index_tbl.index_not1.cnt至此完成拆分level字段。 4.3 合并拆分
接下来将上面两步拆分进行合并。直接来看看代码
with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,level,json_parse(content) as content_json,regexp_count(content,}\\s*,\\s*{) as cnt,json_parse(level) as level_json-- ,REGEXP_COUNT(level,:) as level_cntfrom t1
)
select t1.user_id,cast(json_extract(t1.content_json, concat($[,index_tbl.index_no,].type)) as string) as type1,cast(json_extract(t1.content_json, concat($[,index_tbl.index_no,].content)) as string) as content,cast(map_keys(str_to_map(regexp_extract(replace(t1.level,,),{(.*?)}),,,:))[index_tbl.index_no] as string) as type2,cast(map_values(str_to_map(regexp_extract(replace(t1.level,,),{(.*?)}),,,:))[index_tbl.index_no] as int) as level
from t1_json t1
-- 一行变成四行发散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制发散的值 index_no 小于等于 t1.cnt 的行把大于 t1.cnt 的行去掉
where index_tbl.index_not1.cnt;返回结果如下
至此是不是就大功告成了呢 不还不行还有两个问题没有解决。 问题1、数组的元素是有顺序保证的但是键值对不一定是按照数组的元素的顺序排列有可能会出现二者错位的现象只是刚好的举的例子没有错位。为了保证类型type一致需要再加一层操作将上述的 SQL 返回的临时表进行自联结。参考 SQL 如下 with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,level,json_parse(content) as content_json,regexp_count(content,}\\s*,\\s*{) as cnt,json_parse(level) as level_json-- ,REGEXP_COUNT(level,:) as level_cntfrom t1
)
,temp as(select t1.user_id,cast(json_extract(t1.content_json, concat($[,index_tbl.index_no,].type)) as string) as type1,cast(json_extract(t1.content_json, concat($[,index_tbl.index_no,].content)) as string) as content,cast(map_keys(str_to_map(regexp_extract(replace(t1.level,,),{(.*?)}),,,:))[index_tbl.index_no] as string) as type2,cast(map_values(str_to_map(regexp_extract(replace(t1.level,,),{(.*?)}),,,:))[index_tbl.index_no] as int) as levelfrom t1_json t1-- 一行变成四行发散4倍lateral view explode(array(0,1,2,3)) index_tbl as index_no-- 限制发散的值 index_no 小于等于 t1.cnt 的行把大于 t1.cnt 的行去掉where index_tbl.index_not1.cnt
)
select t2.user_id,t2.type1 as type,t2.content,t3.level
from temp t2, temp t3
where t2.user_idt3.user_id and t2.type1t3.type2最终结果如下
问题2、要发散多少行呢 可能前面的时候你会觉得纳闷为什么是array(0,1,2,3)而不是其他使用array(0,1,2,3)是方便理解先把数据跑通实际上这样的处理存在很大的风险一旦元素的个数超过了 4 个就会有数据丢失所以如果使用该方法可能需要把数组的元素加的足够长以规避该风险。但是将元素加得足够大之后原有的行记录都放大对应的倍数会极大消耗资源是否有更好的方法呢 带着这个问题求助下 GPT得到了一个反馈可以通过sequence(start, stop)函数来动态生成数组将start设置为 0而·stop设置为元素的个数cnt便可实现动态发散。参考 SQL 如下 参考阿里云 sequence 函数文档 with t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,level,json_parse(content) as content_json,regexp_count(content,}\\s*,\\s*{) as cnt,json_parse(level) as level_json-- ,REGEXP_COUNT(level,:) as level_cntfrom t1
)
,temp as(select t1.user_id,cast(json_extract(t1.content_json, concat($[,index_tbl.index_no,].type)) as string) as type1,cast(json_extract(t1.content_json, concat($[,index_tbl.index_no,].content)) as string) as content,cast(map_keys(str_to_map(regexp_extract(replace(t1.level,,),{(.*?)}),,,:))[index_tbl.index_no] as string) as type2,cast(map_values(str_to_map(regexp_extract(replace(t1.level,,),{(.*?)}),,,:))[index_tbl.index_no] as int) as levelfrom t1_json t1-- 使用 sequence() 动态发散lateral view explode(sequence(0, t1.cnt)) index_tbl as index_no
)
select t2.user_id,t2.type1 as type,t2.content,t3.level
from temp t2, temp t3
where t2.user_idt3.user_id and t2.type1t3.type2至此大功告成 五、使用 MySQL 实现
本来到这里就结束了突然心血来潮试试 MySQL 是否有方便的处理方法。 参考MySQL8 官方 json 函数介绍
实践之后发现并没有流程和 ODPS SQL 实现过程差不多不过函数有好些差异。下面提供一份 SQL 参考
with
-- 递归创建数字序列
RECURSIVE index_tbl AS (SELECT 0 AS index_noUNION ALLSELECT index_no 1 FROM index_tbl WHERE index_no 10
)
,t1 as(select 101 as user_id,[{type:a,content:abc}] as content,{a:1} as level union all select 102 as user_id,[{type:a,content:ad},{type:b,content:ab}],{a:1,b:2}
)
,t1_json as(select user_id,cast(content as json) content_json,cast(level AS json) level_json,json_length(cast(content as json)) cntfrom t1
)
,temp as(select t1.user_id,json_unquote(json_extract(content_json, concat($[,index_tbl.index_no,].type))) as type1 ,json_unquote(json_extract(content_json, concat($[,index_tbl.index_no,].content))) as content ,json_unquote(json_extract(json_keys(level_json) , concat($[,index_tbl.index_no,]))) as type2,json_unquote(json_extract(level_json, concat($.,json_unquote(json_extract(json_keys(level_json) , concat($[,index_tbl.index_no,])))))) as levelfrom t1_json t1-- 发散join index_tbl on index_tbl.index_nocnt
)
select t2.user_id,t2.type1 as type,t2.content,t3.level
from temp t2, temp t3
where t2.user_idt3.user_id and t2.type1t3.type2
;MySQL 没有像 ODPS SQL 的lateral view和explode()函数所以不能直接展开MySQL 通过RECURSIVE实现递归创建一个数字序列然后直接和元素个数的字段进行join并设置好边界值实现相同的效果。 使用RECURSIVE创建数字序列表时可以把index_no的上限设置稍微大一些后续关联直接动态限制发散的行数而不是直接放大倍数数据不会全部暴涨到index_no上限值的倍数和lateral view直接发散关联有一定区别。 MySQL 有json_length()函数可以直接计算元素个数相对 ODPS SQL 比较便利也支持json_keys()直接取键这个和 pgsql 类似只不过 pgsql 直接进行了发散将键拆分为一行一个而 MySQL 还需要结合index_tbl数字序列表手动发散另外MySQL 还提供了一个json_unquote()函数可以直接将json_extract()返回的 json 类型转为 字符串。 六、总结
本文分别通过 pgsql、ODPS SQL 和 MySQL 三种 SQL 语法进行 json 类型的处理。其中使用 pgsql 处理方式最简单且简洁而 ODPS SQL 最复杂中间进行了多次数据类型的变更甚至还需要使用一种更少见的数据类型 map 类型来辅助处理而 MySQL 则处于二者之间。 下面通过一张表格对比下三者实现同样功能需要使用到的函数
Postgres SQLODPS SQLMySQL::jsonjson_parse()cast() 或隐式转换json_array_elements()regexp_count()、lateral view explode()、sequence()、json_extract()、cast()RECURSIVE、json_extract()、json_unquote()json_object_keys()str_to_map()、map_values()、regexp_extract()、replace()、regexp_count()、lateral view explode()、sequence()、json_extract()、cast()RECURSIVE、json_extract()、json_unquote()、json_keys()-[] 或 json_extract()json_extract()-json_extract()json_extract() 往期回顾
阿里云大数据实战记录7如何处理生产环境表单的重复数据 阿里云大数据实战记录6修改生产环境表单字段数据类型 阿里云大数据实战记录5修改生产环境表单字段名称
