Dynamically retrieve relevant data from JSON and copy to SQL Table through Azure Data Factory

Question

I am looking for an easy way to dynamically copy data from JSONs to SQL tables. The JSONs all have the same setup where the data is mentioned after fields like so:

  {
    "name": "Employees",
    "description": "Employeedata",
    "fields": [
      {
        "id": "EmployeeId",
        "fieldId": "U001",
        "dataType": "string",
        "label": "Employee",
        "length": 15,
        "controlType": 5,
        "decimals": 0,
        "decimalsFieldId": ""
      },
      {
        "id": "PersonId",
        "fieldId": "U003",
        "dataType": "string",
        "label": "EmpID",
        "length": 15,
        "controlType": 5,
        "decimals": 0,
        "decimalsFieldId": ""
      },
      {
        "id": "EmployerId",
        "fieldId": "U002",
        "dataType": "string",
        "label": "Employer",
        "length": 15,
        "controlType": 5,
        "decimals": 0,
        "decimalsFieldId": ""
      }
    ]
  }
]

The main structure is the same, so the relevant data is always after the fields property. However, the columns and corresponding data differs for each JSON.

When I perform the copy activity as is, the table ends up like this:

Name	Description
Employees	Employeedata

What I want is something like this:

id	fieldId	datatype	label	length	controlType
EmployeeId	U001	string	Employee	15	5
PersonId	U003	string	EmpID	15	5
EmployerId	U002	string	Employer	15	5

I am looking for a way to copy only the arrays after fields to an SQL table without having to manually assign a mapping for each JSON.

In SQL (all dialects, not just Oracle) the output of a query must have a fixed, known number of columns in the output. It is impossible to generate an unknown number of columns in the output if you are using a static SQL query. — MT0
– MT0, Commented Oct 13, 2022 at 12:28

Saideep Arikontham · Accepted Answer · 2022-10-14 12:29:34Z

1

Move JSON file to SQL DB by the copy activity, provide JSON as source.

Provide SInk as SQL database.

()

In the mapping field need to import schema and enable Advance editor field, need to select options in Collection reference field.

()

Delete these column fields to get expected result as per the requirement.

()

Result get as we expected in SQL Database.

()

edited Oct 14, 2022 at 12:29

Saideep Arikontham

6,1922 gold badges6 silver badges14 bronze badges

answered Oct 13, 2022 at 16:34

Nagasai

513 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamically retrieve relevant data from JSON and copy to SQL Table through Azure Data Factory

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related