查询/压平雪花中嵌套Json中数组内的所有元素

Question

我是雪花的新手。我试图查询/压平嵌套Json中的所有字段，但在Transactio数组中，我有多个事务，无法提取所有事务，只能使用索引选择第一个。

我的json结构是这样的

[
{
"Employee": {
"UID": "value",
"Transactions": [
{
"ORBIS-Tx-ID": "123F",
"ReferenceID": {
"Amount": {
"Charge": 0.3,
"Currency": "USD",
"StatusEntry": {
"Status": "COMPLETED",
"Timestamp": {
"$date": "2021-09-07T11:58:40.672Z"
}
},
"SummaryStatus": "COMPLETED",
"Value": 9.7
},
"Metadata": {
"Notes": "Salary advance paid out",
"TxSubType": {
"From": {
"OrbisUID": "OPA",
"ProviderID": "gbQG0sTWZVmioQ"
},
"To": {
"UID": "59B7",
"ProviderID": "DnYOEs5A"
}
},
"TxType": "ADVANCE"
},
"OrigMessage": {
"EC": "0",
"SC": 200,
"approvalCode": "145382",
"card": {
"expirationDate": "202605",
"last4": "6423"
},
"fees": {
"interchange": "0.10",
"network": "0.01"
},
"network": "VisaFF",
"networkID": "5812198027",
"networkRC": "00",
},
"Provider": {
"ProviderID": "iuMVYeZLHQ",
"ProviderName": "Name"
}
}
},
{
"ORBIS-Tx-ID": "123F",
"ReferenceID": {
"Amount": {
"Charge": 0.45,
"Currency": "USD",
"StatusEntry": {
"Status": "COMPLETED",
"Timestamp": {
"$date": "2021-09-10T15:44:11.896Z"
}
},
"SummaryStatus": "COMPLETED",
"Value": 19.55
},
"Metadata": {
"Notes": "Salary advance paid out",
"TxSubType": {
"From": {
"OrbisUID": "ORA",
"ProviderID": “QIHVsTWZVmioQ"
},
"To": {
"UID": "59EB11-85A9-00155DC29747",
"ProviderID": "DngXSCgkYOEs5A"
}
},
"TxType": "ADVANCE"
},
"OrigMessage": {
"EC": "0",
"SC": 200,
"approvalCode": "164647",
"card": {
"expirationDate": "202605",
"last4": "6423"
},
"fees": {
"interchange": "0.10",
"network": "0.02",
},
"network": "VisaFF",
"networkID": "58112122",
"networkRC": "00"
},
"Provider": {
"ProviderID": "QjS0o4Mg",
"ProviderName": "Name"
}
}
}
}
}
]

我希望所有字段都在单独的列中，并且能够查询例如Transactions.ReferenceID.Aunt内的所有交易。现在我是这样变平的：

select 
value:Employer:UID as ER_ORBISUID,
value:Employee:UID EE_ORBISUID,
value:Employee:Transactions[0]['ORBIS-Tx-ID'] as TrasactionID,
array_size(value:Employee:Transactions) as arraySize,
value:Employee:Transactions[0].ReferenceID.Amount.Charge as AmtValue
,value:Employee:Transactions[0]:ReferenceID.Amount.StatusEntry.Status as Staus
from test11,
-- lateral flatten(input => CLMN) as aa,
lateral flatten(input => CLMN:Employee:Transactions);

当我运行上面的查询时，我只能获得数组中的第一个元素，因为我使用的是0索引，我不知道如何在不使用索引的情况下做到这一点。有人能帮忙吗？非常感谢。

Answer 1

您可以使用(LATERAL(FLATTEN函数从JSON数据中提取嵌套变量、对象或数组。

详细信息&示例：https://community.snowflake.com/s/article/Using-lateral-flatten-to-extract-data-from-JSON-internal-field