Parsing Complex JSON in Azure Data Factory

Question

Dears - I'm using ADF to parse output of a REST API that is coming in JSON structure. The problem is that JSON contains array of strings, each string value contains JSON object. I want to extract the values of the innermost JSON .. I've tried several attempts with no luck yet..

Sample JSON Structure

  {
    "tables": [
      {
        "name": "PrimaryResult",
        ,
        "rows": [
          ["{\"Subscription Name\":\"master\",\"Operation Name\":\"5f2a763e4f5cabb8d38dd66a\",... \"}","other nested JSON".....

I'm not able to parse those JSONs that are coming as strings surrounded by double quotes.

any ideas ??

If you are frustrated with ADF's pure lack of multi-collection selection when it comes to parsing JSON, Please upvote this: feedback.azure.com/d365community/idea/… — Trent Tamura
– Trent Tamura, Commented Mar 15, 2022 at 13:42

Steve Johnson · Accepted Answer · 2020-12-21 02:40:37Z

2

You can use json function to change JSON string to JSON object.

My test:

Output of Web activity

Use this expression to get value of Subscription Name:

@json(activity('Web1').output.tables[0].rows[0][0])['Subscription Name']

Output of Set variable activity:

Update

I'm not sure what you need. It seems you want to change all JSON string to JSON object.

If so, you can create an array variable, loop rows[0] by For Each activity and transform items to JSON object in a new array.

edited Dec 21, 2020 at 2:40

answered Dec 18, 2020 at 1:40

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mamhh Over a year ago

That works fine. But I need to pass the whole inner JSON to another activity (e.g., Copy) to break it down and save values in Database columns.. what should I do ? Considering that the JSON contains further nested arrays. Please advise ..

Steve Johnson Over a year ago

@user9942114 I update my answer. Is that you need?

mamhh Over a year ago

that approach will not enable me to perform nested loops nor write parsed data to files .. Please advise ..

Steve Johnson Over a year ago

Please show me screenshot of your pipeline and tell me all your need.

Aaron C Over a year ago

@mamhh, assuming you want to do something similar to what I did: replace the foreach "items" box @activity('Get List of Tables').output.tables[0].rows[0] with @activity('Get List of Tables').output.tables[0].rows this ill iterate over the rows. Then, replace @json(item()) with @item()[0]. This will retrieve the first item in each row.

Collectives™ on Stack Overflow

Parsing Complex JSON in Azure Data Factory

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related