How to send a JSON object instead of an array from Azure Data Factory REST Sink

Question

How to send a JSON object instead of an array from Azure Data Factory REST Sink

Kingsley Leighton 20

I have a pipeline in Azure Data Factory that has a Copy data Activity with an Azure blob storage source(contains JSON files which each have a single JSON object) and a REST sink. I am sending a PUT request and the API endpoint I am sending the request to expects a JSON object, not an array. I cannot change what the API expects.

On the Microsoft learn page for data factory, REST as Sink https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory#rest-as-sink it specifies that the JSON will be sent with the following format:

[
    { <data object> },
    { <data object> },
    ...
]

Is there any way to change the settings or customize the REST sink so it sends the JSON in the below format instead? Or another connector that would be better suited to this?

{ <data object> }

NOTE:
When I have tested this with one file it still sends in array format, just with one element:

[
	{ <data object> }
]

Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-11-25T05:39:41.8233333+00:00
Hi @Kingsley Leighton

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

In Azure Data Factory (ADF), when using the REST sink, by default, the output is formatted as an array of JSON objects (even when you are sending a single JSON object). This is due to the way the REST API connector in ADF is designed to work.

To send a JSON object instead of an array from Azure Data Factory's REST sink, you may need to adjust your pipeline configuration. The default behavior of the REST sink is to send data in an array format, as you noted. Unfortunately, there is no direct setting in the REST sink to change this behavior to send a single JSON object.

For more details, please refer: JSON as source

However, you can consider using a mapping data flow to transform the data before it reaches the REST sink. In the mapping data flow, you can use the "Select" transformation to flatten the structure and ensure that only a single object is passed to the REST sink. This way, when you configure the REST sink, it will receive the data in the desired format.

Benefits of Data Flow Transformation: Using a Data Flow transformation offers several advantages:

Visually Intuitive: Data Flow provides a user-friendly interface for data manipulation, making it easier to understand and manage the transformation process.

Reusable Transformations: Transformations can be reused across different pipelines or data flows, promoting code reusability and maintainability.

No Custom Coding: Unlike the script activity, Data Flow doesn't require writing custom code, making it accessible to users with less scripting experience.

For additional information please refer to a similar thread: https://learn.microsoft.com/en-us/answers/questions/1226454/azure-data-factory-rest-linked-service-sink-return

I hope this information helps. Please do let us know if you have any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-11-26T01:54:30.13+00:00

@Kingsley Leighton

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Kingsley Leighton 20 Reputation points

2024-11-26T16:57:40.78+00:00

Thank you for taking the time to respond. This answer is not entirely clear to me. Please help me understand.

You say, "when using the REST sink, by default, the output is formatted as an array of JSON objects (even when you are sending a single JSON object)." but then say, "To send a JSON object instead of an array from Azure Data Factory's REST sink, you may need to adjust your pipeline configuration." Implying there is a way to send an object from REST sink instead of an array.

It seems you are suggesting that in order to send an object instead of an array from REST sink, I should: "consider using a mapping data flow to transform the data before it reaches the REST sink. In the mapping data flow, you can use the "Select" transformation to flatten the structure and ensure that only a single object is passed to the REST sink.", but you said previously (in the first quote) that even if one JSON object is sent to REST sink it will still send as an array? I do not see how flattening would help here regardless since I am only "sending one file".

For more context on this, I am using a Copy Data activity with Azure blob container as source and REST as sink. I need to use a Copy activity at the end of the pipeline because the API endpoint I am sending to from the REST sink is an on prem URL not accessible publicly. I have the source configured to grab all JSON files from a specified path in the container but only ever put one JSON file(containing one object) at that file path at a time. Therefore, I do not see how using a mapping transformation to select one JSON object(when already only one is ever selected) would change anything here?
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-11-27T05:56:10.2133333+00:00

@Kingsley Leighton

I apologize for the inconsistency in the previous explanation.

Thank you for the additional context – it helps clarify the situation. I see why it might feel unnecessary to use a Mapping Data Flow in your case, as you're only ever processing one JSON file containing a single object at a time.

However, the core issue lies in how Azure Data Factory's REST sink operates. Even though you're processing just one file with a single object, ADF still treats the input data as an array—even if the array contains only one element. This is the default behavior of the REST sink, which expects data in array format, regardless of the number of records being sent.

Here’s why using a Mapping Data Flow can still help:

