在Dataworks中SQL拼接json字符串问题的补遗

1.0 背景

在之前的文章《在Dataworks中使用SQL拼接Json字符串的问题》中我提到，在dataworks有一个拼接字符串的函数 to_json 搭配 named_struct 函数，可以适配几乎各种复杂的json数据结构。但是我忽略了一个问题，请看下面例子：

select 
t2.bill_no,
to_json(NAMED_STRUCT(t2.bill_no,to_json(NAMED_STRUCT('code','CNY','name','CNY')))) as entry_state_info
FROM  t_test_data t2
limit 20
;

结果为：

FAILED: ODPS-0130071:[3,22] Semantic analysis exception - parameter 1 for function NAMED_STRUCT expect must be a constant of STRING

我们发现这里报错了，报的错的意思是：named_struct函数中的key应该是一个常数，而不能是列值。其实这是我在上一篇文章《在Dataworks中使用SQL拼接Json字符串的问题》 所遗漏的。那么这种情况如何来解决呢？

2.0 原因以及解决方案

基于上述的报错，我们回到官方文档来寻找答案，文档中是这样对named_struct函数描述的：

struct named_struct(string <name1>, T1 <value1>, string <name2>, T2 <value2>[, ...])

参数说明：

• value：必填。可以为任意类型。
• name：必填。指定STRING类型的Field名称。此参数为常量。

结合报错以及函数中的name字段的说明（黑体部分），其实我们可以找到被遗漏的原因了。

name字段必须是定制，而不能使用变量，比如说列值，因此，如果json格式中存在name值为变量的情况，这种情况下使用named_struct函数其实是无法得到结果的，此时又需要concat函数来手工拼json了，如下：

select 
t2.bill_no,
concat('{"',t2.bill_no,'":',to_json(NAMED_STRUCT('code','CNY','name','CNY')),'}') as entry_state_info
FROM  t_test_data t2
limit 20
;

结果：

结果正确。

3.0 文章小结

其实所有的技术都是处在螺旋中前进，一开始，我们使用最基本的concat来实现拼接json的功能，它的优点在于通用性强，缺点需要对json的所有{}或者""来手工处理，增加了脚本的复杂程度和易错程度。其次，我们发现了已有的函数to_json和named_struct来处理，优势在于解决了concat函数拼接的复杂程度，但是缺点是在于部分场景下（比如上文描述的情况）无法适配。最后，我们通过concat加上to_json和named_struct三个函数一起，做到既脚本简单又通用性强，所谓的“既要有要”的目的达成。

学习可能也在于此吧。