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?