ADF push json data to SQL

Question

I have an adf pipeline to fetch data from API and store it to blob storage as json. Next I fetch the same json data from blob using lookup. Using foreach and filter I get some particular content inside the json filtered. Now I need to insert this filtered data to DB. Is there a way to copy the filtered data to DB?

Answer 1

Update:

It is the same process if we input json array. As follows show:

My input is [{\"customerId\":100001,\"cutomerName\":\"Tom\"},{\"customerId\":100002,\"cutomerName\":\"John\"},{\"customerId\":100003,\"cutomerName\":\"Tinny\"}], I can see 3 records in my Azure SQL.

---

Here I'm using Lookup activity to get one json object and copy it into azure SQL via Stored procedure activity.

1. This is my json type output of my lookup activity.

    "firstRow": {
        "customerId": 100001,
        "cutomerName": "Tom"
    }

2. In azure sql, we can create a table and a stored procudure.

--1. create table
create table dbo.CustomerInfo(
customerId INT,
cutomerName varchar(50)
)

--2. create stored procedure
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[uspCustomerInfo] @json NVARCHAR(MAX)
AS 
begin
    INSERT INTO dbo.CustomerInfo(customerId,cutomerName) 
        SELECT customerId,cutomerName
        FROM OPENJSON(@json,N'$')
            WITH (
            customerId INT N'$.customerId',
            cutomerName VARCHAR(255) N'$.cutomerName'
            );
end

3. In stored procedure activity setting, we should convert json to string via @string(activity('Lookup1').output.firstRow).

4. This is my input of the stored procedure activity.

5. This is my debug result:
   

6. If you are facing more complex json object you can refer this doc.