Azure Stream Processing upsert to DocumentDB with array

Question

I'm using Azure Stream Analytics to copy my Json over to DocumentDB using upsert to overwrite the document with the latest data. This is great for my base data, but I would love to be able to append the list data, as unfortunately I can only send one list item at a time.

In the example below, the document is matched on id, and all items are updated, but I would like the "myList" array to keep growing with the "myList" data from each document (with the same id). Is this possible? Is there any other way to use Stream Analytics to update this list in the document?

I'd rather steer clear of using a tumbling window if possible, but is that an option that would work?

Sample documents:

{
    "id": "1234",
    "otherData": "example",
    "myList": [{"listitem": 1}]
}

{
    "id": "1234",
    "otherData": "example 2",
    "myList": [{"listitem": 2}]
}

Desired output:

{
    "id": "1234",
    "otherData": "example 2",
    "myList": [{"listitem": 1}, {"listitem": 2}]
}

My current query:

SELECT id, otherData, myList INTO [myoutput] FROM [myinput]

Why are you using Stream Analytics for this? Your simple SELECT * type query isn't really taking advantage of the real power of Stream Analytics. I wonder if it is the wrong tool for the job. Where is the data coming from before it gets to Stream Analytics? — GregGalloway
I'm also doing aggregation on the last hour of data elsewhere in the query, this was just the relevant portion for my question. — Jason

chetangm chetangm · Accepted Answer · 2016-04-13T17:47:27

Currently arrays are not merged, this is the existing behavior of DocumentDB output from ASA, also mentioned in this article. I doubt using a tumbling window would help here.

Note that changes in the values of array properties in your JSON document result in the entire array getting overwritten, i.e. the array is not merged.

You could transform the input that is coming as an array (myList) into a dictionary using GetArrayElements function .

Your query might look something like --

SELECT i.id , i.otherData, listItemFromArray INTO myoutput FROM myinput i CROSS APPLY GetArrayElements(i.myList) AS listItemFromArray

cheers!

Azure Stream Processing upsert to DocumentDB with array

1 Answers