topic Re: pipeline Copy Activity JSON file with multiple documents - Mapping in Pipelines

pipeline Copy Activity JSON file with multiple documents - Mapping

KBD — Fri, 18 Jul 2025 20:16:22 GMT

Hello All:

I am making no progress on this issue so I will put out to you good folks.

We are in this situation because Data Factory has issues with attributes that start with special characters. In this case @ (at sign)

My data comes in via a REST API and look like this: {"@odata.context":"https://fifa.com/goalsAssist","value":[ { .......

sample file here:

{"@odata.context":"https://fifa.com/goalsAssist","value":[ { "team": "BLUE", "player": "ROBIN", "yyyymm": 202201, "goals": 1, "assists": 0, "minutes played": 20, "games played": 1 }, { "team": "BLUE", "player": "ROBIN", "yyyymm": 202202, "goals": 0, "assists": 0, "minutes played": 20, "games played": 1 }, { "team": "BLUE", "player": "ROBIN", "yyyymm": 202203, "goals": 0, "assists": 0, "minutes played": 20, "games played": 1 }, { "team": "BLUE", "player": "ROBIN", "yyyymm": 202204, "goals": 2, "assists": 0, "minutes played": 45, "games played": 1 }, { "team": "BLUE", "player": "ROBIN", "yyyymm": 202205, "goals": 0, "assists": 0, "minutes played": 45, "games played": 2 }, { "team": "BLUE", "player": "ROBIN", "yyyymm": 202206, "goals": 3, "assists": 1, "minutes played": 90, "games played": 3 }, { "team": "BLUE", "player": "ROBIN", "yyyymm": 202207, "goals": 1, "assists": 1, "minutes played": 30, "games played": 1 }, { "team": "BLUE", "player": "ROBIN", "yyyymm": 202208, "goals": 4, "assists": 2, "minutes played": 110, "games played": 3 }, { "team": "BLUE", "player": "ROBIN", "yyyymm": 202209, "goals": 6, "assists": 2, "minutes played": 110, "games played": 3 } ] } {"@odata.context":"https://fifa.com/goalsAssist","value":[ { "team": "GREEN", "player": "BOBBY", "yyyymm": 202201, "goals": 2, "assists": 1, "minutes played": 70, "games played": 2 }, { "team": "GREEN", "player": "PATTSY", "yyyymm": 202201, "goals": 0, "assists": 2, "minutes played": 45, "games played": 1 }, { "team": "GREEN", "player": "BOBBY", "yyyymm": 202202, "goals": 1, "assists": 0, "minutes played": 60, "games played": 2 }, { "team": "GREEN", "player": "PATTSY", "yyyymm": 202202, "goals": 0, "assists": 3, "minutes played": 45, "games played": 1 }, { "team": "GREEN", "player": "BOBBY", "yyyymm": 202203, "goals": 0, "assists": 0, "minutes played": 50, "games played": 2 }, { "team": "GREEN", "player": "PATTSY", "yyyymm": 202203, "goals": 0, "assists": 1, "minutes played": 90, "games played": 1 }, { "team": "GREEN", "player": "BOBBY", "yyyymm": 202204, "goals": 3, "assists": 2, "minutes played": 40, "games played": 3 }, { "team": "GREEN", "player": "PATTSY", "yyyymm": 202204, "goals": 0, "assists": 2, "minutes played": 90, "games played": 2 }, { "team": "GREEN", "player": "BOBBY", "yyyymm": 202205, "goals": 2, "assists": 0, "minutes played": 40, "games played": 3 }, { "team": "GREEN", "player": "PATTSY", "yyyymm": 202205, "goals": 1, "assists": 3, "minutes played": 120, "games played": 3 }, { "team": "GREEN", "player": "BOBBY", "yyyymm": 202206, "goals": 1, "assists": 3, "minutes played": 40, "games played": 3 }, { "team": "GREEN", "player": "PATTSY", "yyyymm": 202206, "goals": 0, "assists": 0, "minutes played": 45, "games played": 1 }, { "team": "GREEN", "player": "BOBBY", "yyyymm": 202207, "goals": 0, "assists": 1, "minutes played": 10, "games played": 1 }, { "team": "GREEN", "player": "PATTSY", "yyyymm": 202207, "goals": 0, "assists": 0, "minutes played": 60, "games played": 2 }, { "team": "GREEN", "player": "BOBBY", "yyyymm": 202208, "goals": 4, "assists": 4, "minutes played": 90, "games played": 3 }, { "team": "GREEN", "player": "PATTSY", "yyyymm": 202208, "goals": 0, "assists": 1, "minutes played": 90, "games played": 2 }, { "team": "GREEN", "player": "BOBBY", "yyyymm": 202209, "goals": 3, "assists": 6, "minutes played": 75, "games played": 2 }, { "team": "GREEN", "player": "PATTSY", "yyyymm": 202209, "goals": 1, "assists": 3, "minutes played": 120, "games played": 2 } ] } {"@odata.context":"https://fifa.com/goalsAssist","value":[]}

The Data Factory activities choke on the @odata.context

Please note there are multiple JSON docs in the file and there are mutliple JSON objects per line in the file.

I have been able to handle the multiple JSON docs in the file and the mutliple JSON objects per file in

another Pipeline with the Flatten tool.

My thought is to use Mapping on the Copy activity to ignore the @odata.

I have tried many mappings with not luck .

This is one attempt. @odata.content is NOT included. The rest of the data, I want move over as is.

Re: pipeline Copy Activity JSON file with multiple documents - Mapping

BhaveshPatel — Fri, 18 Jul 2025 23:56:24 GMT

Rather than Data Factory, I should start with Python Notebooks if you know how to do ETL REST APIs in Python. it is just JSON file.

Re: pipeline Copy Activity JSON file with multiple documents - Mapping

KBD — Sat, 19 Jul 2025 00:22:39 GMT

I already have this working in Python with no issues.

But Corporate, the powers that be, want a Data Factory solution because it is " No code"

Re: pipeline Copy Activity JSON file with multiple documents - Mapping

BhaveshPatel — Sat, 19 Jul 2025 08:09:55 GMT

Then Write some code in python notebooks and rest UI/UX in Data Factory. This is how I should do:

Python --> Spark Dataframe --> Convert to Delta Table ( This part programmimg code ) and once delta table is ready, write copy activity in Fabric Data Factory.

Re: pipeline Copy Activity JSON file with multiple documents - Mapping

Anonymous — Thu, 24 Jul 2025 17:03:23 GMT

Hi KBD

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

Re: pipeline Copy Activity JSON file with multiple documents - Mapping

Anonymous — Mon, 28 Jul 2025 04:11:04 GMT

Hi KBD

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

Re: pipeline Copy Activity JSON file with multiple documents - Mapping

Anonymous — Thu, 31 Jul 2025 08:54:08 GMT

Hi KBD

As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards

Re: pipeline Copy Activity JSON file with multiple documents - Mapping

justinjbird — Sat, 02 Aug 2025 12:02:54 GMT

Wasn't clear from your description where you intend to send the data or what your specific issue was. But I attempted this using the example file and send it to a lakehouse table.

You have to set set up a collection reference which is the array you want to iterate over. In your structure that would be `$['value']` from there you map the columns relative to the collection path. Screenshot of my mapping...

27 rows copied from the example file...

If you are trying to send it somewhere else should be similar principle. Note in the example I also added an additional column `runDTS` for debug.

Re: pipeline Copy Activity JSON file with multiple documents - Mapping

KBD — Mon, 04 Aug 2025 13:25:18 GMT

Thanks for your kind attention to this matter.

That is what I came up with also.

In short only work with $value array ,

make no reference to @odata.context

Took a while to figure out the correct config.

Thanks again for your suggestion.