flink 嵌套 json 案例

作者: loukey_j | 来源:发表于2021-05-14 17:53 被阅读0次

CREATE TABLE ta (
e STRING,
a ROW(a21 string,a22 string),
b ROW(b21 string,b22 ROW(b31 string)),
c ARRAY<string>,
d ARRAY<ROW(d21 ROW(d31 string,d32 string))>,
f ARRAY<ROW(f21 ROW(f31 string,f32 string),f22 ARRAY <ROW (f33 string, f34 ROW(f41 string))>)>
)

消息数据:
{"a":{"a21":"a21","a22":"a22"},"b":{"b21":"b21","b22":{"b31":"b31"}},"c":["c21","c22"],"d":[{"d21":{"d31":"d311","d32":"d321"},"d22":"d221"},{"d21":{"d31":"d312","d32":"d322"},"d22":"d222"}],"f":[{"f21":{"f31":"f311","f32":"f321"},"f22":[{"f33":"f331","f34":{"f41":"f411"}},{"f33":"f332","f34":{"f41":"f412"}}]},{"f21":{"f31":"f312","f32":"f322"},"f22":[{"f33":"f333","f34":{"f41":"f413"}},{"f33":"f334","f34":{"f41":"f414"}}]}],"e":"e"}

select * from ta;
+----+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+---------------------------------------------------------------------+
| op | e | a | b | c | d | f |
+----+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+---------------------------------------------------------------------+
| +I | e | a21,a22 | b21,b31 | [c21, c22] | [d311,d321, d312,d322] | [f311,f321,[f331,f411, f332,f412], f312,f322,[f333,f413, f334,f414]]|

select
a.a21,a.a22,
b.b21,b.b22.b31,
c[1],c[2],
d[1].d21.d31,d[1].d21.d32,
d[2].d21.d31,d[2].d21.d32,
f[1].f21.f31,f[1].f21.f32,
f[2].f21.f31,f[2].f21.f32,
f[1].f22[1].f33,f[1].f22[1].f34.f41,
f[1].f22[2].f33,f[1].f22[2].f34.f41,
f[2].f22[1].f33,f[2].f22[1].f34.f41,
f[2].f22[2].f33,f[2].f22[2].f34.f41
from ta
+----+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+
| op | a21 | a22 | b21 | b31 | EXPR $4 | EXPR$ 5 | EXPR $6 | EXPR$ 7 | EXPR $8 | EXPR$ 9 | EXPR $10 | EXPR$ 11 | EXPR $12 | EXPR$ 13 | EXPR $14 | EXPR$ 15 | EXPR $16 | EXPR$ 17 | EXPR $18 | EXPR$ 19 | EXPR $20 | EXPR$ 21 |
+----+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+
| +I | a21 | a22 | b21 | b31 | c21 | c22 | d311 | d321 | d312 | d322 | f311 | f321 | f312 | f322 | f331 | f411 | f332 | f412 | f333 | f413 | f334 | f414 |