As the title suggests, I'm trying to take a complex JSON file, flatten it, and put it into a dedicated SQL pool table. The data is an output from the Shopify API orders endpoint. Here is a sample, it is one single order:
[
{
"id": 4469307,
"admin_graphql_api_id": "gid://shopify/Order/something",
"app_id": 580111,
"browser_ip": "0.0.0.123456",
"buyer_accepts_marketing": false,
"cancel_reason": null,
"cancelled_at": null,
"cart_token": "8a74ef5f327ebe9",
"checkout_id": 231875,
"checkout_token": "aba460",
"client_details": {
"accept_language": "en-US,en;q=0.9",
"browser_height": 800,
"browser_ip": "68.132.33.3",
"browser_width": 1600,
"session_hash": null,
"user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/101.0.4951.54 Safari/537.36"
},
"closed_at": null,
"confirmed": true,
"created_at": "2022-07-10T15:49:36-07:00",
"currency": "USD",
"current_subtotal_price": "2.00",
"current_subtotal_price_set": {
"shop_money": {
"amount": "2.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "2.00",
"currency_code": "USD"
}
},
"current_total_discounts": "0.00",
"current_total_discounts_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"current_total_duties_set": null,
"current_total_price": "1.18",
"current_total_price_set": {
"shop_money": {
"amount": "1.18",
"currency_code": "USD"
},
"presentment_money": {
"amount": "1.18",
"currency_code": "USD"
}
},
"current_total_tax": "1.23",
"current_total_tax_set": {
"shop_money": {
"amount": "1.23",
"currency_code": "USD"
},
"presentment_money": {
"amount": "1.23",
"currency_code": "USD"
}
},
"customer_locale": "en-US",
"device_id": null,
"discount_codes": [],
"estimated_taxes": false,
"financial_status": "paid",
"fulfillment_status": null,
"gateway": "shopify_payments",
"landing_site": "/",
"landing_site_ref": null,
"location_id": null,
"name": "28",
"note": null,
"note_attributes": [],
"number": 28,
"order_number": 28,
"order_status_url": "https://www.myshopifystore.com/ordersstuff",
"original_total_duties_set": null,
"payment_gateway_names": [
"shopify_payments"
],
"presentment_currency": "USD",
"processed_at": "2022-07-10T15:49:35-07:00",
"processing_method": "email",
"reference": null,
"referring_site": "",
"source_identifier": null,
"source_name": "web",
"source_url": null,
"subtotal_price": "12.00",
"subtotal_price_set": {
"shop_money": {
"amount": "12.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "12.00",
"currency_code": "USD"
}
},
"tags": "residential_address, UPS Ground",
"taxes_included": false,
"test": false,
"token": "3988d",
"total_discounts": "0.00",
"total_discounts_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"total_line_items_price": "12.00",
"total_line_items_price_set": {
"shop_money": {
"amount": "12.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "12.00",
"currency_code": "USD"
}
},
"total_outstanding": "0.00",
"total_price": "95.18",
"total_price_set": {
"shop_money": {
"amount": "95.18",
"currency_code": "USD"
},
"presentment_money": {
"amount": "95.18",
"currency_code": "USD"
}
},
"total_price_usd": "1.18",
"total_shipping_price_set": {
"shop_money": {
"amount": "1.95",
"currency_code": "USD"
},
"presentment_money": {
"amount": "1.95",
"currency_code": "USD"
}
},
"total_tax": "1.23",
"total_tax_set": {
"shop_money": {
"amount": "1.23",
"currency_code": "USD"
},
"presentment_money": {
"amount": "1.23",
"currency_code": "USD"
}
},
"total_tip_received": "0.00",
"total_weight": 0,
"updated_at": "2022-07-10T15:50:46-07:00",
"user_id": null,
"discount_applications": [],
"fulfillments": [],
"line_items": [
{
"id": 122691,
"admin_graphql_api_id": "gid://shopify/somethingelse",
"fulfillable_quantity": 1,
"fulfillment_service": "manual",
"fulfillment_status": null,
"gift_card": false,
"grams": 0,
"name": "That one product | Green",
"origin_location": {
"id": 329,
"country_code": "US",
"province_code": "GA",
"name": "JohnDoe",
"address1": "123 everyway",
"address2": "",
"city": "Atlanta",
"zip": "30349"
},
"pre_tax_price": "41.00",
"pre_tax_price_set": {
"shop_money": {
"amount": "41.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "41.00",
"currency_code": "USD"
}
},
"price": "41.00",
"price_set": {
"shop_money": {
"amount": "41.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "41.00",
"currency_code": "USD"
}
},
"product_exists": true,
"product_id": 655663,
"properties": [],
"quantity": 1,
"requires_shipping": true,
"sku": "101",
"tax_code": "P2",
"taxable": true,
"title": "That one product",
"total_discount": "0.00",
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"variant_id": 3966283,
"variant_inventory_management": "shopify",
"variant_title": "Green",
"vendor": "Jim's",
"tax_lines": [
{
"channel_liable": false,
"price": "1.64",
"price_set": {
"shop_money": {
"amount": "1.64",
"currency_code": "USD"
},
"presentment_money": {
"amount": "1.64",
"currency_code": "USD"
}
},
"rate": 0.04,
"title": "STATE TAX"
},
{
"channel_liable": false,
"price": "1.23",
"price_set": {
"shop_money": {
"amount": "1.23",
"currency_code": "USD"
},
"presentment_money": {
"amount": "1.23",
"currency_code": "USD"
}
},
"rate": 0.03,
"title": "COUNTY TAX"
}
],
"duties": [],
"discount_allocations": []
},
{
"id": 12269573734459,
"admin_graphql_api_id": "gid://shopify/something",
"fulfillable_quantity": 1,
"fulfillment_service": "manual",
"fulfillment_status": null,
"gift_card": false,
"grams": 0,
"name": "The Other Product",
"origin_location": {
"id": 3234585968699,
"country_code": "US",
"province_code": "GA",
"name": "JohnDow",
"address1": "123 everyway",
"address2": "",
"city": "Atlanta",
"zip": "30349"
},
"pre_tax_price": "1.00",
"pre_tax_price_set": {
"shop_money": {
"amount": "1.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "1.00",
"currency_code": "USD"
}
},
"price": "1.00",
"price_set": {
"shop_money": {
"amount": "1.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "1.00",
"currency_code": "USD"
}
},
"product_exists": true,
"product_id": 6,
"properties": [],
"quantity": 1,
"requires_shipping": true,
"sku": "9941",
"tax_code": "1234",
"taxable": true,
"title": "The Product #1",
"total_discount": "0.00",
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "USD"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "USD"
}
},
"variant_id": 8491,
"variant_inventory_management": "shopify",
"variant_title": "Ochre",
"vendor": "Stanley",
"tax_lines": [
{
"channel_liable": false,
"price": "1.64",
"price_set": {
"shop_money": {
"amount": "1.64",
"currency_code": "USD"
},
"presentment_money": {
"amount": "1.64",
"currency_code": "USD"
}
},
"rate": 0.04,
"title": "STATE TAX"
},
{
"channel_liable": false,
"price": "1.23",
"price_set": {
"shop_money": {
"amount": "1.23",
"currency_code": "USD"
},
"presentment_money": {
"amount": "1.23",
"currency_code": "USD"
}
},
"rate": 0.03,
"title": "COUNTY TAX"
}
],
"duties": [],
"discount_allocations": []
}
],
"payment_terms": null,
"refunds": []
}
]
I've tried:
1a. Data Flow Flatten transformation - I'm unable to unroll by multiple arrays in a single transformation
1b. Data Flow Flatten transformation - attempted to create multiple sources (of the same file/dataset) and flatten transformations but unable to stitch
1c. Data Flow Flatten transformation - using the method described here though I can't get the line items array to process correctly nor show up in the expression builder.
2. Copy activity with manually mapping to a table in which I've strung together nested items names with an underscore to form a column name e.g.
line_items.tax_lines.price_set.presentment_money.currency_code = line_items_tax_lines_price_set_presentment_money_currency_code
a. this almost seemed to work but I encountered two separate errors
i. The first error was all empty arrays gave this during debug: The retrieved type of data JObject is not supported yet. Please either remove the targeted column 'discount_codes' or enable skip incompatible row to skip the issue rows.
ii. when I unchecked all them (not a viable solution) I received another error: Column 'current_subtotal_price_set' not found in SQL DW table '[dbo].[FactShopifyOrders]'
- this seemed particularly strange as 'current_subtotal_price_set' shouldn't be a column at all.
If I had a million dollars, I would give it to the first person who can actually help me solve this with Synapse.
Thanks in advance!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(284.8, 44%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBL%3C/text%3E%3C/svg%3E)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 26%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDA%3C/text%3E%3C/svg%3E)