Underlying Behavior of REST Sink: In Azure Data Factory, the REST Sink always wraps the data in an array, even if the source consists of only one JSON object. This is part of the default behavior of the REST connector, which expects the data to be formatted as an array, regardless of the number of items. Therefore, the REST sink will always send data as an array, even if there is only one item (a single JSON object in your case). This is why you'll see the data in the format: [ { <data object> } ].

Flexibility for Future Scenarios: This approach not only addresses your current needs but also prepares your pipeline for potential future scenarios where you might need to send multiple files from the blob container. In such cases, the mapping data flow will ensure that each object is sent as a separate payload to the REST sink.

Alternatively, if Data Flow doesn’t seem like the best fit for your case, you might consider using a Custom Web Activity instead of the REST sink. The Web Activity offers more flexibility in formatting the HTTP request, allowing you to send the payload as a single JSON object before forwarding it to the on-prem REST endpoint. This could give you greater control over the request format without relying on the array behavior of the REST sink.

Note: While there is no direct configuration in ADF to modify the array formatting behavior of the REST Sink, using a Web Activity or a Mapping Data Flow (to prepare the data) could help you achieve the desired result. The Web Activity will provide the most control over the structure of the payload being sent to the API.

I hope this information helps.

Thank you.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-11-28T10:11:04.7+00:00

@Kingsley Leighton

Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Kingsley Leighton 20 Reputation points

2024-12-02T16:31:26.3833333+00:00

@Chandra Boorla

Thank you this helps, but I am still seeing a contradiction:

Using Mapping Data Flow: You say, "the REST sink will always send data as an array, even if there is only one item", but then, "This approach not only addresses your current needs [...] the mapping data flow will ensure that each object is sent as a separate payload to the REST sink".

It is still not clear to me how Mapping Data Flow can address my current needs: I need to send an object. If the REST sink always sends data as an array how does using a Mapping Data Flow to "ensure that each object is sent as a separate payload to the REST sink" make any difference in what REST sink will send because as established in your response, "the REST Sink always wraps the data in an array, even if the source consists of only one JSON object. This is part of the default behavior of the REST connector, which expects the data to be formatted as an array, regardless of the number of items. Therefore, the REST sink will always send data as an array"?
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-12-04T11:02:17.1666667+00:00

@Kingsley Leighton

In Azure Data Factory (ADF) REST sink by default always sends data in the format of a JSON array, even when the input is a single JSON object. This is part of the default behavior of the REST connector in ADF, and unfortunately, there is no direct configuration within the REST sink itself to send data as a single object rather than an array.

Why Mapping Data Flow Doesn't Help:

You are correct to be confused about how Mapping Data Flow could help in this scenario. Apologies for the confusion, if your source JSON file contains a single object and you're using the REST sink, ADF will still wrap the data in an array format: [ { ... } ]. The Mapping Data Flow's purpose is to manipulate or transform data structures, but it doesn’t directly alter how the REST sink wraps the payload (i.e., in an array format). So, even with Data Flow transformations, the final payload will still be sent as an array, not a single object.

Recommended Approach - Web Activity

The Web Activity provides more flexibility and allows you to control the exact structure of the HTTP request, including the payload format.

The Web Activity allows you to fully control the HTTP request and send the data in the format that your API requires. This gives you the flexibility to bypass the REST Sink’s default array formatting and achieve the desired result of sending a single JSON object.

To send a JSON object instead of an array in your pipeline, the Web Activity is the best approach. This will give you the control you need over the payload structure without being restricted by the default behavior of the REST Sink.

I hope this information helps.

Thank you.
Kingsley Leighton 20 Reputation points

2024-12-04T16:28:38.23+00:00

Thank you that resolves the confusion, I appreciate all of your responses. I will try web activity and post an answer with that approach fully explained if it works.
Kingsley Leighton 20 Reputation points

2024-12-04T16:58:44.04+00:00
This cannot be done using the current functionality of Azure Data Factory REST Sink, which will always send the request body as an array of JSON object regardless of the number of JSON objects sent. To my knowledge, there is no way to re-configure this.

I have found a way using a Lookup Activity and a Web Activity. Use a Lookup activity to grab a single or multiple JSON file(s) from the Blob Storage, this will be the output of the Lookup activity. A Web Activity can then be triggered after the completion of the Lookup Activity. In the Web Activity you must specify the body of the message, in which you can use dynamic content to insert the output of the Lookup activity like so:

