ADF copy activity mapping complete object within array

Question

ADF copy activity mapping complete object within array

Robbert-Jan Wijnen 0

I am exploring the possibilities Azure Data Factory has. I am trying to load data from a web api. The result is a JSON:

{
  "comments": [
    {
      "id": 1,
      "body": "This is some awesome thinking!",
      "postId": 242,
      "likes": 3,
      "user": {
        "id": 105,
        "username": "emmac",
        "fullName": "Emma Wilson"
      }
    },
    {
      "id": 2,
      "body": "What terrific math skills you're showing!",
      "postId": 46,
      "likes": 4,
      "user": {
        "id": 183,
        "username": "cameronp",
        "fullName": "Cameron Perez"
      }
    },
    {
      "id": 3,
      "body": "You are an amazing writer!",
      "postId": 235,
      "likes": 2,
      "user": {
        "id": 1,
        "username": "emilys",
        "fullName": "Emily Johnson"
      }
    }
  ],
  "total": 340,
  "skip": 0,
  "limit": 3
}

My table in SQL server has two columns:

id
json_object

Per comment i want to load the ID in the ID column and the complete json result for that particular comment in the column json_object.

I set my collection reference to: '$['comments']' to loop over each comment. I cannot figure out how to make the mapping for my json_object column. For ID 1 i need the value for the column json_object to be:

{
      "id": 1,
      "body": "This is some awesome thinking!",
      "postId": 242,
      "likes": 3,
      "user": {
        "id": 105,
        "username": "emmac",
        "fullName": "Emma Wilson"
      }
    }

Any suggestions or is this just not possible within the Copy activity?

0 comments

1 answer

Your answer

Answer 1

Venkat Reddy Navari 5,840 Microsoft External Staff Moderator

Hi Robbert-Jan Wijnen

Yes, this scenario is possible within Azure Data Factory's Copy Activity, with a bit of JSON mapping and transformation. Since you want to insert both the id and the entire comment object into your SQL table, here's how you can achieve that:

Set Collection Reference: You've already done this correctly:


$.comments

Create a Mapping in Copy Activity: Under Mappings, map:

id → id
Use Expression mapping to convert the full comment object to a JSON string for json_object.

Use Expression Mapping for json_object: Unfortunately, ADF doesn't have a built-in way to directly map the whole object as a JSON string. But you can work around this using a Derived Column transformation (if using Data Flow), or use a custom inline mapping with ADF's Copy Activity if you're using Mapping Data Flows.

Option A: Use Data Flow

Add a Derived Column step before the sink.

Create a new column json_object using the toString() or string() function:


json_object = toString($$)

Here, $$ refers to the current object, so it serializes the whole comment.

Option B: Preprocess in Azure Function / Logic App (if needed)

If you're limited to just Copy Activity (without Data Flows), then you'd need to preprocess the source in:

Azure Function
Logic App
or REST API that returns json_object as a string field per record.

Alternative: Use Copy Activity with REST + Sink Stored Procedure

If using only Copy Activity, you can write a Stored Procedure as your Sink and pass:

id as one column
the full JSON row as a string using @activity('YourCopyActivity').output.rows or a similar workaround.

Hope this helps. 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.

Venkat Reddy Navari 5,840 Reputation points Microsoft External Staff Moderator

2025-07-15T20:25:34.5133333+00:00

Robbert-Jan Wijnen 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.
Robbert-Jan Wijnen 0 Reputation points

2025-07-16T16:32:03.6766667+00:00

Do you have an example of the last option? When can’t use data flows because we completely run on the self hosted integration runtime
Venkat Reddy Navari 5,840 Reputation points Microsoft External Staff Moderator

2025-07-18T19:55:38+00:00
Robbert-Jan Wijnen Thanks for the follow-up! Since you're limited to using Copy Activity only (without Data Flows) and are running on a Self-hosted Integration Runtime, here's an example of how you can implement the solution using a Stored Procedure as the sink:

Step-by-Step Approach (Without Data Flow):

Copy Activity Setup

Source: Your REST API returning the JSON with comments array.

Collection Reference: Set this to $.comments – you're already doing this correctly.

Sink: SQL Stored Procedure

Instead of directly mapping to a table, define a Stored Procedure that takes two parameters:

CREATE PROCEDURE InsertComment @id INT, @json_object NVARCHAR(MAX) AS BEGIN INSERT INTO YourTargetTable (id, json_object) VALUES (@id, @json_object) END

Mapping in ADF Copy Activity In the Sink settings of your Copy Activity:

Choose Stored Procedure as sink type.

Map:

id → @id

Full object → @json_object

Since ADF Copy Activity doesn’t natively serialize the full object, you can use the @dataset(). or @item() expressions in inline datasets and map @json_object using:

@string(item())

This converts the full comment object (each item() in the loop) to a string, which you can pass as a parameter to the stored procedure.

Hope this helps. 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.

Share via

ADF copy activity mapping complete object within array

1 answer

Your answer