flinkSql在遇到复杂的json时,如何映射成表呢? 这里推荐一种方便的方式:
嵌套的json格式如下:
{
"data":{
"data":{
"mac_value":0,
"ad_name":2056,
"voice":75,
"dataTimeStamp":1598522106830
},
"type":3,
"deviceId":"001C92F7DCd85A"
},
"timestamp":1598522106835,
"type":1
}
期望映射成一个source表
CREATE TABLE t_1144_1 (
-- 最主要的是这行,定义类型为ROW
`data` ROW(deviceId string,`data` ROW(mac_value string,ad_name string,voice string,dataTimeStamp string)),
-- 通过xx.xx取数据,映射成自己想要的表
deviceId as cast(`data`.deviceId as varchar) ,
mac_value as cast( `data`.`data`.mac_value as integer),
ad_name as cast( `data`.`data`.ad_name as integer),
voice as cast( `data`.`data`.voice as integer),
eventTime as cast( `data`.`data`.`dataTimeStamp` as varchar),
-- 时间窗口以及水位线
windowEventTime AS TO_TIMESTAMP(FROM_UNIXTIME((cast(cast( `data`.`data`.`dataTimeStamp` as varchar) as bigint)+43200000)/1000)),
WATERMARK FOR windowEventTime AS windowEventTime - INTERVAL '2' SECOND
) WITH (
xxxx
)
后续的sql,就可以基于这个表来进行操作了.
通过使用ROW的方式, 可以让复杂的json转变为可操作的schema,使用时, 可以让xx.xx.xx来使用.