cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Alexlorenzo21
Level: Powered On

Using Custom Connector to Get data and then Parse Json and enter into SQL

Hello,

 

I am having a really hard time figuring out how create this flow.

I want to parse the following json response below.

 

The end result I am trying to do is to enter the "employees" table data into a SQL server table.

 

The response of the GET API call is the following. 
 

{
  "title": "compensation date",
  "fields": [
    {
      "id": "employeeNumber",
      "type": "employee_number",
      "name": "Employee #"
    },
    {
      "id": "fullName2",
      "type": "text",
      "name": "Last Name, First Name"
    },
    {
      "id": "firstName",
      "type": "text",
      "name": "First Name"
    },
    {
      "id": "lastName",
      "type": "text",
      "name": "Last Name"
    },
    {
      "id": "payRateEffectiveDate",
      "type": "date",
      "name": "Compensation: Date"
    },
    {
      "id": "payType",
      "type": "pay_type",
      "name": "Pay type"
    },
    {
      "id": "payRate",
      "type": "currency",
      "name": "Pay rate"
    },
    {
      "id": "payPer",
      "type": "paid_per",
      "name": "Paid per"
    },
    {
      "id": "jobTitle",
      "type": "list",
      "name": "Job Title"
    },
    {
      "id": "department",
      "type": "list",
      "name": "Department"
    },
    {
      "id": "division",
      "type": "list",
      "name": "Division"
    }
  ],
  "employees": [
    {
      "id": "40525",
      "employeeNumber": "144216",
      "fullName2": "XXXX",
      "firstName": "XXXX",
      "lastName": "XXXX",
      "payRateEffectiveDate": "2015-01-01",
      "payType": "Salary",
      "payRate": " XXXX",
      "payPer": null,
      "jobTitle": "XXXXXXXX",
      "department": "XXXXXXXX",
      "division": "XXXXXXXX"
    },
        {
      "id": "40535",
      "employeeNumber": "153444",
      "fullName2": "XXXX, XXXX",
      "firstName": "XXXX",
      "lastName": "XXXX",
      "payRateEffectiveDate": "2016-11-01",
      "payType": "Hourly",
      "payRate": "19.50 USD",
      "payPer": "Hour",
      "jobTitle": "XXXX",
      "department": "XXXXXXXX",
      "division": "XXXXXXXX"
}
]
}

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Using Custom Connector to Get data and then Parse Json and enter into SQL

Hi @Alexlorenzo21 ,

 

Do you want to get the data in the array of employees?

Perhaps you could try the following method:

  • Initialize an Object variable with the variable name Response. Use json response as the value of the variable.
  • Use the "objectName[‘Property Name']" method to get the array contained in it.

Expression:

variables('Response')['employees']
  • Configure Apply to each to traverse the array, because each element is an object, so you can still use "object[‘property’]" to get the value of each property.

For example, id and employeeNumber should be obtained as below, Expression reference:

items('Apply_to_each')['id']
items('Apply_to_each')['employeeNumber']

Image reference:

2.PNG

Please take a try.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Using Custom Connector to Get data and then Parse Json and enter into SQL

Hi @Alexlorenzo21 ,

 

Do you want to get the data in the array of employees?

Perhaps you could try the following method:

  • Initialize an Object variable with the variable name Response. Use json response as the value of the variable.
  • Use the "objectName[‘Property Name']" method to get the array contained in it.

Expression:

variables('Response')['employees']
  • Configure Apply to each to traverse the array, because each element is an object, so you can still use "object[‘property’]" to get the value of each property.

For example, id and employeeNumber should be obtained as below, Expression reference:

items('Apply_to_each')['id']
items('Apply_to_each')['employeeNumber']

Image reference:

2.PNG

Please take a try.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
>