Storing dictionary from API into Table

Question

I'm querying an API using Azure Data Factory and the data I receive from the API looks like this.

{ "96":"29/09/2022", "95":"31/08/2022", "93":"31/07/2022" )

When I come to write this data to a table, ADF assumes the column names are the numbers and the dates are stored as rows like this

96	95	93
29/09/2022	31/08/2022	31/07/2022

when i would like it to look like this

Date	ID
29/09/2022	96
31/08/2022	95
31/07/2022	93

Does any one have any suggestions on how to handle this, I ideally want to avoid using USP's and dynamic SQL. I really only need the ID for the month of the previous one we're in.

PS - API doesn't support any filtering on this object

Updates

I'm querying the API using a web activity and if i try to store the data to an Array variable the activity fails as the output is an object.

When I use a copy data activity I've set the sink to automatically create the table and the mapping looks likes this

mapping image

Thanks

Saideep Arikontham · Answer

Instead of directly trying to copy the JSON response to SQL table, convert it the response to a string, extract the required values and insert them into the SQL table.

Look at the following demonstration. I have taken the sample response provided as a parameter (object type). I used set variable activity for extracting the values.

My parameter:

{"93":"31/07/2022","95":"31/08/2022","96":"29/09/2022"}

Dynamic content used in set variable activity:

@split(replace(replace(replace(replace(string(pipeline().parameters.api_op),' ',''),'"',''),'{',''),'}',''),',')

The output for set variable activity will be:

enter image description here

Now inside For each activity (pass the previous variable value as items value in for each), I used copy data to copy each row separately to my sink table (Auto create option enabled). I have taken a sample json file as my source (We are going to ignore all the columns anyway.)
Create the required 2 additional columns called id and date with the following dynamic content:

#id
@split(item(),':')[0]

#date
@split(item(),':')[1]

enter image description here

Configure the sink. Select the database, create dataset, give a name for table (I have given dbo.opTable) and select Auto create table under sink settings.
The following is an image of mapping. Delete the column mappings which are not required and only use additional columns created above.

enter image description here

When I debug the pipeline, it will run successfully, and the required values are inserted into the table. The following is output sink table for reference.

Storing dictionary from API into Table

1 Answers