我有一個來自 IoT 中心的流，例如：
{
"store_id": "111",
"data": [
{
"timestamp": "2018-04-06T11:46:11.842305",
"book_id": "001",
"author_id": "101"
},
{
"timestamp": "2018-04-06T11:46:11.842306",
"book_id": "002",
"author_id": "102"
},
{
"timestamp": "2018-04-06T11:46:11.842307",
"book_id": "003",
"author_id": "103"
}
]
}
我想在這樣的 SQL DB 中傳遞這個流：
id id_type timestamp
001 book_id 2018-04-06T11:46:11.842305
101 author_id 2018-04-06T11:46:11.842305
002 book_id 2018-04-06T11:46:11.842306
102 author_id 2018-04-06T11:46:11.842306
003 book_id 2018-04-06T11:46:11.842307
103 author_id 2018-04-06T11:46:11.842307
有什么方法可以使用cross apply或以其他方式從多 json 元素中創建兩個新列
uj5u.com熱心網友回復：
如果它是一個靜態資料透視表（您事先知道欄位串列并且您可以對其值進行硬編碼），那么您可以使用以下內容：
WITH Unfolding AS (
SELECT
d.ArrayValue.*
FROM input i
CROSS APPLY GetArrayElements(i.data) d
),
Books AS (
SELECT
timestamp,
book_id as id,
'book' as id_type
FROM Unfolding
),
Authors AS (
SELECT
timestamp,
author_id as id,
'author' as id_type
FROM Unfolding
),
AllRecords AS (
SELECT timestamp, id, id_type FROM Books
UNION
SELECT timestamp, id, id_type FROM Authors
)
SELECT
*
INTO output
FROM AllRecords
您為每個物體創建一個CTE以在最后提取和聯合它們。
如果您有動態值，則需要使用JavaScript UDF。遺憾的是，我沒有代碼示例。它應該很簡單（但有點痛苦）。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/388532.html