CODEWITHSUNDEEP
azureazure-sql-databaseazure-data-factory
Jesse
Jesse

Reputation: 41

Can we parse and copy json array into multiple sql tables via ADF

I want to use azure data factory parse some json data and copy this into a azure sql database.

The customer data, which looks like this:

    {
      "customerId": 125488,
      "firstName": "John",
      "lastName": "Smith",
      "age": 25,
      "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
      },
      "phoneNumber": [
        {
          "type": "home",
          "number": "212 555-1234"
        },
        {
          "type": "fax",
          "number": "646 555-4567"
        }
      ]
    }

My goal is to put customerId, firstName, lastNameand age into a customers table, like the one shown below.

    create table dbo.customer (
        customerId int,
        firstName varchar(255),
        lastName varchar(255),
        age int
    )

This part I have already done using the copy data (api to sql). My next goal is to put the phone numbers into a phone number table, like the one shown below.

    create table dbo.phonenumber (
        customerId int,
        phoneNumber varchar(255)
        phoneType varchar(255)
    )

I am using the copy activity in a pipeline to move the customer data into the customer table, but I cannot do multiple outputs writing to different tables. Can we do that in one pipeline?

Upvotes: 1

Views: 455

Answers (1)

Joseph Xu
Joseph Xu

Reputation: 6043

I think you can use stored procedure in copy activity to copy the data into serveral tables.
I created a simple test as follows:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[uspCustomer] @json NVARCHAR(MAX)
AS  

BEGIN 
    INSERT INTO dbo.customer(customerId,firstName,lastName,age) 
        SELECT customerId,firstName,lastName,age 
        FROM OPENJSON(@json)
          WITH (
            customerId INT '$.customerId',
            firstName VARCHAR(255) '$.firstName',
            lastName VARCHAR(255) '$.lastName',
            age INT '$.age'
          );
    INSERT INTO dbo.phonenumber(customerId,phoneNumber,phoneType)
        SELECT customerId,phoneNumber,phoneType
        FROM OPENJSON(@json)
          WITH (
            customerId INT '$.customerId',
            phoneNumber VARCHAR(255) '$.phoneNumber[0].number',
            phoneType VARCHAR(255) '$.phoneNumber[0].type'
          );
    INSERT INTO dbo.phonenumber(customerId,phoneNumber,phoneType)
        SELECT customerId,phoneNumber,phoneType
        FROM OPENJSON(@json)
            WITH (
            customerId INT '$.customerId',
            phoneNumber VARCHAR(255) '$.phoneNumber[1].number',
            phoneType VARCHAR(255) '$.phoneNumber[1].type'
            );

END

The following is a test of the stored procedure.

DECLARE @json NVARCHAR(MAX);
SET @json = '{"customerId": 125488,"firstName": "John","lastName": "Smith","age": 25,"address": {"streetAddress": "21 2nd Street","city": "New York","state": "NY","postalCode": "10021"},"phoneNumber":[{"type": "home","number": "212 555-1234"},{"type": "fax","number": "646 555-4567"}]};'

exec [dbo].[uspCustomer] @json

The result is as follows:
enter image description here

That's all.

Upvotes: 1

Related Questions