I have a stream from IoT Hub like:
{
"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"
}
]
}
I want to pass this stream in a SQL DB like this:
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
is there any way to use cross apply
or other way to create two new columns form multi json element
CodePudding user response:
If it's a static pivot (you know in advance the list of fields and you can hardcode their value), then you get there with something like this:
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
You create one CTE per entities to extract and UNION them all at the end.
If you have dynamic values, you will need to use a JavaScript UDF. I don't have a code sample sadly. It should be straightforward (but a bit painful).