How to deal with Array type columns in data flow working with REST API

Question

Hi,

I'm trying to retrieve data from a REST API into an SQL database. I'm using a dataflow to retrieve the data > flatten > sink.

It's working exactly how I want, but I'm having trouble with a few columns because they are Array type (bottom 5 rows on my image). I've tried changing the data type in the sink (SQL table) but this doesn't solve the problem.

Changing the data type in the source is no option. I thus am looking for a way to change the data type so that I can export it to my SQL database without errors.

Hopefully there is an easy solution for this, as I really need these columns :-).

When I add a second stream in my data flow, I can unroll by subscription_items and it seems to work. However, I want the results to end up in 1 table. I don't know if this is the right solution (making separate data streams in my flow)?

Answer 1

@Ruben Betjes Thanks for using this forum and raising your issue and glad to know that you were able to figure it out.

As you rightly figured out, flatten transformation has option to unroll more than one array column by using the + sign. This is being called out in the public documentation as well.

Doc ref: ADF Flatten transformation - Unroll by

Just wanted to share this additional information so that others will get benefitted. Feel free to open a new thread if you have any further questions.

Thank you & have a good day!

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Fixed it by adding another column to unroll by :-)

This is solved!