Parsing JSON in Azure DataFactory

Question

I want to use azure data factory to call an api, parse some json data, and save this into a azure sql database.

There is no api which returns all the customer data in one go so this can only be done one customer at a time. This can not be changed. I have a customer api which returns a basic list of customer numbers, for example:

{
    "customerId": 100001,
    "customerId": 100002,
    "customerId": 100003,
    "customerId": 100004,
    "customerId": 100005,
    "customerId": 100006,
    "customerId": 100007,
}

I am using a http api connection in data factory to retrieve this list, then using a for each loop to go through them one by one and triggering another pipeline. This other pipeline will go and get 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"
    }
  ],
  "history": [
    {
      "description": "registered",
      "date": "2021-01-011T01:45:53.5736067Z"
    },
    {
      "description": "verificationRequired",
      "date": "2021-01-011T01:49:53.5736067Z"
    },
    {
      "description": "verified",
      "date": "2021-01-011T01:56:53.5736067Z"
    }
  ]
}

My goal is to put customerid, firstname, lastname and age into a customers table, like the one shown below.

create table 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 phonenumber (
    customerId int,
    phoneNumber varchar(255)
    phoneType varchar(255)
)

I also want to repeat this for customer history too.

I am using the copy data task in a pipeline to move the customer data into the customer table, but I cannot do multiple outputs writing to different tables. The only way I can think to do this is having three different pipelines for the three different tables, but this means calling the api three different times for the same data. There must be a better way?

Value your help Peace, Amjid

Hi @jxlg4, If my answer is helpful for you, please accept(mark) it as answer. This can be beneficial to other community members. — Joseph Xu

Joseph Xu Joseph Xu · Accepted Answer · 2021-02-26T07:56:09

I think you can create a stored procedure to move the customer data into serveral tables.
My example is 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:

I think you can use a stored procedure activity to accept this json Object.

Parsing JSON in Azure DataFactory

1 Answers