How to map a json string into object type in sink transformation

Using Azure Data Factory and a data transformation flow. I have a csv that contains a column with a json object string, below an example including the header:

"Id","Name","Timestamp","Value","Metadata" "99c9347ab7c34733a4fe0623e1496ffd","data1","2021-03-18 05:53:00.0000000","0","{""unit"":""%""}"

Want to store the data in a json like this:

{      "id": "99c9347ab7c34733a4fe0623e1496ffd",      "name": "",      "values": [          {              "timestamp": "2021-03-18 05:53:00.0000000",              "value": "0",              "metadata": {                  "unit": "%"              }          }      ]  }  

The challenge is that metadata has dynamic content, meaning, that it will be always a json object but the content can vary. Therefore I cannot define a schema. Currently the column "metadata" on the sink schema is defined as object, but whenever I run the transformation I run into an exception:

Conversion from ArrayType(StructType(StructField(timestamp,StringType,false),   StructField(value,StringType,false), StructField(metadata,StringType,false)),true) to ArrayType(StructType(StructField(timestamp,StringType,true),   StructField(value,StringType,true), StructField(metadata,StructType(StructField(,StringType,true)),true)),false) not defined  

https://stackoverflow.com/questions/66687600/how-to-map-a-json-string-into-object-type-in-sink-transformation March 18, 2021 at 04:46PM