查詢(xún)嵌套數(shù)組
如果一個(gè)表包含 STRUCT 的 ARRAY，則可展平 ARRAY 以查詢(xún) STRUCT 的字段。您也可以展平 STRUCT 值的 ARRAY 類(lèi)型字段。

查詢(xún) ARRAY 中的 STRUCT 元素
以下示例將 UNNEST 與 CROSS JOIN 結(jié)合使用，以展平 STRUCT 的 ARRAY。

WITH races AS (SELECT "800M" AS race,[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]AS participants) SELECTrace,participant FROM races r CROSS JOIN UNNEST(r.participants) as participant; +------+---------------------------------------+ | race | participant | +------+---------------------------------------+ | 800M | {Rudisha, [23.4, 26.3, 26.4, 26.1]} | | 800M | {Makhloufi, [24.5, 25.4, 26.6, 26.1]} | | 800M | {Murphy, [23.9, 26, 27, 26]} | | 800M | {Bosse, [23.6, 26.2, 26.5, 27.1]} | | 800M | {Rotich, [24.7, 25.6, 26.9, 26.4]} | | 800M | {Lewandowski, [25, 25.7, 26.3, 27.2]} | | 800M | {Kipketer, [23.2, 26.1, 27.3, 29.4]} | | 800M | {Berian, [23.7, 26.1, 27, 29.3]} | +------+---------------------------------------+

查詢(xún) STRUCT 中的 ARRAY 類(lèi)型字段
您也可以從嵌套的重復(fù)字段中獲取信息。例如，以下語(yǔ)句用于返回在 800 米賽跑中圈速最快的參賽選手。

WITH races AS (SELECT "800M" AS race,[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]AS participants) SELECT race, (SELECT nameFROM UNNEST(participants),UNNEST(splits) AS durationORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap FROM races;+------+-------------------------+ | race | runner_with_fastest_lap | +------+-------------------------+ | 800M | Kipketer | +------+-------------------------+

總結(jié)

以上是生活随笔為你收集整理的sql 查询嵌套数组的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。