Azure data factory data flow json to SQL

Question

I have a source JSON file with hierarchical data, which I need to sink in two SQL tables(relational). The JSON is as below

{
"orders":[
  {
    "orderid":"30933",
    "ordername":"abc",
    "items":[
      {
        "itemid":1,
        "itemstatus":"Failed"
      },
      {
        "itemid":2,
        "itemstatus":"Failed"
      }
    ]
  },
  {
    "orderid":"308320",
    "ordername":"xyz",
    "items":[
      {
        "itemid":5,
        "itemstatus":"Succeeded"
      }
    ]
  }
]
}

My SQL holding two tables Order and OrderItem with OrderID primary and foreign key.

Now I have an Azure data factory data flow with source as above JSON and I need to park all data relational in respective tables.

So here I need OrderId(30933,308320) and OrderName(abc,xyz) will go into Order table and respective items data go into OrderItem table(which reference OrderId from Order table). In this case Order table have 2 and OrderItem table have 3 entries.

Hi @Manish Jain, If my answer is helpful for you, hope you can accept it as answer. This can be beneficial to other community members. Thank you. If you have any other concerns, please let us know. — Leon Yue
– Leon Yue, Commented Aug 10, 2020 at 0:58
@LeonYue,Thanks for your time and effort. Sure I'll check it and do it. — Manish Jain
– Manish Jain, Commented Aug 12, 2020 at 8:37

Leon Yue · Accepted Answer · 2020-08-26 05:34:16Z

1

We can not achieve that in one copy active.

We could using two copy actives in one pipeline, I tested and it succeed. You could follow my steps bellow:

Copy active1: copy the data from Orders(orderid and ordername) to table Orders.
Copy active2: copy the data from items(itemid and itemstatus) to table OrderItems.

Note:

Copy active 1 and 2 use the same json file as the source. The differences are in the Mapping settings.
Copy active 1 sink is Azure SQL database table Orders, Copy active 2 sink is Azure SQL database table OrderItems.

To make you understand it clearly, I made two GIF pictures.

Mapping settings in Copy active 1:

Mapping settings in Copy active 2:

Run the pipeline:

Check the data in table:

The limit is that we only could get the first element of items, we can not choose the collection reference both.

Update:

Congratulations that you achieved it in another way: flatten/transpose the data using data flow and then maintain it to pour in a relational SQL table.

edited Aug 26, 2020 at 5:34

answered Aug 7, 2020 at 3:15

Leon Yue

16.5k1 gold badge17 silver badges26 bronze badges

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

2 Comments

Manish Jain Over a year ago

Hi Actually I achieved it in another way, but as per your knowledge, I would say the answer will productive to others. I flatten/transpose the data using data flow and then maintain it to pour in a relational SQL table.

Leon Yue Over a year ago

@ManishJain Congratulations! I will update my answer. Thanks for share the workaround for us.

Collectives™ on Stack Overflow

Azure data factory data flow json to SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related