Unnest 轉換

注意:轉換屬於基礎程式語言的一部分,使用者無法直接存取。本篇文章的內容僅供參考。

在來源資料中,依據索引鍵拆分來自陣列或物件欄的巢狀資料,以便建立新資料列或欄。

這種轉換在陣列或物件類型的資料欄上的運作方式不同。

unnest 轉換必須包含你所指定的鍵值,當做轉換步驟的一部分。如果陣列資料的資料欄不含鍵值,要打散此類資料欄的巢狀結構時,請使用 flatten 轉換。請參閱 Flatten 轉換一文。

系統可能會自動將這項轉換指令當做您方案的前幾步驟。請參閱初始剖析步驟

基本用法

unnest col: myObj keys:'sourceA','sourceB' pluck:true markLineage:true

輸出:

  • myObj 資料欄擷取 sourceAsourceB 鍵值的對應值,並分成新的兩個資料欄。
  • 由於 markLineagetrue,新資料欄名稱的前面會加上來源名稱:sourceA_column1sourceB_column2
  • 由於 plucktrue,來源資料欄中未遺漏的值會新增至對應的新資料欄,並從來源資料欄中移除。

參數

unnest col:column_ref keys:'key1','key2' [pluck:true|false] [markLineage:true|false]

符記是否必要?資料類型說明
unnest轉換轉換名稱
col字串來源資料欄名稱
keys字串加上引號的鍵值名稱,各項以半形逗號分隔。請見以下範例。
pluck布林值若是 true,從來源所打散的任何巢狀值也會一併從來源中移除。預設值是 false
markLineage布林值如果是 true,新資料欄名稱的前面會加上來源資料欄的名稱。

如要進一步瞭解語法標準,請參閱語言文件語法附註一文。

col

找出要套用轉換指令的資料欄 (只能指定一個資料欄)。

使用須知:

是否必要?資料類型
字串 (資料欄名稱)

keys

用來擷取指定的來源資料欄資料,並以半形逗號分隔的鍵值清單。

  • 鍵值必須用引號包覆。(例如 'key1','key2')。任何引號所包覆的值都會視為單一鍵值的路徑。
  • 鍵值的寫法會區分大小寫。
  • 必須列出每個鍵值,且不能指定特定範圍的鍵值。

注意:若鍵值含有空格之類的非英數字元,就必須用方形括號和引號括住,有底線的值則不需要以括號括住。

這種以半形逗號分隔的鍵值清單,可以用來決定要以來源資料產生的資料欄。如果指定三個鍵值,就會以來源資料欄產生含有對應值的三個新資料欄。

這個參數有不同的語法可用在單層和多層的巢狀資料上。物件和陣列資料類型的語法也有所不同。

使用須知:

是否必要?資料類型

以半形逗號分隔的字串值。

語法範例提供如下。

物件資料鍵值 - 單一層級

注意:鍵值名稱有大小寫之分。

若是物件欄位中的單個頂層鍵值,可以將該鍵指定為單個加上引號的字串:

unnest col:myCol keys: 'myObjKey'

上述語法會在物件的頂層鍵值中尋找 myObjKey 此鍵,並傳回相對應的值給新資料欄。您也可以用方形括號括住此鍵:

unnest col:myCol keys: '[myObjKey]'

如果要指定多個第一層級的鍵值,請使用下列指令:

unnest col:myCol keys:'myObjKey','my2ndObjKey'

上述語法會產生兩個新資料欄 (myObjKeymy2ndObjKey),內有該鍵的相對應的值。

物件資料鍵值 - 多層級

您也可以參照物件第一層級以下的鍵值:

資料範例:

{ "Key1" :
  { "Key1A" :

  }
}
{ "Key2" :
  { "Key2A" :

  }
}
{ "Key3" :
  { "Key3A" :

  }
}

如果要取得 Key1A 鍵值的資料,請使用下列指令:

unnest col: myCol keys: 'Key1[Key1A]'

在新資料欄中顯示的值如下:

