Unable to flatten a complex json object using mapping data flow flatten activity

Question

Hi all,

I have a json file with this output:

{"latitude":51.775043,"longitude":5.583942,"generationtime_ms":0.041961669921875,"utc_offset_seconds":3600,"timezone":"Europe/Berlin","timezone_abbreviation":"CET","elevation":9.0,"daily_units":{"time":"iso8601","temperature_2m_max":"°C","temperature_2m_min":"°C"},"daily":{"time":["2024-01-01","2024-01-02"],"temperature_2m_max":[8.6,12.0],"temperature_2m_min":[5.7,6.7]}}

I'm trying to flatten the file in dataflow. At Unroll by I can choose temperature_2m_max or time.

When I choose time, temperature_max stays an array. 

When I choose both I get errors in data preview. 

I want them both to show the data, without arrays. 

Can you please help me?

I check this one: https://learn.microsoft.com/en-us/answers/questions/1251662/unable-to-flatten-a-complex-json-object-using-mapp

This doesn’t solve my problem. 

Screenshot 2024-11-26 at 11.14.42.png

Screenshot 2024-11-26 at 11.14.08.png

Answer 1

Hello Mariska Janson,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are having trouble flattening your JSON file in Dataflow.

To solve this issue, use the step by step below:

1. In your Dataflow, select the time array to unroll. This will create a new row for each date.
2. After unrolling by time, you need to map the temperature_2m_max and temperature_2m_min values to each corresponding date. This can be done using a mapping transformation.
3. Ensure that the structure is flattened by mapping each element correctly. You might need to use a combination of transformations to achieve this.

This is an example of how you can structure your Dataflow transformations:

[
  {
    "type": "Unroll",
    "field": "daily.time"
  },
  {
    "type": "Map",
    "mappings": [
      {
        "source": "daily.temperature_2m_max",
        "target": "temperature_2m_max"
      },
      {
        "source": "daily.temperature_2m_min",
        "target": "temperature_2m_min"
      }
    ]
  }
]

If you continue to encounter errors, it will need to check the specific error messages in the data preview, as these can provide clues about what might be going wrong.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Answer 2

@Mariska Janson - 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. 

Ask: Unable to flatten a complex json object using mapping data flow flatten activity

Solution: Instead of a data flow. i did a copy activity. In the mapping: Map complex values to string. After that i did a SQL script with a cross apply

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. 

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue. 

 

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members. 

Answer 3

@Mariska Janson - Here are the detailed steps you can follow to perform the mapping transformation in Azure Data Factory:

1. Unroll the 'time' Field:
   • In your Data Flow, add a new transformation and select 'Unroll' (or sometimes called 'Flatten') from the list of transformations.
     • In the 'Unroll by' field, select daily.time. This will create a new row for each date in the 'time' array.
2. Add Mapping Transformation:
   • After unrolling, add a 'Select' transformation to your Data Flow to map the fields appropriately.
     • Inside the 'Select' transformation, you need to manually map the fields from the arrays to new columns.
3. Creating the Mappings:
   • Map the daily.temperature_2m_max array to a new column named temperature_2m_max.
     • Similarly, map the daily.temperature_2m_min array to a new column named temperature_2m_min.

Here’s a step-by-step guide to do the mapping transformation:

1. Add Unroll Transformation:
   • Open your Data Flow.
   • Click on the plus icon to add a new transformation.
   • Choose 'Unroll' from the list of transformations.
   • In the 'Unroll by' field, select daily.time.
2. Add Select Transformation:
   • After the Unroll transformation, click on the plus icon again to add a new transformation.
   • Choose 'Select' from the list of transformations.
   • In the 'Select' transformation, you will see a list of fields. You need to map the array fields to new columns.
3. Map Arrays to Columns:
   • In the 'Select' transformation, you will see a list of fields from the JSON.
   • Find the daily.temperature_2m_max array and map it to a new column, let's name it temperature_2m_max.
   • Similarly, find the daily.temperature_2m_min array and map it to a new column, let's name it temperature_2m_min.
4. Validate the Data Flow:
   • After mapping the fields, click on 'Data Preview' to validate your transformations and ensure that the JSON data is flattened correctly.

For more details refer to this: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-flatten

https://learn.microsoft.com/en-us/answers/questions/1251662/unable-to-flatten-a-complex-json-object-using-mapp

Hope this helps. Do let us know if you 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.