sql - 如何返回数组来自 hive 中的 json_tuple
问题描述
我有一个带有 json 列的配置单元表。它是兽人格式,只有一列有 json 字符串。
- json_column
{
"type":"REGULAR",
"period":[
"ONCE_PER_FOUR_WEEK",
"ONCE_PER_SIX_WEEK",
"ONCE_PER_ONE_MONTH",
"ONCE_PER_TWO_MONTH",
"ONCE_PER_THREE_MONTH"
],
"count":[
"4",
"8",
"12"
],
"day":[
"SATURDAY",
"SUNDAY"
],
"content":[
{
"count":"2",
"value":5,
"unit":"PERCENT"
},
{
"count":"3",
"value":10,
"unit":"PERCENT"
}
]
}
我想将此栏分为五栏。
type string,
period array<string>,
count array<string>,
day array<string>,
content array<struct<count :string, value :int, unit :string>>
首先,我将此列分为四列,并用json_tuple
.
SELECT b.type as type,
b.period as period,
b.count as count,
b.deliveryImpossibleDay as day,
b.content as content
FROM sample_table a
LATERAL VIEW JSON_TUPLE(a.content, 'type', 'period', 'count', 'day',
'content') b
AS type, period, count, day, content
我需要将content
列更改为结构数组,但如果返回字符串值。
[{"count":"2","value":5,"unit":"PERCENT"},{"count":"3","value":10,"unit":"PERCENT"}]
我怎样才能将它从 转换string
为array<struct<count :string, value :int, unit :string>>
?
请问有什么想法吗?
解决方案
不幸的是 JSON_TUPLE 和 GET_JSON_OBJECT 返回字符串。要在不使用自定义 UDF 的情况下转换 JSON 字符串,您可以解析字符串、拆分、分解和重新组装结构和数组。
演示:
with sample_table as (
select '{
"type":"REGULAR",
"period":[
"ONCE_PER_FOUR_WEEK",
"ONCE_PER_SIX_WEEK",
"ONCE_PER_ONE_MONTH",
"ONCE_PER_TWO_MONTH",
"ONCE_PER_THREE_MONTH"
],
"count":[
"4",
"8",
"12"
],
"day":[
"SATURDAY",
"SUNDAY"
],
"content":[
{
"count":"2",
"value":5,
"unit":"PERCENT"
},
{
"count":"3",
"value":10,
"unit":"PERCENT"
}
]
}' as content
)
SELECT b.type as type,
--to convert to array<string>
--remove [" and "], split by ","
split(regexp_replace(b.period,'^\\["|"\\]',''),'","') as period,
split(regexp_replace(b.count,'^\\["|"\\]',''),'","') as count,
split(regexp_replace(b.day,'^\\["|"\\]',''),'","') as day,
--convert to struct and collect array of structs
collect_list(named_struct('count', x.count, 'value', int(x.value), 'unit', x.unit)) as content
FROM sample_table a
LATERAL VIEW JSON_TUPLE(a.content, 'type', 'period', 'count', 'day', 'content') b AS type, period, count, day, content
LATERAL VIEW explode(split(regexp_replace(b.content,'^\\[|\\]$',''), --remove []
'(?<=\\}),(?=\\{)' --split by comma only after } and before {
)) e as str_struct
LATERAL VIEW JSON_TUPLE(e.str_struct,'count','value', 'unit') x as count, value, unit
group by b.type,
b.period,
b.count,
b.day
结果:
type period count day content
REGULAR ["ONCE_PER_FOUR_WEEK","ONCE_PER_SIX_WEEK","ONCE_PER_ONE_MONTH","ONCE_PER_TWO_MONTH","ONCE_PER_THREE_MONTH"] ["4","8","12"] ["SATURDAY","SUNDAY"] [{"count":"2","value":5,"unit":"PERCENT"},{"count":"3","value":10,"unit":"PERCENT"}]
推荐阅读
- android - 使用现有证书为 React Native Android 应用程序生成签名的 apk
- reactjs - 在反应中传递来自 JSON 的图像路径
- microsoft-cognitive - 在某些版本的 Windows 中连接 SR-300 相机时遇到麻烦
- php - 遍历数组并检查哪个字符串长度更大
- maven - 使用 Docker 和 Kotlin 缓存 Maven
- sql-server - 运行此查询的更好性能
- python - 如何通过附加操作将列表推导用于 for 循环
- javascript - JS确认表单验证工作,但验证字不显示
- python - 我无法扫描我的条形码(escpos 打印机)
- xamarin - 即使用户删除并重新下载应用程序,Xamarin.Essentials 也可以跟踪首次启动吗?