0

I called a REST API and retrieved the result and placed it in Azure blob storage as a JSON file (all this using Copy data activity from Azure Data Factory).

{"success": "True",
 "timestamp": "1618498386",
 "base": "EUR",
 "date": "2021-04-15",
 "rates": {
  "AED": 4.395136,
  "AFN": 92.798516,
  "ALL": 123.01053,
  "AMD": 623.68381,
  "UAH": 33.449108,
  "UGX": 4330.311325,
  "USD": 1.196623}}

Now I would like to use the same pipeline to connect to a table in SQL Server with the following format.

base date CURR_KEY CURR_VALUE
EUR 2021-04-15 AED 4.395136
EUR 2021-04-15 AFN 92.798516
EUR 2021-04-15 ALL 123.01053
EUR 2021-04-15 AMD 623.68381
EUR 2021-04-15 UAH 33.449108
EUR 2021-04-15 UGX 4330.311325
EUR 2021-04-15 USD 1.196623

The challenge here is to parse the nested JSON object "rates" and place it in the table as shown above through ADF pipeline. Any ideas on how to solve this?

Update: I have posted a question recently asking how to parse the above JSON using a SQL query. The reason I posted this one is to look for answers which solve the aforementioned problem only by using ADF GUI instead of creating a stored procedure.

Improve this question
2
  • 3
    Instead of asking the same question again you should accept one of the correct answers to your question from five days ago, Place key-value pairs of a JSON object into separate columns. Commented Apr 21, 2021 at 21:48
  • @AlwaysLearning, the query which I was looking in the question you mentioned was commented by Iptr and I acknowledged and upvoted that comment (and would accept if he posts it as an answer) and none of the answers posted in that question worked exactly they way I wanted hence I didn't accept them. The reason I posted this question is to know if there is a way to parse this JSON file only by using the Azure Data Factory GUI instead of using the query. Commented Apr 22, 2021 at 7:40

1 Answer 1

Reset to default
2

First, create a Stored procedure in SQL Server. Then use Lookup activity in ADF to get the data of JSON file. Finally, create a Stored procedure activity and pass the Lookup activity's output as parameter.

Details:

enter image description here

enter image description here

Expression in Stored procedure activity:@{activity('Lookup1').output.value[0]}

Stored procedure code(use the answer from your previous question):

CREATE PROCEDURE [dbo].[uspTest] @json NVARCHAR(MAX)
AS  

 

BEGIN TRY 
        INSERT INTO dbo.dvalue(base,date,CURR_KEY,CURR_VALUE)
               SELECT
                    j.base,
                    j.date,
                    CAST(rates.[key] AS char(3)) as CURR_KEY,
                    CAST(rates.value AS decimal(15,6)) as CURR_VALUE
                FROM OPENJSON(@json)
                     WITH ([base] char(3),
                           [date] date,
                           rates nvarchar(MAX) AS JSON) j
                     CROSS APPLY OPENJSON(j.Rates) rates;
END TRY  
BEGIN CATCH 
    PRINT ERROR_MESSAGE ( )   
END CATCH
;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thank you for your answer, I think it would work only if I had access to create a stored procedure. Is there a way by which this would work without involving the process of creating a SP?
@SoakingHummer Yes, but it's performance will be worse. One way to do this is that cast your JSON data to your expected format and save them with csv format in Azure blob storage by Data Flow. Then copy that file to your SQL Server by Copy Data activity.
The performance is worse with data flow as you said and it is also costly. I tried creating two staging tables in the database, one for storing each of the JSON object into a column and the column with the JSON object containing the key-value pairs of exchange rates is used to populate the 2nd JSON table and parsing the JSON from 1st staging table with OPENJSON(). All this is done with just two copy data activities.
@SoakingHummer It's a good way to do this. You can post it as answer and mark it. I think it will benefit to others.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.