How to Flatten A Complex Nested JSON Structure in Synapse

Bill LaLonde 11 Reputation points
2022-07-19T01:54:30.367+00:00

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!

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,103 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,040 questions
0 comments No comments
{count} vote

1 answer

Sort by: Most helpful
  1. Daniel André Fortun 11 Reputation points
    2022-07-19T13:41:15.87+00:00

    Hi,

    I've had similar issues with nested JSON data with multiple arrays within arrays.

    How are you connection to the GraphQL API? If youre using a notebook, i've found it easier to just flatten the hierarchy using python instead of using Data Flows. This can be done with the JSON package, which is built-in, and converting the JSON document to a Python library.

    Also, are you able to configure your GraphQL query? It might just be more simple to edit your query into something more manageable.

    Edit:
    Alternatively, this might work if you must use Data Flows (Although it requires a modest amount of manual work):
    222240-image.png

    As a first step, use a Select activity, and delete all nested arrays. As such:
    222344-image.png

    The "highest level" of data appears as it should:
    222353-image.png

    Now, for each nested array, use a select activity and select the nested data, not the array itself. To be able to join it back with the other data, add a column that is unique for each order/row/document. I chose the id column, but it might be another column as i dont have any other rows.

    222284-image.png

    I only tried with the client_details array, but it seems to work. When every nested array is selected, you can join them back together using the id row.
    222363-image.png

    1 person found this answer helpful.