sqlarraysselectstructimpala
How to query to select a struct of array of struct column within a dataframe in Impala?
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!
Solution
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
- SQL convert nvarchar to float
- MySQL Workbench edit data
- Normalize Google Sheet table (1st step)
- Combine overlapping date ranges
- How to use values from CTE in ON CONFLICT UPDATE
- pyspark statistical window function keeps calculating NULL values
- sqlDecimal to decimal clr stored procedure Unable to cast object of type 'System.Data.SqlTypes.SqlDecimal' to type 'System.IConvertible'
- Sum a record with its previous not-null ordered by timestamp
- Prevent redundant results in SQL query
- SQL Query in Snowflake - Calculate Days of Supply
- How can I find out what FOREIGN KEY constraint references a table in SQL Server?
- How to extract column names from SQL query using Python
- Select on self-referencing table where all chains meet a condition
- SQL query to check if a name begins and ends with a vowel
- In a join, fields with null values are not getting shown but in normal query they are
- MySQL JSON_OBJECT() datetime format
- How to find overlapping records and select the latest record?
- SQL query for returning rows with only certain values in list but not others?
- Why does my query fail to convert a varchar value to int?
- How do I insert a record into a Postgres DB based off the MAX of one column?
- How to auto insert Current DATE in SQL with Java / Hibernate
- IsDate Function in SQL evaluates invalid dates as valid
- SQL Server - Value passes ISDATE() but fails to CAST as DATE or DATETIME
- Snowflake Regex to Camel case file name
- SQL Server DATEDIFF round up the YEAR difference. How to round it down?
- Path table to HTML treeview in SQL Server
- SUM and Decimal precision
- Choosing simple products and variable products separately in WooCommerce tables
- collation conflict between "Hebrew_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"
- Separating fields in the same column