'Update Cosmos DB collection with complex type using Azure Data Factory Dataflow

I have a document structured blow

    [
        {
            "complexType": [
                {
                    "ComplexField1": "data",
                    "ComplexField2": "data",
                    ...
                },
                {...}
            ],
            "id": "id1",
            "rootField1" : "data",
            "rootField2" : 999
        },
        {...}
    ]

What I am trying to do is using a ADF dataflow to update the fields at root (e.g. rootField1, rootFiled2) and leave everything else as is with following steps:

  1. Load collection from Cosmos DB
  2. Lookup rootField1, rootField2 data from another source
  3. Update collection back to Cosmos DB

The problem is at the sink step it will throw a error

    Conversion from ArrayType(StructType(StructField(rootField1,StringType,true), StructField(rootField2,ShortType,true)),false) to ArrayType(StringType,true) not defined

I have tested this error will go away if I remove all complex types from the mapping, but that's mean it will remove all complex type form the document, would like to know how I can update the document without remove complex type?

Other methods tested

1. stringify complexType nodes

Failed as this become a string and no longer a complex type node

2. use derived column to break it into column and sub column

Failed it will throw error when there are numeric items, and changed the structure from

            "complexType": [
                {
                    "ComplexField1": "data",
                    "ComplexField2": "data",
                    ...
                },
                {...}
            ],

to

            "complexType": {
                "ComplexField1": ["data1", "data2", ...],
                "ComplexField2": ["data1", "data2", ...],
            },


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source