Loop Cosmos DB documents using ADF lookup and foreach

Question

We have more than 5000 documents in the Cosmos DB container. The requirement is to process all of them one by one and import them into another Cosmos DB container.

I tried the ADF lookup and Foreach activities to loop the documents and process. Since the ADF lookup activity is limited to fetching only 5000 documents, we want to loop remaining with OFFSET and LIMIT options.

However Cosmos DB does not support OFFSET and LIMIT, getting below error.

ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property '' is invalid: 'Cross Partition OFFSET / LIMIT is not supported.'.,Source=,''Type=System.ArgumentException,Message=Cross Partition OFFSET / LIMIT is not supported.,Source=Microsoft.Azure.Documents.Client,'

Can anyone suggest how to achieve the above requirement? We do not have any sequence number column as well to loop.

Answer 1

I do agree with @David Makogon's comment that looping through all documents might not be a better option in this case. To process each document or filter some documents based on a condition in the loop, you can try ADF dataflow as a workaround.

Take the Cosmos SQL source container dataset as source and target dataset as sink in the dataflow.

In the dataflow, you can use derived column or filter transformations to modify any field in each document or to filter the documents based on given condition as shown in below demo.

Sample expression: concat(name,'_new string')

You can also remove any Key properties from the source using select transformation.

Add these transformations in between the source and sink.

Here, you can specify whether to Recreate the target container or not. Also, you can specify the Partition key name for the target container from source document properties.

Use a Dataflow activity in the pipeline to run this dataflow from the pipeline. Upon running the dataflow, it will copy all source documents from source to target container by following the given modifications or conditions in the transformations like below sample.