在Dataworks中使用SQL拼接Json字符串的问题
原创在Dataworks中使用SQL拼接Json字符串的问题
原创
0 问题背景
其实人总喜欢待在自己的舒适区，对于走出舒适区从大脑里大家是十分抗拒的。比如字符的拼接，我们在学习sql的一开始就接触了concat，因此我们使用的最顺手，比如下面的问题：
表t_test_info有三个字段，memberid，membercode，member_name三个字段，需要把这三个字段拼接成如下图格式的json格式
备注：JSON (JavaScript Obiect Notation)对象是 JSON 中最常用的一种结构。它由大括号包裹，包含一组键值对，每个键值对之间通过逗号分隔。每个键值对由键(key)和值(value)组成，用冒号分隔。键是一个字符串，用双引号括起来，然后是冒号，而值可以是字符串、数值，布尔值、数组、甚至是另一个JSON对象。
具体的sql如下：
create table yht_dw_dev.t_test_info(
memberid BIGINT COMMENT '用户id',
membercode string COMMENT '用户编码',
member_name string COMMENT '用户姓名'
);
insert overwrite table yht_dw_dev.t_test_info
select 1100881 as memberid,'code001' as membercode,'UAS In\"Co Lt\"' as membername
union all
select 1100882 as memberid,'code002' as membercode,'BTS In\'Co Lt' as membername
union all
select 1100883 as memberid,'code003' as membercode,'AUS In\\Co Lt"' as membername
union all
select 1100883 as memberid,'code003' as membercode,'Invest Ment Co.Inc' as membername
union all
select 1100883 as memberid,'code003' as membercode,'Invest\\ Co.\tInc' as membername
;1 常规做法——但有bug
Json格式主要如上述示例，那么最简单的方式即通过concat函数进行拼接，具体如下：
select concat('{"memberid":',memberid,
',"membercode":"',membercode,
'","member_name":"',member_name,'"}') as str_json
from yht_dw_dev.t_test_info结果如下：
粗略看好像没有问题，像是都符合json的样子；但是如果我们使用函数get_json_object来校验一下，发现，并不是：
select
str_json,
GET_JSON_OBJECT(str_json,'$.member_name') as member_name
from(
select concat('{"memberid":',memberid,
',"membercode":"',membercode,
'","member_name":"',member_name,'"}') as str_json
from yht_dw_dev.t_test_info
) t结果如下：
从结果中可以看出，通过concat函数拼接而成的json中，其中 UAS In"Co Lt" 、 AUS In\Co Lt" 和 Invest\\ Co. Inc三个值中由于存在特殊字符 " 和 \ 而导致拼接json失败，而仅仅成了类似json的格式。而在实际场景中，由于公司在前端对用户名未做严格校验或者经常刷数据的行为，导致用户名经常出现类似的情况，因此，使用concat函数拼接json就不可避免的遇到上述所说的问题。
那么如何来解决该问题呢？
2 Maxcompute中相关函数语法
通过翻阅Maxcompute中提供的函数列表，发现了解决方式。在提到具体解决方式之前，也提前来了解一下几个函数的基础语法。
2.1 to_json
命令格式：
string to_json(<expr>)可以将指定复杂类型以指定格式输出，具体的复杂模式有struct，array和map。
注意：
如果输入为STRUCT类型（struct<key1:value1, key2:value2）：
- 转换为JSON字符串时，Key会全部转为小写。
value如果为NULL，则不输出value本组的数据。例如value2为NULL，则key2:value2不会输出到JSON字符串。
以下为示例：
select TO_JSON(MAP('a',ARRAY('hello','world')));
select to_json(NAMED_STRUCT('b',100,'WorkSpace','shared'));
select TO_JSON(ARRAY(NAMED_STRUCT('k11',100,'k12',150)))结果为：
{"a":["hello","world"]}
{"b":100,"workspace":"shared"} ***
[{"k11":100,"k12":150}]在***处可以明显看到 WorkSpace 变成了 workspace 纯小写
2.1 named_struct
命令格式：
struct named_struct(string <name1>, T1 <value1>, string <name2>, T2 <value2>[, ...])name:必填，指定STRING类型的Field名称，此参数为常量
value:必填，可以为任意类型
select named_struct('userid',20001,'username','LiLei','gender','F','weight',73.50);
-- 结果
{userid:20001, username:LiLei, gender:F, weight:73.5}最后的返回一个struct数据类型，可以类比python语言中的字典来记忆。但与字典的区别在于key或者value如果是字符串时，没有引号来包裹。
除此named_struct函数之外，还有一个struct函数，与named_struct函数一同记忆。
命令格式一样：
struct struct(<value1>,<value2>[, ...])只不过，返回的数据中没有指定的key值了，全部变成了value，而key值变成了默认的 col1、col2、col3... 了，具体如下：
select struct('userid',20001,'username','LiLei','gender','F','weight',73.50);
-- 结果
{col1:userid, col2:20001, col3:username, col4:LiLei, col5:gender, col6:F, col7:weight, col8:73.5}3 Maxcompute中json拼接解决方法
借助2中的函数，我们可以轻松解决1中遇到的问题。
同样的1中的例子，具体SQL脚本如下：
select
str_json,
GET_JSON_OBJECT(str_json,'$.memberName') as member_name_upper,
GET_JSON_OBJECT(str_json,'$.membername') as member_name_lower
from(
select to_json(named_struct('memberId',memberid,
'memberCode',membercode,
'memberName',member_name)) as str_json
from yht_dw_dev.t_test_info
) t;结果如下：
4 结论
第一、通过第三节的内容，我们可以摒弃原有的通过concat函数来拼接json字符串的做法，因为官方提供的函数to_json和named_struct组合来拼接json可以保证拼接的准确性，不会受到字段内的特殊字符的影响。
第二、named_struct函数会将key值全部调整为小写，此时再用大写来解析json字符串会出现解析不出来的情况。
5 参考文献
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
