How to map a nested JSON objects to a SQL Server table using Azure Data Factory pipelines

Question

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.

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. — AlwaysLearning
@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. — SoakingHummer

Steve Zhao Steve Zhao · Accepted Answer · 2021-04-22T03:29:54

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:

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
;

How to map a nested JSON objects to a SQL Server table using Azure Data Factory pipelines

1 Answers