CODEWITHSUNDEEP
jsonsql-serverazureazure-data-factory
SoakingHummer
SoakingHummer

Reputation: 572

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

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.

Upvotes: 0

Views: 1623

Answers (1)

Steve Johnson
Steve Johnson

Reputation: 8690

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
;

Upvotes: 2

Related Questions