ADF Complex json file load

Question

ADF Complex json file load

Aayush Ku Soni 1

Hi Team,

I am having a really complex json file as shown in below sample. Kindly provide me input to deal with this kind of file to load into SQL table by using ADF.

[
    {
        "TBL": "Aisha_abc",
        "Row": [
            {
                "Id": "101",
                "Subject": "Maths"
            }
        ]
    },
    {
        "TBL": "Ayan_efg",
        "Row": [
            {
                "Id": "102",
                "Class": "12th"
            }
        ]
    }
]

Here "TBL" is table names to be created in SQL server. And I'd , subject, class are column names.

Regards,
Aayush

KranthiPakala-MSFT 39,007 Reputation points Microsoft Employee

2021-09-30T21:47:59.61+00:00
Hi @Aayush Ku Soni ,

Thanks for using Microsoft Q&A forum and posting your query.

To better assist on your query, could you please clarify below details?

You have mentioned that "TBL" is the table name in SQL. Does those tables already exist or you want your pipeline flow to create them on the fly while processing the data?

And please confirm how many unique tables your json data might contain? And will the schema be same for all tables?
Aayush Ku Soni 1 Reputation point

2021-10-01T06:41:33.343+00:00
Hi Kranthi,

Please find the details below :

Yes, TBL is the table name. Those tables are not existing, I want to create on the fly while processing data.

Number of unique tables are around 30 present in a single json file document. And, "I'D" column is present in all the tables but other column is changing in all tables. Schema will be same for all of them.

Regards,
Aayush
KranthiPakala-MSFT 39,007 Reputation points Microsoft Employee

2021-10-13T18:11:53.857+00:00

Hi @Aayush Ku Soni ,

Apologies for the delayed response and thanks for confirming. You JSON had multiple nested array objects which is making it complex to achieve in ADF.
But you can give a try using two level pipeline where you loop through each table level array and pass it to child pipeline and then copy data to your desired sink.
Or you can give a try with Mapping data flow. But where as in Mapping data flow we don't have auto create table option so you will have to use a separate transformation which can be check if the table from source file exists in your sink and if not then you will have to create it (may be a stored procedure can help).

Hope this info helps.
Aayush Ku Soni 1 Reputation point

2021-10-15T20:03:29.273+00:00

Thanks for the response.

I tried by using mapping data flows but that is not going to help as I am having 90 tables.

Can you please tell me more about this two level pipeline approach?

It will be appreciated.

Regards,
Aayush

KranthiPakala-MSFT 39,007 Reputation points Microsoft Employee

2021-09-30T21:47:59.61+00:00

Hi @Aayush Ku Soni ,

Thanks for using Microsoft Q&A forum and posting your query.

To better assist on your query, could you please clarify below details?

You have mentioned that "TBL" is the table name in SQL. Does those tables already exist or you want your pipeline flow to create them on the fly while processing the data?

And please confirm how many unique tables your json data might contain? And will the schema be same for all tables?
Aayush Ku Soni 1 Reputation point

2021-10-01T06:41:33.343+00:00

Hi Kranthi,

Please find the details below :

Yes, TBL is the table name. Those tables are not existing, I want to create on the fly while processing data.

Number of unique tables are around 30 present in a single json file document. And, "I'D" column is present in all the tables but other column is changing in all tables. Schema will be same for all of them.

Regards,
Aayush
KranthiPakala-MSFT 39,007 Reputation points Microsoft Employee

2021-10-13T18:11:53.857+00:00

Hi @Aayush Ku Soni ,

Apologies for the delayed response and thanks for confirming. You JSON had multiple nested array objects which is making it complex to achieve in ADF.
But you can give a try using two level pipeline where you loop through each table level array and pass it to child pipeline and then copy data to your desired sink.
Or you can give a try with Mapping data flow. But where as in Mapping data flow we don't have auto create table option so you will have to use a separate transformation which can be check if the table from source file exists in your sink and if not then you will have to create it (may be a stored procedure can help).

Hope this info helps.
Aayush Ku Soni 1 Reputation point

2021-10-15T20:03:29.273+00:00

Thanks for the response.

I tried by using mapping data flows but that is not going to help as I am having 90 tables.

Can you please tell me more about this two level pipeline approach?

It will be appreciated.

Regards,
Aayush

ADF Complex json file load

Activity