@activity('Lookup Product IFP').output.firstRow

This will grab the first JSON object found from the Lookup Activity (firstRow), thus only the JSON object will be sent as the body rather than an array of JSON objects.

Big thank you to @Chandra Boorla for suggesting Web Activity for more control over the request body.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-12-05T01:57:10.2633333+00:00

@Kingsley Leighton

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-12-06T01:17:15.7666667+00:00

@Kingsley Leighton

We haven’t heard from you on the last response, please do click Accept Answer and Yes for was this answer helpful, which might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Answer accepted by question author

1 additional answer

Your answer

Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-11-26T01:54:30.13+00:00

@Kingsley Leighton

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Kingsley Leighton 20 Reputation points

2024-11-26T16:57:40.78+00:00

Thank you for taking the time to respond. This answer is not entirely clear to me. Please help me understand.

You say, "when using the REST sink, by default, the output is formatted as an array of JSON objects (even when you are sending a single JSON object)." but then say, "To send a JSON object instead of an array from Azure Data Factory's REST sink, you may need to adjust your pipeline configuration." Implying there is a way to send an object from REST sink instead of an array.

It seems you are suggesting that in order to send an object instead of an array from REST sink, I should: "consider using a mapping data flow to transform the data before it reaches the REST sink. In the mapping data flow, you can use the "Select" transformation to flatten the structure and ensure that only a single object is passed to the REST sink.", but you said previously (in the first quote) that even if one JSON object is sent to REST sink it will still send as an array? I do not see how flattening would help here regardless since I am only "sending one file".

For more context on this, I am using a Copy Data activity with Azure blob container as source and REST as sink. I need to use a Copy activity at the end of the pipeline because the API endpoint I am sending to from the REST sink is an on prem URL not accessible publicly. I have the source configured to grab all JSON files from a specified path in the container but only ever put one JSON file(containing one object) at that file path at a time. Therefore, I do not see how using a mapping transformation to select one JSON object(when already only one is ever selected) would change anything here?
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-11-28T10:11:04.7+00:00

@Kingsley Leighton

Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Kingsley Leighton 20 Reputation points

2024-12-02T16:31:26.3833333+00:00

@Chandra Boorla

Thank you this helps, but I am still seeing a contradiction:

Using Mapping Data Flow: You say, "the REST sink will always send data as an array, even if there is only one item", but then, "This approach not only addresses your current needs [...] the mapping data flow will ensure that each object is sent as a separate payload to the REST sink".

It is still not clear to me how Mapping Data Flow can address my current needs: I need to send an object. If the REST sink always sends data as an array how does using a Mapping Data Flow to "ensure that each object is sent as a separate payload to the REST sink" make any difference in what REST sink will send because as established in your response, "the REST Sink always wraps the data in an array, even if the source consists of only one JSON object. This is part of the default behavior of the REST connector, which expects the data to be formatted as an array, regardless of the number of items. Therefore, the REST sink will always send data as an array"?
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-12-04T11:02:17.1666667+00:00

@Kingsley Leighton

In Azure Data Factory (ADF) REST sink by default always sends data in the format of a JSON array, even when the input is a single JSON object. This is part of the default behavior of the REST connector in ADF, and unfortunately, there is no direct configuration within the REST sink itself to send data as a single object rather than an array.

Why Mapping Data Flow Doesn't Help:

You are correct to be confused about how Mapping Data Flow could help in this scenario. Apologies for the confusion, if your source JSON file contains a single object and you're using the REST sink, ADF will still wrap the data in an array format: [ { ... } ]. The Mapping Data Flow's purpose is to manipulate or transform data structures, but it doesn’t directly alter how the REST sink wraps the payload (i.e., in an array format). So, even with Data Flow transformations, the final payload will still be sent as an array, not a single object.

Recommended Approach - Web Activity

The Web Activity provides more flexibility and allows you to control the exact structure of the HTTP request, including the payload format.

The Web Activity allows you to fully control the HTTP request and send the data in the format that your API requires. This gives you the flexibility to bypass the REST Sink’s default array formatting and achieve the desired result of sending a single JSON object.

To send a JSON object instead of an array in your pipeline, the Web Activity is the best approach. This will give you the control you need over the payload structure without being restricted by the default behavior of the REST Sink.

I hope this information helps.

