Using Data Factory DataFlow to convert a sql server table to a complex JSON

Question

Hello,

I'm trying to convert a sql server table into a complex JSON.

Below is a sample of the table and JSON:

[
{"type":"order",
"action":"purchase",
"identifiers":{"email":"bob@gmail.com"},
"data":{"ts":123456789,
        "order":{"order_id":"OR345","total":109.65,"discount":5.00,"subtotal":103.99,"tax":5.15,"shipping":6.5,"coupon_code":"5OFF","order_type":"Contact",
		"items":[{"product_id":"2045","item_sku":"IT-12345","price":19.99,"quantity":5.00,"discount":0.00,"subtotal":95.00},{"product_id":"2091","price":10.00,"quantity":1.00,"discount":2.00,"subtotal":8.00}]}}}
]

I'm having a difficulty producing the desired JSON using data flow as I'm not able to use the Aggregate schema modifier in a way that will produce the correct result. One of the reasons for this I think has to do with the fact that the "items" object in the JSON is nested within the "order" which is nested within "data".

I'm not sure if I just miss something very simple here or my whole approach to this is flawed to begin with.

Initially I tried using just "copy data" and use the sql server table as a source and JSON (using Azure Blob storage) as a sink. The issue with this approach was that the "items" in the JSON file was not being produced as an array due to the fact that "...complex json path is not supported in the sink".

Hoping someone can point me in the right direction.

Thanks,

Shay

Answer 1

Hi @Shay Levy , Welcome to Microsoft Q&A forum and thanks for reaching out here.

You can use a derived column and aggregate transformation to achieve your requirement in mapping data flow.

Here is a video demonstration on how to create a complex JSON structure from Hierarchical source using Mapping data flows in ADF: Load CSV file in to JSON with Nested Hierarchy using Azure data factory

Here is the public documentation on how to create the JSON structure using derived column in mapping data flow: Creating JSON structures in a derived column transformation of ADF mapping data flow Here is a sample script which is create a complex structure as per your requirement. You will need to enhance it accordingly based on your source structure. (Please note this is just a sample for your reference, please make changes as needed)

source derive(type = type,
		action = action,
		identifiers = @(email=email),
		data = @(ts=@(order=@(order_ID=order_id,
		total=total,
		OrderDiscount=OrderDiscount,
		OrderSubtotal=OrderSubtotal,
		tax=tax,
		shipping=shipping,
		coupon_code=coupon_code,
		order_type=order_type,
		items=@(product_id=product_id,
		item_sku=item_sku,
		item_price=item_price,
		item_quantity=item_quantity,
		item_discount=item_discount,
		Item_subtotal=Item_subtotal))))) ~> derivedColumn2
derivedColumn2 aggregate(groupBy(order_id,
		total,
		OrderDiscount,
		OrderSubtotal,
		tax,
		shipping,
		coupon_code,
		order_type),
	items = collect(data.ts.order.items)) ~> aggregate1

Hope this helps.

---

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.