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

Question

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?

Vincent Doba · Accepted Answer · 2021-11-01 21:33:05Z

3

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

answered Nov 1, 2021 at 21:33

Vincent Doba

5,1683 gold badges28 silver badges49 bronze badges

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

1 Answer 1