Copy json data from Azure cosmos db to Azure sql using Azure Data Factory

Question

I am using Azure Data Factory V1. We want to copy the json data stored as documents from Azure cosmos to a azure sql table, using a copy activity.

I figured out copying the data by specifying the columns in sql table to match the property names from json. However our goal is to copy the entire json data as a single field. We are doing this for the purpose of being agnostic to the schema within the json data.

I have tried specifying a single nvarchar(max) column to store the json data, and the query on the copy activity to be "select c as "FullData" from c". But the copy activity simply generates a NULL.

I think this is because "FullData" is of type json on the document end and it is string on the sql end. I also tried to convert the json object to string within the cosmos db query. But I couldnt find any API to do so.

I know we could write a custom activity to accomplish what I want to do, but is this possible to do with ADF out of the box functionality?

Answer 1

you can use the jsonPathDefinition as similar as this: "column_full": "$. "

Refer to this link on how to use jsonFormat with ADF: https://docs.microsoft.com/en-us/azure/data-factory/supported-file-formats-and-compression-codecs#json-format

Answer 2

To use ADF to copy the JSON document as single field into Azure SQL Database, you need to make sure the result set by the specified Cosmos DB query is actually a single "column" containing the entire object as string.

I don't think Cosmos DB has built-in query syntax for this, but you can create a UDF (user defined function) in Cosmos DB to convert object to string e.g. with JSON.stringify(), then call that UDF in the select query in ADF copy source.