如果要打散第三層級巢狀結構的值,請使用類似如下的轉換指令:

unnest col: myCol keys: 'Key2[Key2A][Key2A1]'

這個轉換會在新資料欄中產生 Value2 的值。

陣列資料鍵值 - 單一層級

可參照使用從 0 開始的索引的陣列元素或鍵值名稱。

注意:必須括起陣列鍵值的所有參照,陣列鍵值僅供索引編號參照。

陣列資料範例:

["red","orange","yellow","green","blue","indigo","violet"]

unnest col: myCol keys:'[1]'

上方轉換會從陣列擷取 orange 值。

unnest col: myCol keys:'[1]','[3]'

傳回的值:orangegreen

陣列資料鍵值 - 多層級

在下列範例中,巢狀的陣列資料結構符合先前範例中物件資料的結構。

[ [ "Item1", ["Item1A", ["Item1A1","Value1"] ] ], [ "Item2", ["Item2A",  ["Item2A1","Value2"] ] ], [ "Item3", ["Item3A",["Item3A1","Value3"] ] ] ] 

打散巢狀 Items2A 的值:

unnest col:myCol keys:'[1][0]'

下列為插入新資料欄的值:

["Item2A1","Value2"]

從第三層級打散巢狀結構:

unnest col:myCol keys:'[2][0][0]'

插入的值是 Item3A

pluck

表明是否該將從來源新增至輸出資料欄的值,從來源資料中移除。

  • 設為 true 即會在這些值新增至輸出資料欄後,將值從輸出資料欄移除。
  • (預設) 設定為 false 則不影響來源資料欄。

使用須知:

是否必要?資料類型
布林值

markLineage

當設定為 true 時,來源資料欄名稱會加在新資料欄名稱前面。範例:

來源資料欄輸出資料欄
mySourceColumnmySourceColumn_column1

欄位名稱中會附加對巢狀鍵值的參照:

來源資料欄鍵值輸出資料欄
mySourceColumnkeys: '[Key1][Key2]'mySourceColumn_Key1_Key2

注意:如果您的 unnest 轉換並未改變列數,假設在執行轉換時資料列數量資訊仍可使用,就仍能在資料柵格內存取來源資料的列數資訊。

使用須知:

是否必要?資料類型
布林值

範例

範例 - 打散巢狀物件

您的資料集如下,Sizes 欄包含可用物件資料的尺寸。

來源:

ProdIdProdNameSizes
1001Hat{'Small':'N','Medium':'Y','Large':'Y','Extra-Large':'Y'}
1002Shirt{'Small':'N','Medium':'Y','Large':'Y','Extra-Large':'N'}
1003Pants{'Small':'Y','Medium':'Y','Large':'Y','Extra-Large':'N'}

轉換:

注意:您可能需要在 Sizes 欄中執行部分替換,才能使其推測為適當的物件類型值,端看您的來源資料格式。最後的格式應會如上所示。

如果尚未推斷,請將 Sizes 欄的類型設為物件:

settype col: Sizes type: 'Object'

打散巢狀資料成為獨立欄位。下列範例會將 Sizes_ 加到新產生的資料欄名稱之前。

unnest col:Sizes keys:'Small','Medium','Large','Extra-Large' markLineage:true

您會發現在上述轉換指令中加入 pluck:true 的效果相當好,加入這個指令後,並非處於巢狀結構的值會從來源移除,只留下尚未處理的值:

unnest col:Sizes keys:'Small','Medium','Large','Extra-Large' markLineage:true pluck:true

如果已經處理完所有的值,則 Sizes 資料欄就會含有一組遺漏資料的對應。您可以使用下列指令,判斷剩餘資料的長度是否大於兩個字元以上。單是用來預覽就是個相當好用的轉換:

derive type:single value:(LEN(Sizes) > 2) as:'len_Sizes'

您可以刪除來源資料欄:

drop col:Sizes

結果:

完成後,資料集應該會如下所示:

ProdIdProdNameSizes_SmallSizes_MediumSizes_LargeSizes_Extra-Large
1001HatNYYY
1002ShirtNYYN
1003PantsYYYN

