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
Advertisement
Answer
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).