Presto - 从非嵌套列中选择所有列以及常规列

Question

我有一个雅典娜表（从json数据转储创建），它在一列中有一个嵌套数组。我能够使用以下查询取消嵌套它们。

select root.Profile,
addressdata.town, addressdata.territory, addressdata.postalCode, addressdata.country, 
street.addressLine1, street.addressLine2, street.door, streetID, addressID
From 
sampletable a 
CROSS JOIN UNNEST(root.address.primaryAddresses) WITH ORDINALITY as t(addressdata, addressID)
CROSS JOIN UNNEST(addressdata.streetAddress) WITH ORDINALITY as t(street, streetID)

问题是，与其使用 addressdata.town ， addressdata.territory ，我想选择所有列而不提及列名以及常规的非嵌套列。

我尝试了select root.profile, addressdata.*但这是在一列中返回所有内容。

请帮忙！

Answer 1

据我了解，您得到了正确的结果，但您也想加入addressdata的其他列。如果是这种情况，您可以像这样：

WITH base_table AS (
    SELECT
        root.Profile,
        addressdata.town AS town,
        addressdata.territory,
        addressdata.postalCode,
        addressdata.country,
        street.addressLine1,
        street.addressLine2,
        street.door,
        streetID,
        addressID
    FROM sampletable a
    CROSS JOIN UNNEST(root.address.primaryAddresses)
        WITH ORDINALITY AS t(addressdata, addressID)
    CROSS JOIN UNNEST(addressdata.streetAddress)
        WITH ORDINALITY AS t(street, streetID)
),
SELECT
    base_table.*,
    addressdata.*
FROM base_table
JOIN addressdata
    ON base_table.town = addressdata.town

联接表需要做更多的工作，但使代码在将来更具可读性。