Bulk import data to Cosmos throws BadRequest error

Question

Hi,

I am using a logic app to get data from an API, make some transformations then insert it into Cosmos.

then inserting the data into Cosmos, a couple of records/items gets inserted and then it throws an error : "The service provider action failed with error code 'BadRequest' and error message 'One or more errors occurred. (Encountered an unexpected JSON token.\r\nActivityId: 91f149f6-d2db-4bb7-a0f5-43210b6d2ec3, Windows/10.0.14393 cosmos-netstandard-sdk/3.23.1)"

Tried solutions,

1- JSON is valid

2- inserting all the records as is without transformation(same number of records just different schema) (worked)

3- added a compose step with the record where it stopped, to make sure that the issue is not from the record's data (worked)

Answer 1

Hi Rashid Hanan,

Welcome to Microsoft Q&A forum.

As I understand, you are getting Encountered an unexpected JSON token when inserting transformed records into Azure Cosmos DB.

This looks like token issue which is described here: https://stackoverflow.com/questions/66995110/azure-cosmos-pagination-feedresponse-continuationtoken-not-working-in-the-next

Please take a look and make changes accordingly in your request.

Also, check what sort of transformation you are making on the data which might be making over limits like maximum size of an item should be within 2MB.

Refer for other limits: https://learn.microsoft.com/en-us/azure/cosmos-db/concepts-limits

Hope this helps. Thanks

Answer 2

Based on the error message you're encountering with Azure Logic Apps and Azure Cosmos DB, it seems like the issue is related to the JSON processing or the data schema used for insertion. Since you've confirmed that the JSON is valid and that inserting records without transformation works, the problem likely lies in the transformation step. Here are some steps to further troubleshoot and resolve this issue:

Troubleshooting Steps

1. Inspect Transformation Logic:
   • Ensure that the transformation logic in your Logic App is not altering the JSON structure in a way that Cosmos DB cannot interpret.
   • Verify that the transformed JSON matches the schema expected by Cosmos DB.
2. Log Transformed JSON:
   • Add an action in your Logic App to log the transformed JSON output just before it's sent to Cosmos DB.
   • Inspect the logged JSON for any anomalies or differences from the expected schema.
3. Schema Validation:
   • Use a JSON schema validation tool to ensure the transformed JSON adheres to the schema expected by Cosmos DB.
4. Incremental Inserts:
   • Try inserting smaller batches of records to identify if there's a specific record causing the issue.
   • If a specific record is identified, closely inspect its data for any anomalies.
5. Check Cosmos DB Settings:
   • Ensure that the Cosmos DB container and database settings (like indexing policies, partition keys, etc.) are compatible with the data you're inserting.
6. Error Handling in Logic App:
   • Implement error handling in your Logic App to catch and log detailed error messages, which might provide more insights.
7. Use Azure Monitor:
   • Leverage Azure Monitor to trace the operations and identify where exactly the failure occurs.

Code Example

In your Logic App, consider adding a step to log the JSON payload. This can be done by using an Azure Function or sending the data to Azure Monitor. For example:

{
    "type": "Function",
    "inputs": {
        "jsonPayload": "@outputs('Transform_Json')"
    }
}

This function call can log the output of your transformation step, allowing you to inspect it for issues.

Additional Considerations

• Rate Limits and Throughput: Ensure that you're not hitting any rate limits or throughput constraints on the Cosmos DB side.
• Logic App Limits: Check if there are any limitations or timeouts in Logic App that might affect the operation.

By following these steps, you should be able to pinpoint the issue more accurately. Remember, the error message suggests that the issue is with the JSON token, so focus on how the data is transformed and represented in JSON format.

Accept the answer if the information helped you. This will help us and others in the community as well.