範例 - 打散巢狀陣列

下列範例會展現 unnestflatten 轉換間的相異之處,包含如何使用 unnest 根據指定鍵值整併陣列資料。

來源:

假設您有下列的學生測驗成績資料,每位學生的分數都儲存在 Scores 陣列中。每個資料列皆具不重複 ID,您必須能於每列上追蹤每回測驗。這個範例有兩個目標:

  1. 每個學生測驗成績皆以單個資料列顯示
  2. 為每個學生成績組合建立不重複的 ID
LastNameFirstNameScores
AdamsAllen[81,87,83,79]
BurnsBonnie[98,94,92,85]
CannonCharles[88,81,85,78]

轉換:

匯入 CSV 格式的資料時,您必須新增 header 轉換指令並將 Scores 資料欄中的引號移除:

header

replace col:Scores with:'' on:`"` global:true

驗證測驗日期:在開始作業前,建議您先檢查每位學生的測驗成績筆數是否正確。接著,您可以使用下列轉換指令來計算 Scores 陣列中的應有元素數 (4 個) 與實際筆數之間的差異:

derive type:single value: (4 - ARRAYLEN(Scores)) as: 'numMissingTests'

預覽轉換結果時,您應該會發現範例資料集中包含所有測驗成績。不過,您不一定會希望最終資料集包含這個資料欄,因為如果這個方案的執行規模比較大,您可能會發現自己遺漏了部分測驗成績。

不重複的資料列 ID:Scores 陣列必須拆分為不同的資料列,以便呈現每一次的測驗成績。不過,系統不會為資料列建立不重複的 ID,因此您無法追蹤個別測驗的成績資料。理論上,您可以使用 LastName-FirstName-Scores 的資料值組合來追蹤測驗成績,不過如果學生有兩次相同的成績記錄,資料集中就會出現重複的資料列。下列轉換指令會建立名為 Tests 的平行陣列,當中包含呈現 Scores 欄資料值數量的索引陣列。索引值會從 0 開始:

derive type:single value:RANGE(0,ARRAYLEN(Scores)) as:'Tests'

此外,您也可以使用 SOURCEROWNUMBER 函式來建立來源資料列 ID:

derive type:single value:SOURCEROWNUMBER() as:'orderIndex'

每個學生測驗成績皆以單個資料列顯示:您的資料應該會如下所示:

LastNameFirstNameScoresTestsorderIndex
AdamsAllen[81,87,83,79][0,1,2,3]2
BurnsBonnie[98,94,92,85][0,1,2,3]3
CannonCharles[88,81,85,78][0,1,2,3]4

接著,請使用 ARRAYZIP 函式將 TestsScores 陣列合併為一個巢狀陣列:

derive type:single value:ARRAYZIP([Tests,Scores])

您的資料集變更如下:

LastNameFirstNameScoresTestsorderIndexcolumn1
AdamsAllen[81,87,83,79][0,1,2,3]2[[0,81],[1,87],[2,83],[3,79]]
AdamsBonnie[98,94,92,85][0,1,2,3]3[[0,98],[1,94],[2,92],[3,85]]
CannonCharles[88,81,85,78][0,1,2,3]4[[0,88],[1,81],[2,85],[3,78]]

只要使用 flatten 轉換指令,您就能將這個巢狀陣列拆分:

flatten col: column1

每個「測驗-成績」組合現已拆分為不同的資料列,您必須使用 unnest 指令將巢狀「測驗-成績」組合拆分為不同的資料欄:

unnest col:column1 keys:'[0]','[1]'

您捨棄不再需要的 column1 之後,請為系統產生的這兩個資料欄重新命名:

rename mapping:[column_0,'TestNum']

rename mapping:[column_1,'TestScore']

不重複的資料列 ID:只要再執行一個步驟,您就能建立不重複的測驗 ID,藉此識別每位學生的特定測驗成績。下列指令會使用原始資料列 ID OrderIndex 作為學生 ID,並使用 TestNumber 值來建立 TestId 資料欄的值:

