How to select an array of specific struct values in hive sql?-CodePudding

Let's say I have a column containing data in this format inside a table named myTable:

myColumn
[{"id": 1, color: "red"}, {"id": 2, color: "blue"}]
[{"id": 1, color: "orange"}, {"id": 2, color: "purple"}]

How do I get extract the colors into an array for each row, in this format?

result
[red, blue]
[orange, purple]

What I have tried so far -

select arr.color
from myTable as mt
    lateral view outer explode(mt.myColumn) as arr
limit 10;

Unfortunately this produces result containing 1 color in each row. How do I create an array for colors in each row?

CodePudding user response：

You don't need to use explode function. You can merely call your desired field directly on your array column:

SELECT myColumn.color FROM myTable