Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

How would I query to select length(int) which is within the array 'details' which is within the 'packets' column? Hopefully, the image attached will explain better than me!

I've tried SELECT packets.details.length FROM test.ssh_data which doesn't work.

This gives me the following error:

illegal column/field reference 'packets.details.length' with intermediate collection 'details' of type 'ARRAY<STRUCT<datestamp:STRING,length:INT>>
Thank you in advance!
In the Impala nested types support, arrays and maps are treated as nested tables. You need to reference them in the FROM clause to unnest them. In that case you can add the array to the from clause, taking care to refer to it via sd, the alias of the table it's inside. E.g.
SELECT d.length FROM test.ssh_data sd, sd.packets.details d
                Hi thanks for the comment. I gave this a go but gives no results? I also tried to add another column to this just to see if that column indeed does not have any values i.e.    SELECT d.length sd.datestamp FROM test.ssh_data sd, sd.packets.details d    but that also produces no results even though I know datestamp does indeed have values :/
– e110
                Aug 19, 2020 at 8:52
                Ah, you might want to change it to "FROM test.ssh_data sd, LEFT JOIN sd.packets.details d". The first version only returns rows if there are elements in the nested collection, so could be filtering out some of the top-level rows.
– Tim Armstrong
                Aug 19, 2020 at 18:26
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.