Flink-SQL: Extract values from nested objects

Question

i'm using Flink SQL and the following scheme shows my source data (belonging to some Twitter data):

CREATE TABLE `twitter_raw` (
    `entities` ROW(
        `hashtags` ROW(
            `text` STRING,
            `indices` INT ARRAY
        ) ARRAY,
        `urls` ROW(
            `indices` INT ARRAY,
            `url` STRING,
            `display_url` STRING,
            `expanded_url` STRING
        ) ARRAY,
        `user_mentions` ROW(
            `screen_name` STRING,
            `name` STRING,
            `id` BIGINT
        ) ARRAY
    )
)
WITH (...);

I want to get only the hashtags in a collection. Therefore i have to map the collection of constructed objects (ROW) to an array of STRING.

Like this scheme:

CREATE TABLE `twitter_raw` (
    `entities` ROW(
        `hashtags` STRING ARRAY,
        `urls` STRING ARRAY,
        `user_mentions` STRING ARRAY
    )
)
WITH (...);

How can i achieve this with Flink-SQL? Maybe built-in functions (JSON-functions?) or own UDF or do i have to write a DataStream Job?

Thanks in advance.

Peter Csala · Accepted Answer · 2021-12-30 18:08:08Z

4

The SQL command UNNEST helps in this case. It is like EXPLODE in Spark.

You can solve it by creating a new row for each hashtag in the hashtags array:

SELECT hashtag, index
FROM twitter_raw 
CROSS JOIN UNNEST(hashtags) AS t (hashtag, index)

edited Dec 30, 2021 at 18:08

Peter Csala

23.7k16 gold badges51 silver badges96 bronze badges

answered Dec 30, 2021 at 16:20

JanOels

1412 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kishorekumar Yakkala Over a year ago

FYI, Unnest is useful for Map data type as well

Francesco Guardiani · Accepted Answer · 2021-12-24 10:05:03Z

1

You can either define a computed row, or a VIEW, and then extracting the hashtags field using the dot notation. e.g.:

CREATE VIEW hashtags_raw (hashtags) AS 
  SELECT entities.hashtags AS hashtags FROM twitter_raw

answered Dec 24, 2021 at 10:05

Francesco Guardiani

6973 silver badges16 bronze badges

1 Comment

JanOels Over a year ago

This view does not fix the problem of accessing the individual fields of the array.

Collectives™ on Stack Overflow

Flink-SQL: Extract values from nested objects

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related