How to flatten dynamic Json in ADF/Synapse

Rashmi Shetty 6 Reputation points
2021-10-02T02:06:35.697+00:00

I have a issue where I have dynamic json and enabled schema drift in my source, To flatten the json I am coding it as array(byName('Columnname')) in unroll by dynamic content section. Here columnname is the array which need to be unrolled. I am getting this error -

{"message":"Job failed due to reason: at : scala.MatchError: array(byName('Columname')) (of class com.microsoft.dataflow.FunctionNode). Details:scala.MatchError: array(byName('Columname')) (of class com.microsoft.dataflow.FunctionNode)\n\tat com.microsoft.dataflow.transformers.FoldDownUtils$.unstackUnrollByRecurse(FoldDown.scala:79)\n\tat

I have tried byNames(['Columnname']) as well and array(columnNames()[7]) nothing seems to work.

Kindly help as to how to unroll array of dynamic json with schema drift option enabled at source

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,667 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,120 questions
{count} vote

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,326 Reputation points Microsoft Employee
    2021-10-04T09:04:05.45+00:00

    Hi @Rashmi Shetty ,

    Thank you for posting your query on Microsoft Q&A Platform.

    Flatten transformation is useful to flatten array values to rows.

    You should consider passing your column name which is array type in to unroll section either manually or using dynamic expression.

    Your expression has array() and byName() function. array() function will build array from passed values in it. So if your intention is to build array first by suppling some values to it, then kindly use derived column transformation and build your array column there and then pass that array column in to unroll section either manually or using byName() function dynamically.

    Hope this helps. Please let us know how it goes. It would be great if you can share detailed implementation with screenshots to repro issue and help better. Thank you.