Facing problem to understand repeat element and sub repeat element while pulling the data from REST API. Any document which may help in this?

Matillion ETL
21

It worked!

 

Thank you for the patience and the help.

 

I am now facing a new challenge, is there a way to flatten if there are more than 2 sub repeating elements?

 

Thanks,

Ryan

22

Unfortunately, the functionality stops at the 2nd level. So, if you want to flatten it at multiple levels, you would need to either bring it in as a whole and use Python or Snowflake to pull it apart.

23

Thanks for bringing that up @Bodo​! I am a bit guilty of making assumptions at times. 😏

You can also set the BackwardsCompatibility mode in the RSD itself. This would allow you to get around having to specify it in the connection options. Here is an example of where I set it in the RSD:

0694G00000J6Kp4QAF_0D74G000007kcGbSAI
0694G00000J6Kp4QAF_0D74G000007kcGbSAI1185×506 129 KB

24

Thanks for the help, I'll go for the Python option then. It's a pity, though, as for a simple JSON the API Query/Query profileis a nice and clean solution.

25

Hi Bryan, how did you set up the API Profile that returned a full JSON response which can also be accessed by API Query component? Can API Query component access API Extract profile?

 

I want to get the full JSON response and dynamically pass parameters values using metadata table from Snowflake. I can't achieve the both with either API Query or API Extract.

 

Can you also share with me how you did it using Python script? Many thanks

26

Hi @CP872896​, I spoke on using API Profiles to get raw JSON. I went back and found 2 places where I was doing that. One of them I was using an API Extract Profile and the other I used Python because I didn't want to load the results into a table but rather load them into a variable for other operations.

You should be able to get parameters from your table and pass them in as parameters in your API call. It's not direct though. You will first need to read the table and get the parameters and assign them to a variable. This would be accomplished by using the Query result to Scalar component. Once you have the variables loaded from your table, you can then call the API Extract and pass in the parameters using the URI, Query, or Header parameters. Hopefully this helps. The only downfall to the API Extract component is that you have to load the results into a table but it works pretty well. Hopefully this helps!