Querying nested fields in Flink SQL 1.11

Question

I have a schema that looks like this: Table: org_table

`transaction_amt` VARCHAR(64) NOT NULL,
`transaction_adj_amt` BIGINT NOT NULL ,
`event_time` TIMESTAMP(3),
`fd_output` ROW<`restime`  BIGINT `outcome` VARCHAR(64)>,

When I query this table like this:

SELECT transaction_amt, transaction_adj_amt, event_time, fd_output.restime as response_time, fd_output.outcome as outcome,  YEAR(event_time), MONTH(event_time) 
           FROM org_table

When running the above query on the table I am getting an error. Is there something I am missing here?

scala.MatchError: CAST (of class org.apache.calcite.sql.fun.SqlCastFunction

score 0 · Answer 1 · answered Sep 24 '21 at 02:59

0

Maybe you need to pay attention to the input parameters of Flink built-in functions, such as YEAR and MONTH functions, their input parameters are date types

Flink built-in function description can refer to https://ci.apache.org/projects/flink/flink-docs-release-1.13/docs/dev/table/functions/systemfunctions/

answered Sep 24 '21 at 02:59

ChangLi

772
2
8

If I remove the row fields from the query, it works even with YEAR and MONTH (built in functions) – Ahmed Saif Zamzam Sep 24 '21 at 08:31

score 0 · Answer 2 · edited Dec 16 '21 at 18:51

0

Try fd_output.get(0) to access restime, fd_output.get(1) for outcome.

edited Dec 16 '21 at 18:51

Tomerikoo

18,379
16
47
61

answered Dec 16 '21 at 18:35

w_upasana

11
3

Querying nested fields in Flink SQL 1.11

2 Answers2