How to convert Table data to JSON with ADF CopyData

Necmi Kilic 31 Reputation points
2022-10-20T17:50:11.987+00:00

Hi all,

I have a table with 4 columns:

CustomerId CustomerName NumberOfOrders

-------------
----------------- --------------------
1 Abc 11
2 Vgt 19
3 Mkl 7

In a pipeline I would like to create a JSON data with that format:
{
"data": [
[1, "Abc", 11],
[2, "Vgt", 19],
[3, "Mkl", 7]
]
}

What I want to do is sending this json data to Web activity. Is it possible to create this JSON with Copy Data? If possible, how can I set the mappings?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,872 questions
0 comments No comments
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 33,476 Reputation points Microsoft Employee
    2022-10-21T07:36:00.85+00:00

    Hi @Necmi Kilic ,
    Welcome to Microsoft Q&A platform and thanks for posting your question here.
    As per my understanding, you are trying to transform SQL table into JSON . Please let me know if my understanding is incorrect.

    In order to achieve the above requirement, you need to make use of mapping dataflow. Kindly follow the below steps :

    1. Add SQL dataset to Source transformation in mapping dataflow.
    2. Use Derived column transformation to create a new column called data: array(toString(CustomerId),CustomerName,toString(NumberOfOrders))
    3. Use another Derived column transformation to update the newly created column by created nested array: data: array(data)
    4. Use select transformation to unselect the source columns
    5. Use Sink transformation to load the transformed data into JSON file. In the sink settings, provide file name option as 'Output to single file' and provide the filename . Also, set single partition in optimize tab

    Please refer to the below video for more details:
    252894-tabletojson.gif

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Anonymous
    2022-10-21T20:00:40.74+00:00

    If the format below will work you can do dynamically without specifying the columns anywhere. Just create a Copy Activity from a database table source to a json file sink without importing the schemas. In the Copy Activity sink settings specify array of objects under File Pattern. It will look like below.

    [
    {"CustomerId":1, "CustomerName":"Abc", "NumberOfOrders": 11},
    {"CustomerId":2, "CustomerName":"Vgt", "NumberOfOrders": 19},
    {"CustomerId":3, "CustomerName":"Mkl", "NumberOfOrders": 7}
    ]

    This is the simplest way to convert.

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.