derive type:single value: (orderIndex * 10) + TestNum as: 'TestId'

上述資料值均為整數。如果您想讓 ID 好看一點,可以新增下列指令:

merge col:'TestId00','TestId'


延伸:
您也可以針對這個資料集產生一些摘要統計資訊。舉例來說,您可能會想計算每位學生的測驗平均成績。若要進行這個步驟,您必須瞭解如何將測驗資料值妥善分組。以本範例為例,此方案的執行規模若比較大,學生名字之間可能會產生衝突,使得您無法依據 LastName 資料值進行分組,因此您可能需要使用下列指令來建立主鍵:

merge col:'LastName','FirstName' with:'-' as:'studentId'

您現在可以使用這個結果作為計算成績的分組參數:

derive type:single value:AVERAGE(TestScore) group:studentId as:'avg_TestScore'

結果:

捨棄不需要的資料欄並移動資料欄位置後,您應該會看到下面這樣的資料集:

TestIdLastNameFirstNameTestNumTestScorestudentIdavg_TestScore
TestId0021AdamsAllen081Adams-Allen82.5
TestId0022AdamsAllen187Adams-Allen82.5
TestId0023AdamsAllen283Adams-Allen82.5
TestId0024AdamsAllen379Adams-Allen82.5
TestId0031AdamsBonnie098Adams-Bonnie92.25
TestId0032AdamsBonnie194Adams-Bonnie92.25
TestId0033AdamsBonnie292Adams-Bonnie92.25
TestId0034AdamsBonnie385Adams-Bonnie92.25
TestId0041CannonChris088Cannon-Chris83
TestId0042CannonChris181Cannon-Chris83
TestId0043CannonChris285Cannon-Chris83
TestId0044CannonChris378Cannon-Chris83

範例 - 從汽車資料中擷取鍵值值,接著將巢狀欄位打散為個別的資料欄

這個範例會示範如何使用以下轉換指令,將物件中的巢狀資料打散為個別欄位:

  • extractkv:從來源字串中移除鍵/值組合,請參閱 Extract 轉換
  • unnest:在獨立的資料列和資料欄中拆分巢狀資料,請參閱 Unnest 轉換

來源:

您有以下的二手車相關資訊。VIN 資料欄含有車輛 ID,而 Properties 資料欄則包含描述每部車輛特性的鍵/值組合。您想要將此資料拆分成不同的資料欄。

VINProperties
XX3 JT4522year=2004,make=Subaru,model=Impreza,color=green,mileage=125422,cost=3199
HT4 UJ9122year=2006,make=VW,model=Passat,color=silver,mileage=102941,cost=4599
KC2 WZ9231year=2009,make=GMC,model=Yukon,color=black,mileage=68213,cost=12899
LL8 UH4921year=2011,make=BMW,model=328i,color=brown,mileage=57212,cost=16999

轉換:

請新增下列轉換指令,以識別資料欄中所有開頭為字母字元的鍵值。

  • valueafter 字串標示對應值在鍵後的開始位置。
  • delimiter 字串表示每個鍵/值組合的結尾。

extractkv col:Properties key:`{alpha}+` valueafter:`=` delimiter:`,`

現在已建立上述值的物件,您可以使用 unnest 轉換拆分這個對應的資料。以下步驟指定了每個鍵,可以在結果中讓系統使用鍵的名稱做為各個資料欄的標題:

unnest col:extractkv_Properties keys:'year','make','model','color','mileage','cost'

結果:

捨棄不必要的 Properties 資料欄後,您應該會看到下面這樣的資料集:

VINyearmakemodelcolormileagecost
XX3 JT45222004SubaruImprezagreen1254223199
HT4 UJ91222006VWPassatsilver1029414599
KC2 WZ92312009GMCYukonblack6821312899
LL8 UH49212011BMW328ibrown5721216999

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
Google Cloud Dataprep 說明文件
需要協助嗎?請前往我們的支援網頁