Exploding Nested Arrays - How to

[rajkve5055]

Hi,

I have nested arrays in my Kafka topic

Array1 [{
String1 : "Value1",
String2 : "Value2"
Array2: [{
String1: "Value1",
Int1: "Value"]}]

What is the best way to Explode this, Nested Explodes doesn’t seem to work, appreciate any pointers, thanks.

[filpano]

I suppose it depends on what you want to access.
If you want to access Array1.String1/Array1.String2, for example:

CREATE OR REPLACE STREAM ARR_OUTER_EXPLODED
AS SELECT
  EXPLODE(ARRAY1) AS ARRAY1
FROM SOURCE_STREAM;

CREATE OR REPLACE STREAM ARR_OUTER
AS SELECT
  ARRAY1->STRING1,
  ARRAY1->STRING2
FROM ARR_OUTER_EXPLODED 

Should work.
If you want to access Array2.String1/Array2.Int1:

CREATE OR REPLACE STREAM ARR_OUTER_EXPLODED
AS SELECT
  EXPLODE(ARRAY1) AS ARRAY1
FROM SOURCE_STREAM;

CREATE OR REPLACE STREAM ARR_INNER_EXPLODED
AS SELECT
  EXPLODE(ARRAY1->ARRAY2)
FROM ARR_OUTER_EXPLODED 

CREATE OR REPLACE STREAM ARR_INNER
AS SELECT
  ARRAY2->STRING1,
  ARRAY2->INT1
FROM ARR_INNER_EXPLODED

should work.