JSON Array Object Mapping in ADF

Tariq Jamal 1 Reputation point
2021-09-14T22:06:50.127+00:00

We currently have an API end point that we call and it returns JSON on items and how they are ranked on Amazon. The JSON includes a lot of data, but the most important elements are below.

[
  {
    "timestamp": 1631656500626,
    "products": [
      {
        "title": "This is the very long title of the product",
        "parentAsin": "B086PDNJ98",
        "asin": "B0000D8999",
        ],        
        "salesRankReference": 1055398,
        "salesRanks": {
          "3734871": [
            4828978,
            113,
            4829096,
            124,
            4829320,
            153,
            4829484,
            136,
            4829588,
            106,            
          ]
        }                
      }
    ]
  }
]

The problem is that the sales rank listing can be thousands of listings. We would like to map this to a single field in a table and then break it out using stored procedures. When trying to map this field to an NVARCHAR(MAX) field we get an error about the field type not matching.

The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.,Source=System.Data,''Type=System.InvalidOperationException,Message=String or binary data would be truncated.,Source=System.Data,'

Not sure if this is just out of the depth of what ADF can handle.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,911 questions
{count} votes