How to generate a JSON file in ADF from SQL table source based on a pre-defined JSON structure?

Question

How to generate a JSON file in ADF from SQL table source based on a pre-defined JSON structure?

Robbin Berger 20

Hi There,

I'm trying to create a JSON file based ofa pre-defined structure.

The source is a existing SQL Table in Azure.

To achieve this i've uploaded the json file to the datalake and created a 'dataset' to that file.

In a copy activity the mapping schema loads propperly and I can even map the fields as I need.

However when I run the activity an error occurs no matter what I try.

The unfortunate thing is that the error does not contain any message.

The JSON structure looks like below.
In which the 'field' fields need to be populated from the SQL Table.

Does anyone have any experience in mapping SQL table source to JSON sink?
Any help would be greatly appreciated.

{
  "Object1": {
    "Object2": {
      "@Field1": "",
      "Fields": {
        "Field2": 0,
        "Field3": "",
        "Field4": ""
      }
    }
  }
}

AnnuKumari-MSFT 34,571 Reputation points Microsoft Employee Moderator

2023-03-29T04:28:08.3+00:00

@Robbin Berger ,

Just checking in to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

Answer accepted by question author

AnnuKumari-MSFT 34,571 Microsoft Employee Moderator

Hi @Robbin Berger ,

Welcome to Microsoft Q&A platform and thanks for posting your question here. As I understand your query, you are trying to copy data from SQL table to JSON file whose structure is already defined. Kindly let me know if that is not the ask here.

Could you please try removing @ symbol before Field1 and see if it works?

mapping

You can also use derived column transformation of Mapping dataflow in azure datafactory to create nested json using Add subcolumn options.

For more details, kindly check the following videos:

How to convert a JSON record into NESTED JSON using mapping dataflow

Load CSV file in to JSON with Nested Hierarchy using Azure data factory

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thanks

Robbin Berger 20 Reputation points

2023-03-31T12:44:35.9866667+00:00

Hi Annu,

The problem was indeed the @ sign.

At first it did not seem to be the apparent problem but later I found out that the imported schema did not directly change after removing the @ sign from the JSON format and simply running the pipeline again.

So after first changing the .JSON format, than making a new dataset and use that one in the pipeline the pipeline was running without errors.

Later we added the "@" sing back in the "Mapping" menu under the column "Name" so the output was in line again with target system requirements.

Thanks a lot for your support!

Kind regards,

Robbin

1 additional answer

Your answer

AnnuKumari-MSFT 34,571 Reputation points Microsoft Employee Moderator

2023-03-29T04:28:08.3+00:00

@Robbin Berger ,

Just checking in to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'
Robbin Berger 20 Reputation points

2023-03-31T12:44:35.9866667+00:00

Hi Annu,

The problem was indeed the @ sign.

At first it did not seem to be the apparent problem but later I found out that the imported schema did not directly change after removing the @ sign from the JSON format and simply running the pipeline again.

So after first changing the .JSON format, than making a new dataset and use that one in the pipeline the pipeline was running without errors.

Later we added the "@" sing back in the "Mapping" menu under the column "Name" so the output was in line again with target system requirements.

Thanks a lot for your support!

Kind regards,

Robbin

Answer 1

Robbin Berger 20

Knipsel

0 comments