JSON Array from REST API to ADLS

Divakar Krishnan 106 Reputation points
2022-01-20T13:16:26.067+00:00

Dear Team,

I have a requirement to load all JSON files which has Array type from REST API to ADLS Gen 2 (BLOB format).

I tried to use Copy Activity but Array type [0] is not accepting in Copy activity, I cannot manipulate JSON file and I need to load as is.

Can you please suggest me on how to achieve this requirement?

Note: As a workaround, I am loading those JSON files with array type to Azure SQL by using same copy activity.

Thanks,
P.K.Dviakar

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,717 questions
{count} votes

2 answers

Sort by: Most helpful
  1. svijay-MSFT 5,211 Reputation points Microsoft Employee
    2022-01-21T14:02:05.267+00:00

    Hello @Divakar Krishnan ,

    Welcome to the Microsoft Q&A platform.

    From my understanding - You would like to manipulate the JSON data before storing it to the blob.

    You could make use of mapping data flow which offers lot of transformation capabilities compared to Copy Activity.

    Also you can configure the input form of the JSON document. I believe you are looking setting below setting.

    167291-image.png

    If my understanding is in correct, please let me know I'll update/correct my answer accordingly.

    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
    0 comments No comments

  2. Divakar Krishnan 106 Reputation points
    2022-01-21T14:14:23.933+00:00

    Hello @svijay-MSFT ,

    Thanks for your reply.

    My input is JSON with array type from REST API, I would like to bring this data and store it in our azure data lake as is as a raw data without any manipulation.

    I tried to do this using Copy activity using Sink as BLOB Dataset with JSON type. However, my input JSON is Array type [0], so it's throwing error at the time of run time.

    Issue is happening only with Sink as BLOB Dataset with JSON type or parquet, same is working when I loading it in Azure SQL table . But our strategy is, we need to load all raw data in our data lake.

    Please suggest me the best way to load it in our data lake in JSON format

    ![167302-image.png]1