Thank you.
Kingsley Leighton 20 Reputation points

2024-12-04T16:28:38.23+00:00

Thank you that resolves the confusion, I appreciate all of your responses. I will try web activity and post an answer with that approach fully explained if it works.
Kingsley Leighton 20 Reputation points

2024-12-04T16:58:44.04+00:00

This cannot be done using the current functionality of Azure Data Factory REST Sink, which will always send the request body as an array of JSON object regardless of the number of JSON objects sent. To my knowledge, there is no way to re-configure this.

I have found a way using a Lookup Activity and a Web Activity. Use a Lookup activity to grab a single or multiple JSON file(s) from the Blob Storage, this will be the output of the Lookup activity. A Web Activity can then be triggered after the completion of the Lookup Activity. In the Web Activity you must specify the body of the message, in which you can use dynamic content to insert the output of the Lookup activity like so:

@activity('Lookup Product IFP').output.firstRow

This will grab the first JSON object found from the Lookup Activity (firstRow), thus only the JSON object will be sent as the body rather than an array of JSON objects.

Big thank you to @Chandra Boorla for suggesting Web Activity for more control over the request body.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-12-05T01:57:10.2633333+00:00

@Kingsley Leighton

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-12-06T01:17:15.7666667+00:00

@Kingsley Leighton

We haven’t heard from you on the last response, please do click Accept Answer and Yes for was this answer helpful, which might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Answer 1

@Kingsley Leighton

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Issue:

I have a pipeline in Azure Data Factory that has a Copy data Activity with an Azure blob storage source(contains JSON files which each have a single JSON object) and a REST sink. I am sending a PUT request and the API endpoint I am sending the request to expects a JSON object, not an array. I cannot change what the API expects.

On the Microsoft learn page for data factory, REST as Sink https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory#rest-as-sink it specifies that the JSON will be sent with the following format:

[
    { <data object> },
    { <data object> },
    ...
]

Is there any way to change the settings or customize the REST sink so it sends the JSON in the below format instead? Or another connector that would be better suited to this?

{ <data object> }

NOTE: When I have tested this with one file it still sends in array format, just with one element:

[
	{ <data object> }
]

Solution:

I have found a way using a Lookup Activity and a Web Activity. Use a Lookup activity to grab a single or multiple JSON file(s) from the Blob Storage, this will be the output of the Lookup activity. A Web Activity can then be triggered after the completion of the Lookup Activity. In the Web Activity you must specify the body of the message, in which you can use dynamic content to insert the output of the Lookup activity like so:

@activity('Lookup Product IFP').output.firstRow

This will grab the first JSON object found from the Lookup Activity (firstRow), thus only the JSON object will be sent as the body rather than an array of JSON objects.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

Hope this helps. Do let us know if you have any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-12-09T01:39:53.0933333+00:00

@Kingsley Leighton

Following up to see if the above suggestion was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Kingsley Leighton 20 Reputation points

2024-12-09T15:43:18.69+00:00

@Chandra Boorla Thank you for reposting this, when you copied the answer over it looks like the inline code did not copy over correctly. There should be no "YAMLAI" line above the code block and the code should read:

@activity('Lookup Product SCH').output.firstRow
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2024-12-09T17:16:24.9133333+00:00

@Kingsley Leighton

I apologize for the oversight and appreciate you bringing it to my attention. I have now modified my post. Could you please take a moment to click "Accept the answer" if the above suggestion was helpful. This will be beneficial to other community members reading this thread.

Thank you.

Answer 2

Hi both,

I was having the same issue as @Kingsley Leighton and I eventually found out that the approach of using a LookUp + Web activities helped. However, I hit a limitation recently in the LookUp activity when I was trying to read the content of a particularly big JSON (a few MBs). The LookUp failed in this case with this error:

ErrorCode=UserErrorComputePluginFailure,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The size of lookup activity result exceeds the limitation 4194304.,Source=,''Type=Microsoft.DataTransfer.Execution.Core.ExecutionException,Message=The size of lookup activity result exceeds the limitation 4194304.,Source=Microsoft.DataTransfer.TransferTask,'

Is there any way to overcome this, @Chandra Boorla ? Did you ever face this @Kingsley Leighton ? If yes, how did you solve the issue?

Thank you both in advance.

Share via

How to send a JSON object instead of an array from Azure Data Factory REST Sink

1 additional answer

Your answer