Read comments in the code. You can filter the dataset as you want:
with
my_table as(
select stack(2, '{ "foo": { "bar": [{ "x": 1, "y": 0 }, { "x": 0, "y": 1 }] } }',
'{ "foo": { } }'
) as EventData
)
select * from
(
select --get_json_object returns string, not array.
--remove outer []
--and replace delimiter between },{ with ,,,
--to be able to split array
regexp_replace(regexp_replace(get_json_object(EventData, '$.foo.bar'),'^\[|\]$',''),
'\},\{', '},,,{'
)bar
from my_table t
) s --explode array
lateral view explode (split(s.bar,',,,')) b as bar_element
--get struct elements
lateral view json_tuple(b.bar_element, 'x','y') e as x, y
Result:
s.bar b.bar_element e.x e.y
{"x":1,"y":0},,,{"x":0,"y":1} {"x":1,"y":0} 1 0
{"x":1,"y":0},,,{"x":0,"y":1} {"x":0,"y":1} 0 1
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…