Azure Data Factory Parse string array of Json to array of json

Question

Im trying to copy data from SQL Server to CosmosDb.

I have created a DataFlow to apply transformations and set the proper json schema for the items in the CosmosDb container.

The SQL Server data looks like the following:

Id	Name	Array
1	Name1	[{"property1":"value1","property2":"value2"},{"property1":"value1"}]
2	Name2	[{"property1":"value1","property2":"value2"},{"property1":"value1"}]

The CosmosDb schema should be like the following:

{
   "id" : "1",
   "name" : "Name1",
   "array" : [
      {
         "property1":"value1",
         "property2":"value2"
      },
      {
         "property1":"value1"
      }
   ]
}

I've used the Derived Column and Parse activities in the Data Flow, applying several functions in the expression builder, but I can't find a proper solution for it.

Is there a straight forward way to parse the string array of jsons value in to an actual array of jsons without manipulating the string, for example using split or replace, in Azure Data Factory?

Answer 1

I could find a solution by myself. The steps are the following:

1.- Add a Parse activity

2.- Under Parse Settings select JSON format if not selected and under JSON settings select Array of documents

3.- Finally in the Output colum type to define a complex schema, you should define your schema like this: