How to parse and flatten these JSON records

Question

Hello,

I have a JSON file in the following format:

{
"first_name": "Jane",
"last_name": "Doe",
"userid": 12345,
“profile”: {
“annoying_field_name_10”: {“field_id”: 15, “field_name”: “Gender”, “value”: “Female”, “applicable”: 1 },
“annoying_field_name_11”: {“field_id”: 16, “field_name”: “Interests”, “value”: “Baking”, “applicable”: 1 }
}
}

In this file i have 5000 users, with around 150 profile fields each, the "annoying_field_name..." is completely unique for every user and field name combination.
I would like to parse and flatten the file so my results look like this:

I have not been able to figure out how to use a wildcard or dynamics expressions in either parsing or flatten and unroll functions in ADF.

Is anyone able to advise further on this please?

Answer 1

Hi @Mia Lancaster ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As I understand your query, you are trying to transform the JSON data provided in the question into a tabular structure. Please let me know if that is not the case.

I looked at the structure of the JSON , it seems due to the different key names in 'profile' json i.e. 'annoying_field_name_(n)' columns , json is not having proper defined schema. So you need to select all these columns one by one in order to flatten it.

Since the JSON you shared is having multiple nested JSONs within, it would be quite a hectic job to select the column names one by one considering the fact that the number of users are 5000 and there are 150 profile fields in your case. Even after manually selecting each and every column names , it won't return the desired result , it will create multiple columns 'annoying_field_name1.fieldid1', 'annoying_field_name1.fieldid2' etc .

The best way to handle it is to correct the json from source and have defined schema like below, so that on a single go you can achieve the desired result. Kindly ask the source team to reform the json like below or use a custom code in C#, or java and transform the json first:

{  
   "first_name":"Jane",  
   "last_name":"Doe",  
   "userid":12345,  
   "profile":{  
      "annoying_field_name":[  
         {  
            "field_id":15,  
            "field_name":"Gender",  
            "value":"Female",  
            "applicable":1  
         },  
         {  
            "field_id":16,  
            "field_name":"Interests",  
            "value":"Baking",  
            "applicable":1  
         }  
      ]  
   }  
}  

On top of this result, you can use select transformation to remove the unwanted columns like 'first_name' and 'last_name'.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote 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