unpivot columns into two new columns in Stream Analytics

Question

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

Answer 1

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